Can I do this in a query?

[Rendbre]

Guys,

I am running a query of sales orders and stock to get a daily build list for production, ie if there is enough stock you don't need to build more.

The problem is I have multiple lines of the same part and only one on hand stock figure. Can I get the query to recognise the same item numbers and net these off against the total stock figure, rather than pull in a copy of the same stock figure for each line?

Cheers,

Ben

 

[themurph2000]

Guys,

I am running a query of sales orders and stock to get a daily build list for production, ie if there is enough stock you don't need to build more.

The problem is I have multiple lines of the same part and only one on hand stock figure. Can I get the query to recognise the same item numbers and net these off against the total stock figure, rather than pull in a copy of the same stock figure for each line?

Cheers,

Ben

I would imagine doing a Groups/Totals Query would be the way to go. Create a new query using only the fields you want to group by (part number) and total (amount in stock). You then click on the Totals Query (greek letter Sigma), and convert the "Group By" under your totals to a "Sum," leaving the "Group By" for your part number the same.

See if that works.

 

[Rendbre]

I'll definately try that, thank you.