I work at a hospital and deal with several thousand account numbers a day. I am working on an Access database that will handle some of my workload. Here is what I am facing. Lets say on some of the tables that I import there are duplicate account numbers with different totals in the end...

acct info info info amount
123456 xxxxx xxxxx xxxx 5.50
123456 xxxxx xxxxx xxxx 17.50
123456 xxxxx xxxxx xxxx 900.00
753951 xxxxx xxxxx xxxx xxxxxxxx

and so on...

is there a way that i can only show one account number but the total of the amount for the account? please help...this is desperately needed.

JG

acct info info info amount
123456 xxxxx xxxxx xxxx 5.50
123456 xxxxx xxxxx xxxx 17.50
123456 xxxxx xxxxx xxxx 900.00
753951 xxxxx xxxxx xxxx xxxxxxxx


JG

You could run a summation query that would show totals for each account PROVIDING that the other info was identical.

So for
123456 xxxxx xxxxx xxxx 5.50
123456 xxxxx xxxxx xxxx 17.50
123456 xxxxx xxxxx xxxx 900.00
all the intermediate value would need to be identical to get a single line summary.

If the intermediate data is not required you could leave it out and just sum
123456 5.50
123456 17.50
123456 900.00

L

what if the information that is on the other lines, is needed as well.

You are going to get just one total for the account. How do you know which constituent record to retrieve the other info from?

what if the information that is on the other lines, is needed as well.

You can have the other info as well but just remember that the summation will be applied to Distinct rows. So if a particular account has varied "other info" you will get multiple summation lines for a single account based upon the Distinct rows

It looks as if the database was not normalised correctly. Everything seems okay when it is started but when you want to do anything like this the lack of correct design bites back.

Not too much you can do about it except complain to the designer

L

I would like to thank everyone that has replied to my questions. Len, I would complain to the designer, but that would be me! This was my first access database and it looks like I will have to start it over again. This time however, we got the IS dept to allow us to go to an Access workshop that will hopefully help us out. Once again, Thanks everyone!

JG

Len, I would complain to the designer, but that would be me! This was my first access database and it looks like I will have to start it over again. This time however, we got the IS dept to allow us to go to an Access workshop that will hopefully help us out. Once again, Thanks everyone!

JG

I have trouble complaining as well since I am the designer in my company.

Access is totally different to any other Office application. most others you can fudge and get away with it. Not in Access. The design is the start and end. We all try very hard to get the design right because otherwise you run into problems that are very difficult to resolve.

I am like most designers in that I have got things wrong at some time or another and the only way is togo back to the beginning and start over,

Good luck

L