reports from multiple queries of same table

[daver56]

I have a case where I need to produce a report that contains several different results from the same table. I have 4 queries, each one giving me the results I need, but Access tells me that it cannot connect to more than one query that is from the same table. I need all of the results in the one report.

 

[mhartman1]

Hello:

To get data from multiple tables into one query, your tables must be related some how.

If this is not possible you can construct a UNION query by way of SQL to get data from multiple tables.

Regards

Mark

 

[daver56]

I do not need to get data from multiple tables. Just from one table. But I need 4 different queries to get the data I need, and Access tells me I cannot use 4 different queries in a report if they are all from the same table. It will not let me even use two tables if they are on the same table. I may look into subreports, and see if it will let me do it that way.

 

[lespaul]

If all the queries have the same number of fields in the SELECT clause, you can use a UNION query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dhookom]

You could get a much higher level of help if you provided either the SQL of the queries or tell us how they are different and how they are the same. Maybe why there are 4 and not 3 or 5.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daver56]

will do.

Thanks

 

[daver56]

Ok, here goes

The table associated with this case has 60-70 fields in it, but I am only concerned with 6 of them.

The fields are tdate, vehicle, status, priority time_1 and time_2.

All the queries are for the same range of dates and I have no problem with that.

vehicle is a text field (can be over 100 different entries)
status is a number field (aprox 25 different numbers)
priority is a number field. (aprox 40 different numbers)
time fields are "long time" (hh:mm:ss)

Fields of interest would look something like this.

tdate vehicle status priority time_1 time_2
__________________________________________________________
1-2-07 101 10 1 00:23:15 00:35:10
1-2-07 104 12 2 00:13.10 00:16:33
1-2-07 301 5 9 00:30:28 00:50:02
1-3-07 101 10 3 00:10:22 00:19:31
1-3-07 301 10 2 00:18:45 00:36:00
1-4-07 501 5 1 00:09:05 00:15:12
1-5-07 501 12 7 00:21:11 00:44:09



I need the following (all within the given date range of one month) with totals for each vehicle

1. Total entries per vehicle and average of time_1

2. Total entries per vehicle where priority is 1,2,or 3 and average of time_2

3. Total entries per vehicle where priority is 1,2 or 3 AND status is 10, 12, or 14

4. Total enteries per vehicle where priority is NOT 1, 2 or 3

5. Total eneeries per vehicle where priority is NOT 1, 2 or 3 and status is 10, 12, or 14



I need to produce a report with the above results per vehicle.


There will be around 8000 records that match the date range.

I was thinking maybe a query and report for each of the above and then use the report for each as a subreport for the final report.

Dave

 

[dhookom]

Subreports would work well. You might get all the results in a single query with sql like;

Select Vehicle, Avg(Time_1) as Avg1, 
Avg(IIf(Priority IN (1,2,3), Time_2, Null)) as Avg2, 
Sum(Abs(Priority IN (1,2,3) And Status IN (10,12,14))) as P123Status101214,
Sum(Abs(Priority Not IN (1,2,3))) as PNot123,
Sum(Abs(Priority Not IN (1,2,3) And Status IN (10,12,14))) as PNot123Status101214
FROM tblTooManyFields
GROUP BY Vehicle;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[daver56]

I think I have made a goof in the data I collected to send. The problem with all of this is that the data is coming from linked tables in a large sql database. I was trying to pull everything I needed through a query and do the reports I need, but I ran into more issues than I had planned for. I am working on doing a make table query, to pull the data I need and make conversions that will make the queries and reports easier. Some of the conversions that I am making are to the dates and times. For what ever reason some of the dates and times are stored as text fields. For what reason I do not know. Anyway, I have a make table query that is pulling the fields I need, making the conversions to real dates and times, that will hopefuly make things easier. I will post the new format, and the queries that pull the data I need. I am going to compare the single sql provided by dhookom and see if I can adapt that to work on my own.

Learning can be fun, but I am wondering when the fun will start.

I may be back here real soon, either way I will post updates.

Thanks for all the posts.

Dave