Compiling multiple queries...

[alktrigger]

Access 2007

I've created a set of queries (nearly identical) to sum records dating back 9 weeks (1 qry/wk) which will eventually work it's way into a report.

each query is as follows, except for the date range changes

SELECT tblShippingList.Name,
           Count(tblReceived.ID) AS CountOfID, 
           Sum(tblReceived.Received) AS SumOftblReceived
FROM tblShippingList INNER JOIN tblReceived ON tblShippingList.ID = tblReceived.Name
WHERE (((tblReceived.ReferenceDate)>([Forms]![frmReportLaunch].[txtStartDate]-14) 
          And (tblReceived.ReferenceDate)<=([Forms]![frmReportLaunch].[txtStartDate]-7)))
GROUP BY tblShippingList.Name;

I am now trying to combine all the queries together by using another query. I've brought all 9 of my weekly queries into my output query, but no matter how I try to relate the queries or arrange them, I just keep getting blank results. How should I be combining the individual queries, or is it possible even to do this with a single query?

 

[rainman89]

Why do you want them in a single query? For reporting purposes?

You can put them each into their own subreport when it comes time to put them in and then combine them on a single report

 

[alktrigger]

The purpose is to create a report, but the whole reason I'm building this database is to create a report that mimics the old spreadsheet that was being used before. I'm open to suggestions.

 

[rainman89]

Ok. So do it the way i said with the subreports. It will be easier then trying to combine your queries

 

[alktrigger]

I've been playing with the subreport, but it's not really accomplishing what I am intending.

 

[rainman89]

What exactly are you intending? Mocking a data sheet from excel?

 

[alktrigger]

exactly that. I am trying to have a row of data showing for each record much like:

Name | count of dates a-b | count of dates b-c| -----

that is my ultimate goal.

Would I be better off if I create an append query that writes to a temporary table, in the layout that I want, and just pulling data from there?

 

[rainman89]

That would probably be easier then combining all the queries, yes

 

[alktrigger]

Sounds like problem solved. I hope.

Thanks

 

[alktrigger]

Well, it's still being difficult. I've made the append query but I'm still having issues with the fact that access does not want to join the queries, thus not returning any results at all

 

[rainman89]

Shouldnt be if you are merely appending the data into the table. make sure all the fields in the queries are in the table and you should be all set.

 

[alktrigger]

I finally have this working right, but I just realized that it's not showing records for those entries that did not have records in each individual query. Is there some way to do something similar to a LEFT JOIN when using the make table query, so that it will show an empty record?

 

[alktrigger]

I take that back.

I'm an idiot. Maybe I should have just used LEFT JOIN

 

[rainman89]

Glad you are figuring it out~!