I have this info in a table:

ID Date Silo1 Silo2 S1lbs S2lbs
1 3/15/08 1 2 32 19
2 3/15/08 3 44 0
3 3/26/08 2 66 0
4 3/26/08 3 34 0
5 3/27/08 1 2 24 15
6 3/27/08 2 52 0

How do I query this to sum the pounds per silo, sorted by date regardless if the silo "number" was recorded under Silo1 or Silo2? (Silo1 and Silo2 are actually text fields.)
Somehow I would like the data to end up looking like this:

Date Silo Lbs
3/15/08 1 32
3/15/08 2 19
3/15/08 3 44
3/26/08 2 66
3/26/08 3 34
3/27/08 1 24
3/27/08 2 67

I think there is a fundamental design problem here that makes this task more difficult than it should be. That being said, here is what I did:

I create a local table called "SilageCompilation" that looks like:

SilageCompilation
Date
Silo
Lbs

Then I created a DELETE query to empty this table as needed.

Then I created two APPEND queries that look like this (assuming your original table name is "Silage"

qrySilo1
INSERT INTO SilageCompilation ( [Date], Silo, Lbs )
SELECT Silage.Date, Silage.Silo1, Silage.S1Lbs
FROM Silage
WHERE (((Silage.Silo1)<>""));

qrySilo2
INSERT INTO SilageCompilation ( [Date], Silo, Lbs )
SELECT Silage.Date, Silage.Silo2, Silage.S2Lbs
FROM Silage
WHERE (((Silage.Silo2)<>""));

Then I created a query called "SilageCompiled" that looks like this:

qrySilageCompiled
SELECT SilageCompilation.Date, SilageCompilation.Silo, Sum(SilageCompilation.Lbs) AS SumOfLbs
FROM SilageCompilation
GROUP BY SilageCompilation.Date, SilageCompilation.Silo;


Then I created a form with a button that runs them all, and put this code on the on click event of the button:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_SilageCompilation"
DoCmd.OpenQuery "qrySilo1"
DoCmd.OpenQuery "qrySilo2"
DoCmd.SetWarnings True

This cleans out the local table, then adds in the data from the original table. So when you open the last query "qrySilageCompiled" you get these results:

Date Silo SumOfLbs
3/15/2008 1 32
3/15/2008 2 19
3/15/2008 3 44
3/26/2008 2 66
3/26/2008 3 34
3/27/2008 1 24
3/27/2008 2 67

I didn't know how you wanted to view the results (form, report, datasheet) so I leave that up to you. Anyway, this gets the job done. It is really a fundamental design issue that prevents this from being run in the first place. I would do this:

tblTransaction
TransID
Date
OtherInfo?

tblSilos
SiloID
SiloNumber
OtherInfo?

tblSilo_Trans
Silo_TransID
TransID
SiloID
Lbs

This would allow this query to be run in one step. HTH

mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, the DELETE query SQL is:

qryDELETE_SilageCompilation
DELETE SilageCompilation.*
FROM SilageCompilation;


mmcdonal

Look it up at: http://wrox.books24x7.com