Auto number in a query

[Sed]

Please help!!!.

I have a query that when I "sum" my auto number diappear. for example, when I run my query I get 22 records, when I suppose to get 2. So, I "sum" or "group by" my column (which I want to do), my auto number disappear. Which I don't want to do.

 

[boblarson]

You can't get a sum for all of the records that way and still have the autonumber. It doesn't make sense that you would. You have to leave out the autonumber field to get a sum of all of the records but if you leave in the autonumber it will give you a sum for each autonumber which would match the behavior you are seeing.

So, what are you trying to sum and what is the purpose of your autonumber?

 

[Sed]

Boblarson,
thanks for the quick reply, the purpose of th autonumber is for form. When I open my form it goes directly to the last empty field to be entered. We have multiple people entering this form. My form will automatically assign a number to that individual person entering the PAN (primary authorization number). And the reason why I want to sum is because we my multiple parts and part number per each shipment. I just wanted to sum the total weight etc...

 

[vbaInet]

I think you want to use =Sum([WeightField]) in the form's footer or you can look into the DSum() function.

 

[Sed]

I used the Dsum() function but it's returning blank. PremQty: =Sum("[Prem_Qty]","[qry Parts]")

 

[boblarson]

Don't put the query name in square brackets and it is DSUM, not SUM.

=DSum("[Prem_Qty]", "qry Parts")

 

[vbaInet]

Compare and contrast:

I used the Dsum() function but it's returning blank.

PremQty: =Sum("[Prem_Qty]","[qry Parts]")

However, you should not be using a DSum() function in the query if all you're doing is calculating the sum of all records. Use it in your form's footer or header section.

 

[Sed]

OK, It worked but with one little quarks. It's summing everything for both Auth Num. Of course I should of know. I want it to sum if the Auth Num is the same.

 

[Sed]

Sorry, let me upload my excel

 

[vbaInet]

In that case you need to go back to the way you were doing it before and add the Auth Num field to the query. Remember to tick the Show box.

Can you please edit your post and get rid of the long line.

 

[Sed]

Not sure if I understand. The way I did it before didn't work.

 

[vbaInet]

It will work if you have Auth Num visible. Group By and Sort By Auth Num.

 

[Sed]

I'm crying very hard right now. I'm not getting it. I've tried it every way possible. If I do the group by, I will get what I want, but the Auth num is not there. And, yes the tick is check on the Auth Num field.

 

[boblarson]

I'm crying very hard right now. I'm not getting it. I've tried it every way possible. If I do the group by, I will get what I want, but the Auth num is not there. And, yes the tick is check on the Auth Num field.

How about posting a copy of your database (see here for how to do it) with bogus data, of course, so we can see what you have and try to help.

 

[Sed]

four of my tables are linked ODBC (external). Let me try to re-do my tables and I will post it when I'm done. thanks so much for you help. I have no more hair left to pull

 

[Sed]

OK, see attached DB. qry planner form is the query.

 

[vbaInet]

When I initially opened your db and found a decent number of tables, my first thought was your tables were normalized but to my amazement this was not the case. Some tables have no primary key and between all 14 tables only 2 are linked. You need to fix that really soon.

In the query you mentioned, I don't see your attempt to perform the sum. Plus, how do you expect to get a sum grouped by Auth Num with all those other fields included in the query?

 

[Sed]

Yes, I know. My new DB does not have that many tables. I had to copy my old one and post it. The one I'm working on is clean. In the query I mentioned, scroll all the way to the right "expr1" is the sum that I tried to plug in. If you run the query, notice that there's only two records, which is right. But, the sum code summed everything on that table instead of splitting it out per autonumber. Right now, the result is 9164 for both autonumber, and I need it to be 4632 for the first autonumber and 4532 for the second autonumber. P.S. thank you very much for helping.

 

[vbaInet]

Yes, I know. My new DB does not have that many tables. I had to copy my old one and post it. The one I'm working on is clean.

Having less tables doesn't have really have anything to do with normalization. I was referring to the lack of Primary and Foreign Keys in your tables and relationships between them.

1. Do all tables in your new db have these Primary Keys and Foreign Keys (where necessary) setup?
2. Are there proper relationships between the tables?

If you don't fix it to meet the afore-mentioned requirements then you will fall into problems.

Here's the expression:

Expr1: DSum("[Prem_Qty]","qry parts","Auth_Num = " & [Shipments].[Auth Num])

 

[Sed]

You are awesome, thanks a bunch. If you are ever in Atlanta, let me know, I owe you a few beer.

Can I ask you one more favor? In my main form, right now it's reading from "shipper" table, I want it to read from "dbo_supplier" table and keep my combo box as is. I'm not good with forms.