Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 31st, 2008, 10:39 AM
Authorized User
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query Help Please

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
Old April 2nd, 2008, 06:57 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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:


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"

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

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:

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:




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


Look it up at: http://wrox.books24x7.com
Old April 2nd, 2008, 07:02 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Sorry, the DELETE query SQL is:

DELETE SilageCompilation.*
FROM SilageCompilation;


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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.