Query from queries
- Thread starter tikkat3
- Start date
ajetrumpet
Banned
- Local time
- Today, 16:34
- Joined
- Jun 22, 2007
- Messages
- 5,628
You have 2 tables AND 4 queries listed in Design as you are building this new query? Is your DB that big? What are you trying to do exactly? What do your other queries hold?
- Local time
- Today, 16:34
- Joined
- Feb 28, 2001
- Messages
- 30,568
You have two possible conditions that could cause this.
First, your culprit could be WHERE clauses that end up excluding all records.
Second, if these tables involve formal relationships, the JOINs will by default be INNER joins. But it is not clear to me that queries will behave quite the same way. If you have any two tables or queries that are disjoint, the implied JOIN would return nothing.
Try building the JOIN queries in layers to see at what point you lose output. At least you would have a place to start.
First, your culprit could be WHERE clauses that end up excluding all records.
Second, if these tables involve formal relationships, the JOINs will by default be INNER joins. But it is not clear to me that queries will behave quite the same way. If you have any two tables or queries that are disjoint, the implied JOIN would return nothing.
Try building the JOIN queries in layers to see at what point you lose output. At least you would have a place to start.
The reason for the number of queries feeding the main query is that I cannot think of another way of getting the results in a single query. Each sub query has the following data: Contract number, planned visit 1, actual visit 1, date (between 2 dates), Fix (number/digit code).
The constant will be the date range. There are upto 5 planned and actual visits and the Fix code is specific to the visit number. As each contract number can have many visits associated with it across many months, the Fix code is needed to filter out the planned and actual visits within the date range. Otherwise I have found that because there is a visit (could be number 5) the results include all visits, including those in other months.
The Fix code is specific to a visit number. I can therefore not get multiple visits and their fix code into the query and have them make sense.
The constant will be the date range. There are upto 5 planned and actual visits and the Fix code is specific to the visit number. As each contract number can have many visits associated with it across many months, the Fix code is needed to filter out the planned and actual visits within the date range. Otherwise I have found that because there is a visit (could be number 5) the results include all visits, including those in other months.
The Fix code is specific to a visit number. I can therefore not get multiple visits and their fix code into the query and have them make sense.
ajetrumpet
Banned
- Local time
- Today, 16:34
- Joined
- Jun 22, 2007
- Messages
- 5,628
Want to post the code or the attachment? Might be able to help....but I'm not getting the exact problem from your description. There should not be a big problem querying what you need....
I have attached 2 screenshots:
The first shows one of the queries, which filters out the specific data needed. There are another 5 of these (2nd fix, 3rd fix etc). The constants across all the queries is the C/Q number and date. The [Fix] field varies according to the fix number.
The second screenshot shows the report design that the query feeds. In each of the detail sections, the first 2 fields show the results of columns 2 & 3 of the above queries. The third field in each section is a calculation based on the 2 preceding fields.
In the report footer the fields sum up the detail fields and provide further calculations based upon the sums in this section. The very right hand column in the report footer makes total calculations based upon the sum calculations in the 6 sections to the left.
In other words, the report footer sums up the individual detail sections and then totals up in the right hand column.
The SQL for the query in the screenshot is:
SELECT Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[1st Fix Men Plan])=True) AND ((Personnel.[1st Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="1"))
ORDER BY Projects.Date;
I hope this helps make better sense!
The first shows one of the queries, which filters out the specific data needed. There are another 5 of these (2nd fix, 3rd fix etc). The constants across all the queries is the C/Q number and date. The [Fix] field varies according to the fix number.
The second screenshot shows the report design that the query feeds. In each of the detail sections, the first 2 fields show the results of columns 2 & 3 of the above queries. The third field in each section is a calculation based on the 2 preceding fields.
In the report footer the fields sum up the detail fields and provide further calculations based upon the sums in this section. The very right hand column in the report footer makes total calculations based upon the sum calculations in the 6 sections to the left.
In other words, the report footer sums up the individual detail sections and then totals up in the right hand column.
The SQL for the query in the screenshot is:
SELECT Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[1st Fix Men Plan])=True) AND ((Personnel.[1st Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="1"))
ORDER BY Projects.Date;
I hope this helps make better sense!
ajetrumpet
Banned
- Local time
- Today, 16:34
- Joined
- Jun 22, 2007
- Messages
- 5,628
The query that you presented is supposed to be one of the subs?
If you have a sub for every Plan and Act visit possibilty (up to 5?), do SELECT queries only for those, then query all of those from another. I don't think the Having clause is necessary for these subs, I would suggest the Group By use only once. If you need it more than once, I guess I'm not seeing it.
Don't think there is much to change on the report, just need to straiten the query.
So, the fix number references ALL visits made for the specific contract, right? This quote doesn't imply that.
Single Visit = Visit Number = Unique Fix #?
Not sure if I can decipher the reasoning here.
If you have a sub for every Plan and Act visit possibilty (up to 5?), do SELECT queries only for those, then query all of those from another. I don't think the Having clause is necessary for these subs, I would suggest the Group By use only once. If you need it more than once, I guess I'm not seeing it.
Don't think there is much to change on the report, just need to straiten the query.
So, the fix number references ALL visits made for the specific contract, right? This quote doesn't imply that.
Visit Number = (usually for separate visits)
Single Visit = Visit Number = Unique Fix #?
Not sure if I can decipher the reasoning here.
The reason for needing the fix number as well as the visit number is due to the way in which the database was originally set up. The visits are recorded through a subform with the main project data being in the main form. For each stage (Fix) of a project a separate record is created - albeit with the same C/Q number etc. The Fix field in the main form denotes which stage of the project. The manning (planned and actual) is in the subform which draws its data from its own table. The 2 tables have their master/child relationship through the C/Q number.
Therefore to ensure the correct data is provided in the query both the Fix field from the Projects table and the appropriate Planned and ACT fields from the Personnel table are needed. If the Fix field is omitted, false data is provided - I have tried it!
It makes this type of query complicated as subqueries have to be created to provide the overall picture. In the meanwhile I have a part solution by using subreports I can get the correct results for each section in the detail and the summaries in the report footer.
The final hurdle is to get the grand totals into the final column of the report footer. Access will not let me create another subreport to sum the totals of the other report footer subreports to give me the grand totals.
I wish there were some easier way to do this!
Therefore to ensure the correct data is provided in the query both the Fix field from the Projects table and the appropriate Planned and ACT fields from the Personnel table are needed. If the Fix field is omitted, false data is provided - I have tried it!
It makes this type of query complicated as subqueries have to be created to provide the overall picture. In the meanwhile I have a part solution by using subreports I can get the correct results for each section in the detail and the summaries in the report footer.
The final hurdle is to get the grand totals into the final column of the report footer. Access will not let me create another subreport to sum the totals of the other report footer subreports to give me the grand totals.
I wish there were some easier way to do this!
