aggregate query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

aggregate query

Tim Elbert

2 years ago

I have a query that shows the sum of each item in one field with the itemIds in ten other fields. I would like to query this to show the total number of each item represented, by the item number (see excel sample in photo). Each ItemId represents one item but can show up in any of the ten fields depending on how they are ordered. (Each item is a plant and each field is a location so depending on how they want it planted, each item can be used in any or all of the fields. I need each item (1) to be multiplied by the sum(Field1) and then added together with all the others of the same item number for a total number of each Item (again, see excel sample). Can this be done with an aggregate query and if so, please help me with the formula.
If this cannot be done in one step in an aggregate query, what would be the steps to get this?
Thank you.

	Tim Elbert OP @Reply 2 years ago

	Juan C Rivera @Reply 2 years ago
Can you show the tables and the relashipship. I like to try and make a better understanding of how this is set up. but off the top of my head I think your ID is missing. try this video to see if it helps Relationships

Tim Elbert OP @Reply

2 years ago

Hi Juan
I am not sure how that will help. I might not be asking the question correctly, or perhaps I am misunderstanding something. The relationships are there and the information is all correct, I just need it shown in a different manner. Each number in the 'Plug' feilds are the PlantId that represents a specific plant, and each field represents a specific location. Each line represents a specific sales order. I just need to know how many there are of each specific plant in the whole table. I could do a COUNT formula but there is the 'sumoftotal' column that represents how many are on that specific order. The excel table on the right represents the 'look' I am looking for.

	Kevin Yip @Reply 2 years ago
You may need to re-design your table structure. Access (or any relational database system) can only count rows in an aggregate queries, not columns. So your table needs to be more "row-oriented" so that rows can be counted. For instance. E.g.: Instead of this: Plug1 Plug2 Plug3 137 138 139 do this: PlugNum PlantID 1 137 2 138 3 139

	Tim Elbert OP @Reply 2 years ago
Thanks Kevin, that is quite helpful. I will work on that.

	Richard Rost @Reply 2 years ago
Normalizing Data

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View