Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 31st, 2008, 10:39 AM
Authorized User
 
Join Date: Mar 2008
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old April 2nd, 2008, 06:57 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old April 2nd, 2008, 07:02 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:35 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.