"Unique" Criteria? HELP!

[helpmeee]

Hey Everyone,
I'm new here and am in desperate need for some Access help! I have two tables that are put together into one query with title headings, "Mission" "Master" "PO Number" "PO Item" "Item/Acct Value" and "Supplier Name". My goal is to create two reports from this query (one of sales between 100k-500k and one with sales greater than 500k).

Here's the problem: there are multiple PO item's for one PO number. IE:

I need to somehow add the PO Item costs and in turn make that one PO Number. So when I pull a report, ONE cost will match ONE PO Number. IE:

I think I need to put something in the "criteria" section on the query but I'm not an access pro by any means. Can someone help me out? GREATLY APPRECIATED

 

[Dennisk]

you need to turn your select query into a totals query , using Groupby on the POs and Sum on the item costs.

Depending on your version of Access you may find the menu option on the view menu when in query design mode.

 

[helpmeee]

you need to turn your select query into a totals query , using Groupby on the POs and Sum on the item costs.

Depending on your version of Access you may find the menu option on the view menu when in query design mode.

I understand what you're saying but have NO idea how to actually do it. I'm working with Access 2007 if that helps?

 

[Brianwarnock]

What Dennisk is saying, and assuming 2007 is the same as previous, then when in query design mode select View from the menu bar,
select Total from the dropdown list.
A new row will appear in the design grid showing Group by but with a drop down to select other aggregate options.

Brian

 

[helpmeee]

What Dennisk is saying, and assuming 2007 is the same as previous, then when in query design mode select View from the menu bar,
select Total from the dropdown list.
A new row will appear in the design grid showing Group by but with a drop down to select other aggregate options.

Brian

I got the "Group By"'s to appear. Now what? Do I group "PO Item" by 'Sum'? Do I put anything in the criteria for PO Number?

 

[helpmeee]

Does anyone know where I go from here?

 

[Brianwarnock]

What did you try? Did you click in the Group by field and then select from the list created by the down arrow?

You will only be able to Group on PO number and Sum Item Value to Group on say PO ITEM will not give one record, ditto any other fields with varying values , experiment and then come back with any other questions.

Brian