Query Help

[rkrause]

I am having trouble with a query. I have a table called Part Avg Cost, it has 3fields in it. PartCost, LocationID, Location. In my query i have Partcost as a SUM LocationID as Count. Now i need to beable to add up all the partcost and the LocationID count and then divide Part cost by LocationID count.

any help i have attached the document

 

[ajetrumpet]

What will your desired end result (the number) represent (describe it in words)??

 

[rkrause]

If i have 50 Cushions in stock and they total 1837$ it should be each cusion is worth 36.74

 

[ajetrumpet]

So you want a query that gives you one number in one column...the AVERAGE cost of a cushion, correct??

 

[rkrause]

Right now i have the Sum of all the cusions on hand is 50 getting divided by the LocationID.

In my query i have
Party Cost, totaled by sum

LocationID-Totaled by count
then i have a custom field
PartAvgCost: [PartCost]/[LocationID]

my end result is # of cusions 50/ Location id 22=2.272727

 

[rkrause]

Nevermind i got it to work. but when i go to run it wants me to enter in a criteria for SumOfPartCost and CountOfLocationID, i do not want to enter anything in for them, how do i get rid of this coming up

 

[ajetrumpet]

Right now i have the Sum of all the cusions on hand is 50 getting divided by the LocationID.

I probably can't go any further here unless I know what kind of data is the the "Loc.ID" field and the "location" field, and why on earth (unless "location" refers to aggregate data somehow) you are including more steps than need be (if all you want is an average cost).

 

[ajetrumpet]

Nevermind i got it to work. but when i go to run it wants me to enter in a criteria for SumOfPartCost and CountOfLocationID, i do not want to enter anything in for them, how do i get rid of this coming up

Obviously, it's not working. I have no idea...from the looks of the .doc screenshot, your file is pretty large. Sounds like we are just shooting in the dark here, because you can see it and I cannot.

 

[rkrause]

the table im working with was built on a make table query, the locationid field is in the table location, it is a random number 1-321

 

[ajetrumpet]

Get rid of the GROUP BY in your last field (put EXPRESSION in its place), it is an aggregate expression, not a field. An explanation of why you do this can be found here...

http://www.access-programmers.co.uk/forums/showthread.php?t=135763

Access is asking for the field names in that expression because it is reading it as a field instead of an expression, and it can't find the field in the table.