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
Default

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
 
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
Default

Sorry, the DELETE query SQL is:

qryDELETE_SilageCompilation
DELETE SilageCompilation.*
FROM SilageCompilation;


mmcdonal

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.