SQL to combine values using 2 sets of parameters?

[Aero11]

Hello again,

I am trying to create a script that will match up multiple columns and combine the values, leaving only one unique line entry.

So, for example, I have one column with "Work Order Number" another with "Status", and one more with "Number of Days". For whatever reason, our database can have multiple instances of the same work order number and status, but with different values.

Work Order - Status - Number of Days

21104 - - - Not in Stock - - - 5
21104 - - - Not in Stock - - - 8
21104 - - - Not in Stock - - - 10

I would like to have it combine everything into

Work Order - Status Number - of Days

21104 - - - Not in Stock - - - 23


Can this be done?

I am using the MS Query tool that comes with Excel, but most Access methods work.

Thank you in advance.

 

[Joe4]

I am using the MS Query tool that comes with Excel, but most Access methods work.

I don't know much about MS Query, but this is pretty easy to do in Access using what is called an Aggregate (or Totals) Query. Your queries would "Group" the records on Work Order and Status, and then Sum the Number of Days field.

Access has examples of Aggregate Queries in their built-in help files. It is pretty simple to do.

 

[dk]

Hi

You should just be able to use Totals in MS Query (this is the same as doing a group by query). The SQL would look something like this:

SELECT WORK_ORDER, STATUS, SUM(NUMBER_OF_DAYS) FROM YOURTABLE GROUP BY WORK_ORDER, STATUS


HTH
DK

 

[Aero11]

Hi

You should just be able to use Totals in MS Query (this is the same as doing a group by query). The SQL would look something like this:

SELECT WORK_ORDER, STATUS, SUM(NUMBER_OF_DAYS) FROM YOURTABLE GROUP BY WORK_ORDER, STATUS


HTH
DK

Worked like a charm! Thank you!