Same query information, different results.

[EPBoone]

Alright, I have ten different queries. Here is the SQL for one of them.

SELECT [DCU LOG].[Doc#], [DCU LOG].JCN, [DCU LOG].[PART NO], [DCU LOG].SERNO, [DCU LOG].[JD T/I], [DCU LOG].MCN, [DCU LOG].PC, [DCU LOG].STATUS, [DCU LOG].REMARKS, *
FROM [DCU LOG]
WHERE ((([DCU LOG].[JD T/I]) Between [Enter BCM1 Start Date] And [Enter BCM1 End Date]) AND (([DCU LOG].STATUS) Like "BCM1&quot AND (Not ([DCU LOG].[JC JD]) Is Null));

The other nine are the same, except I need the different Status's (RFI, BCM1, BCM2, and so on.). Notice I also query these by date. When I do my reports, I need all the info from all these different queries, and the only information that changes is the Status. The date would be the same for each set of results. I am creating a form/report that will display the results (and some other stuff) from all of these queries. Is there a way that I can get the info from these queries all at the same time without having to type in the dates over and over again for each Query?

Thanks ahead of time,
Gene

 

[Smitty020]

Gene,

If the result set that you are looking for is the same for each of the queries, try using a UNION. In SQL, cut and paste the query ten times, in between each query, type UNION. Then you can change the location for "status" as needed. It would look like this:

SELECT [DCU LOG].[Doc#], [DCU LOG].JCN, [DCU LOG].[PART NO], [DCU LOG].SERNO, [DCU LOG].[JD T/I], [DCU LOG].MCN, [DCU LOG].PC, [DCU LOG].STATUS, [DCU LOG].REMARKS, *
FROM [DCU LOG]
WHERE ((([DCU LOG].[JD T/I]) Between [Enter BCM1 Start Date] And [Enter BCM1 End Date]) AND (([DCU LOG].STATUS) Like "BCM1&quot AND (Not ([DCU LOG].[JC JD]) Is Null));

UNION

SELECT [DCU LOG].[Doc#], [DCU LOG].JCN, [DCU LOG].[PART NO], [DCU LOG].SERNO, [DCU LOG].[JD T/I], [DCU LOG].MCN, [DCU LOG].PC, [DCU LOG].STATUS, [DCU LOG].REMARKS, *
FROM [DCU LOG]
WHERE ((([DCU LOG].[JD T/I]) Between [Enter BCM1 Start Date] And [Enter BCM1 End Date]) AND (([DCU LOG].STATUS) Like "BCM1&quot AND (Not ([DCU LOG].[JC JD]) Is Null));

UNION

SELECT [DCU LOG].[Doc#], [DCU LOG].JCN, [DCU LOG].[PART NO], [DCU LOG].SERNO, [DCU LOG].[JD T/I], [DCU LOG].MCN, [DCU LOG].PC, [DCU LOG].STATUS, [DCU LOG].REMARKS, *
FROM [DCU LOG]
WHERE ((([DCU LOG].[JD T/I]) Between [Enter BCM1 Start Date] And [Enter BCM1 End Date]) AND (([DCU LOG].STATUS) Like "BCM1&quot AND (Not ([DCU LOG].[JC JD]) Is Null));


UNION

..and so on. Just cut and paste, changing the information from status. If you are expecting repeating records, use UNION ALL instead of UNION.

hope this is helpful,

Smitty020

 

[EPBoone]

Not quite what I wanted, but thanks. What I need is ten different sets of results for each status. Actually, I am nuking this. I just realized that what I am doing is actually a pretty easy thing. Thanks though. I appreciate it.

Gene

 

[EPBoone]

I take that back. What I was going to do won't work either. The thing is, I have a table that lists aircraft parts. The fields involved are [JD T/I] which is the date I received the part, [JC JD] which is the date the part was repaired, and [Status] which is the type of repair that was done. What I have so far is each Query lists the parts by the Status in a certain range of days (i.e. one query will list all the parts that had an RFI Status between 1000 (my date format) and 1030. I want to display the results of all of these different Status's on one form or report and then for each set of records count the number of records and get an average of how many days between the time I received them and the time they were repaired for the days that I choose. I can make a form and add all the subforms, and get all the information I need, but if I do that, I have to type in the same days over and over again for each set of records. I also tried using one query and text boxes to count and average just the different Status's, but I can't seem to get that to work either. Maybe I'm just not doing it right. (well, obviously). I give up. What do you think?

Gene

 

[EPBoone]

Right now, the only way I can get this to work is creat a form and make subforms with all the different Queries, but I have to type in the same dates 10 times.

 

[katiekat]

Is there some way perhaps to send what you type in the first time into the others in the after update event on that control??

Do you have 10 different tables that these queries are based on? Holy tek-tips batman!:-0

 

[Smitty020]

I'm not quite sure I completely understand what your trying to do. Do you want to show the information grouped by "Status"? If this is the case, run the union query and get all of the parts with all of the status'. In the query. I would pass a new field without showing it saying,

If Status1 then 1
else if Status2 then 2
else if Status3 then 3
.....
....
.....
else n

where n would be the number of Status's. Then on the report, you could group by this field, then total them for each group.

Please disregard if this is not what your looking for.
hth,
Smitty

 

[raskew]

I must be interpreting this incorrectly, but why would you need ten queries when all of the information is the same except for the status? Why not one query, which includes all status', grouped/sorted on status?