If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Troublesome query
Hi, I have built two different queries, one works, and the second does not
work correctly. I am attempting to pull all records from one table, and only the records in the second table which are the first entry of the day. I have puzzled through this several times and am unable to figure out why it's not working correctly. The problem I am having is that the second query is not pulling the first record of the day, where the first query will pull that record every time. Any help is most appreciated. I am pasting the query which isn't working first, and the second is the one which does work. Thanks! SELECT [South Plant Filter Runtimes].Date, [South Plant Filter Runtimes].[Filter #], [South Plant Filter Runtimes].Time, [South Plant Filter Runtimes].Type, [South Plant Filter Runtimes].Hours, [South Plant Filter Runtimes].Comments, [South Plant Filter Runtimes].[Failure Step], [South Plant Filter Runtimes].Headloss, [South Plant Filter Runtimes].[Filter Flow Rate], First([Plant Flows].Date) AS FirstOfDate, First([Plant Flows].[South Plant Flow]) AS [FirstOfSouth Plant Flow], First([Plant Flows].[Filter 14 Status]) AS [FirstOfFilter 14 Status], First([Plant Flows].[Filter 15 Status]) AS [FirstOfFilter 15 Status], First([Plant Flows].[Filter 16 Status]) AS [FirstOfFilter 16 Status], First([Plant Flows].[Filter 17 Status]) AS [FirstOfFilter 17 Status], First([Plant Flows].[Filter 18 Status]) AS [FirstOfFilter 18 Status], First([Plant Flows].[Filter 19 Status]) AS [FirstOfFilter 19 Status], First([Plant Flows].[Filter 20 Status]) AS [FirstOfFilter 20 Status], First([Plant Flows].[Filter 21 Status]) AS [FirstOfFilter 21 Status], First([Plant Flows].[Filter 22 Status]) AS [FirstOfFilter 22 Status], First([Plant Flows].[Filter 23 Status]) AS [FirstOfFilter 23 Status], First([Plant Flows].[West 5 MG Tank Level]) AS [FirstOfWest 5 MG Tank Level], First([Plant Flows].[East 5 MG Tank Level]) AS [FirstOfEast 5 MG Tank Level] FROM [Plant Flows] RIGHT JOIN [South Plant Filter Runtimes] ON [Plant Flows].Day = [South Plant Filter Runtimes].Date GROUP BY [South Plant Filter Runtimes].Date, [South Plant Filter Runtimes].[Filter #], [South Plant Filter Runtimes].Time, [South Plant Filter Runtimes].Type, [South Plant Filter Runtimes].Hours, [South Plant Filter Runtimes].Comments, [South Plant Filter Runtimes].[Failure Step], [South Plant Filter Runtimes].Headloss, [South Plant Filter Runtimes].[Filter Flow Rate] HAVING ((([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog]![Beginning Date] And ([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog 2]![Ending Date]) AND (([South Plant Filter Runtimes].[Filter #]) Is Not Null)); SELECT [North Plant Filter Runtimes].Date, [North Plant Filter Runtimes].[Filter #], [North Plant Filter Runtimes].Time, [North Plant Filter Runtimes].Hours, [North Plant Filter Runtimes].Comments, [North Plant Filter Runtimes].[Failure Step], [North Plant Filter Runtimes].[Headloss Trip Setpoint], [North Plant Filter Runtimes].[Filter Flow Rate], First([Plant Flows].Date) AS FirstOfDate, First([Plant Flows].[Filter 3 Status]) AS [FirstOfFilter 3 Status], First([Plant Flows].[Filter 4 Status]) AS [FirstOfFilter 4 Status], First([Plant Flows].[Filter 5 Status]) AS [FirstOfFilter 5 Status], First([Plant Flows].[Filter 7 Status]) AS [FirstOfFilter 7 Status], First([Plant Flows].[Filter 8 Status]) AS [FirstOfFilter 8 Status], First([Plant Flows].[West 5 MG Tank Level]) AS [FirstOfWest 5 MG Tank Level], First([Plant Flows].[East 5 MG Tank Level]) AS [FirstOfEast 5 MG Tank Level], First([Plant Flows].[Filter 6 Status]) AS [FirstOfFilter 6 Status], First([Plant Flows].[North Plant Flow]) AS [FirstOfNorth Plant Flow], [North Plant Filter Runtimes].Type FROM [Plant Flows] RIGHT JOIN [North Plant Filter Runtimes] ON [Plant Flows].Day = [North Plant Filter Runtimes].Date GROUP BY [North Plant Filter Runtimes].Date, [North Plant Filter Runtimes].[Filter #], [North Plant Filter Runtimes].Time, [North Plant Filter Runtimes].Hours, [North Plant Filter Runtimes].Comments, [North Plant Filter Runtimes].[Failure Step], [North Plant Filter Runtimes].[Headloss Trip Setpoint], [North Plant Filter Runtimes].[Filter Flow Rate], [North Plant Filter Runtimes].Type HAVING ((([North Plant Filter Runtimes].Date)=[Forms]![Date Dialog]![Beginning Date] And ([North Plant Filter Runtimes].Date)=[Forms]![Date Dialog 2]![Ending Date])); |
#2
|
|||
|
|||
Troublesome query
I figured it out, thanks anyway.
"RWhittet" wrote: Hi, I have built two different queries, one works, and the second does not work correctly. I am attempting to pull all records from one table, and only the records in the second table which are the first entry of the day. I have puzzled through this several times and am unable to figure out why it's not working correctly. The problem I am having is that the second query is not pulling the first record of the day, where the first query will pull that record every time. Any help is most appreciated. I am pasting the query which isn't working first, and the second is the one which does work. Thanks! SELECT [South Plant Filter Runtimes].Date, [South Plant Filter Runtimes].[Filter #], [South Plant Filter Runtimes].Time, [South Plant Filter Runtimes].Type, [South Plant Filter Runtimes].Hours, [South Plant Filter Runtimes].Comments, [South Plant Filter Runtimes].[Failure Step], [South Plant Filter Runtimes].Headloss, [South Plant Filter Runtimes].[Filter Flow Rate], First([Plant Flows].Date) AS FirstOfDate, First([Plant Flows].[South Plant Flow]) AS [FirstOfSouth Plant Flow], First([Plant Flows].[Filter 14 Status]) AS [FirstOfFilter 14 Status], First([Plant Flows].[Filter 15 Status]) AS [FirstOfFilter 15 Status], First([Plant Flows].[Filter 16 Status]) AS [FirstOfFilter 16 Status], First([Plant Flows].[Filter 17 Status]) AS [FirstOfFilter 17 Status], First([Plant Flows].[Filter 18 Status]) AS [FirstOfFilter 18 Status], First([Plant Flows].[Filter 19 Status]) AS [FirstOfFilter 19 Status], First([Plant Flows].[Filter 20 Status]) AS [FirstOfFilter 20 Status], First([Plant Flows].[Filter 21 Status]) AS [FirstOfFilter 21 Status], First([Plant Flows].[Filter 22 Status]) AS [FirstOfFilter 22 Status], First([Plant Flows].[Filter 23 Status]) AS [FirstOfFilter 23 Status], First([Plant Flows].[West 5 MG Tank Level]) AS [FirstOfWest 5 MG Tank Level], First([Plant Flows].[East 5 MG Tank Level]) AS [FirstOfEast 5 MG Tank Level] FROM [Plant Flows] RIGHT JOIN [South Plant Filter Runtimes] ON [Plant Flows].Day = [South Plant Filter Runtimes].Date GROUP BY [South Plant Filter Runtimes].Date, [South Plant Filter Runtimes].[Filter #], [South Plant Filter Runtimes].Time, [South Plant Filter Runtimes].Type, [South Plant Filter Runtimes].Hours, [South Plant Filter Runtimes].Comments, [South Plant Filter Runtimes].[Failure Step], [South Plant Filter Runtimes].Headloss, [South Plant Filter Runtimes].[Filter Flow Rate] HAVING ((([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog]![Beginning Date] And ([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog 2]![Ending Date]) AND (([South Plant Filter Runtimes].[Filter #]) Is Not Null)); SELECT [North Plant Filter Runtimes].Date, [North Plant Filter Runtimes].[Filter #], [North Plant Filter Runtimes].Time, [North Plant Filter Runtimes].Hours, [North Plant Filter Runtimes].Comments, [North Plant Filter Runtimes].[Failure Step], [North Plant Filter Runtimes].[Headloss Trip Setpoint], [North Plant Filter Runtimes].[Filter Flow Rate], First([Plant Flows].Date) AS FirstOfDate, First([Plant Flows].[Filter 3 Status]) AS [FirstOfFilter 3 Status], First([Plant Flows].[Filter 4 Status]) AS [FirstOfFilter 4 Status], First([Plant Flows].[Filter 5 Status]) AS [FirstOfFilter 5 Status], First([Plant Flows].[Filter 7 Status]) AS [FirstOfFilter 7 Status], First([Plant Flows].[Filter 8 Status]) AS [FirstOfFilter 8 Status], First([Plant Flows].[West 5 MG Tank Level]) AS [FirstOfWest 5 MG Tank Level], First([Plant Flows].[East 5 MG Tank Level]) AS [FirstOfEast 5 MG Tank Level], First([Plant Flows].[Filter 6 Status]) AS [FirstOfFilter 6 Status], First([Plant Flows].[North Plant Flow]) AS [FirstOfNorth Plant Flow], [North Plant Filter Runtimes].Type FROM [Plant Flows] RIGHT JOIN [North Plant Filter Runtimes] ON [Plant Flows].Day = [North Plant Filter Runtimes].Date GROUP BY [North Plant Filter Runtimes].Date, [North Plant Filter Runtimes].[Filter #], [North Plant Filter Runtimes].Time, [North Plant Filter Runtimes].Hours, [North Plant Filter Runtimes].Comments, [North Plant Filter Runtimes].[Failure Step], [North Plant Filter Runtimes].[Headloss Trip Setpoint], [North Plant Filter Runtimes].[Filter Flow Rate], [North Plant Filter Runtimes].Type HAVING ((([North Plant Filter Runtimes].Date)=[Forms]![Date Dialog]![Beginning Date] And ([North Plant Filter Runtimes].Date)=[Forms]![Date Dialog 2]![Ending Date])); |
#3
|
|||
|
|||
Troublesome query
On Sat, 21 Jan 2006 16:46:01 -0800, "RWhittet"
wrote: Hi, I have built two different queries, one works, and the second does not work correctly. I am attempting to pull all records from one table, and only the records in the second table which are the first entry of the day. I have puzzled through this several times and am unable to figure out why it's not working correctly. The problem I am having is that the second query is not pulling the first record of the day, where the first query will pull that record every time. Any help is most appreciated. I am pasting the query which isn't working first, and the second is the one which does work. Thanks! SELECT [South Plant Filter Runtimes].Date, [South Plant Filter Runtimes].[Filter #], [South Plant Filter Runtimes].Time, [South Plant Filter Runtimes].Type, [South Plant Filter Runtimes].Hours, [South Plant Filter Runtimes].Comments, [South Plant Filter Runtimes].[Failure Step], [South Plant Filter Runtimes].Headloss, [South Plant Filter Runtimes].[Filter Flow Rate], First([Plant Flows].Date) AS FirstOfDate, First([Plant Flows].[South Plant Flow]) AS [FirstOfSouth Plant Flow], First([Plant Flows].[Filter 14 Status]) AS [FirstOfFilter 14 Status], First([Plant Flows].[Filter 15 Status]) AS [FirstOfFilter 15 Status], First([Plant Flows].[Filter 16 Status]) AS [FirstOfFilter 16 Status], First([Plant Flows].[Filter 17 Status]) AS [FirstOfFilter 17 Status], First([Plant Flows].[Filter 18 Status]) AS [FirstOfFilter 18 Status], First([Plant Flows].[Filter 19 Status]) AS [FirstOfFilter 19 Status], First([Plant Flows].[Filter 20 Status]) AS [FirstOfFilter 20 Status], First([Plant Flows].[Filter 21 Status]) AS [FirstOfFilter 21 Status], First([Plant Flows].[Filter 22 Status]) AS [FirstOfFilter 22 Status], First([Plant Flows].[Filter 23 Status]) AS [FirstOfFilter 23 Status], First([Plant Flows].[West 5 MG Tank Level]) AS [FirstOfWest 5 MG Tank Level], First([Plant Flows].[East 5 MG Tank Level]) AS [FirstOfEast 5 MG Tank Level] Well... just for starters, your table design is not properly normalized. If your PLANT FLOWS table has 23 filter status fields, you're storing data in fieldnames; should this not be split into three tables (Filters, with 23 (or 10??) records and FilterStatus? FROM [Plant Flows] RIGHT JOIN [South Plant Filter Runtimes] ON [Plant Flows].Day = [South Plant Filter Runtimes].Date GROUP BY [South Plant Filter Runtimes].Date, [South Plant Filter Runtimes].[Filter #], [South Plant Filter Runtimes].Time, [South Plant Filter Runtimes].Type, [South Plant Filter Runtimes].Hours, [South Plant Filter Runtimes].Comments, [South Plant Filter Runtimes].[Failure Step], [South Plant Filter Runtimes].Headloss, [South Plant Filter Runtimes].[Filter Flow Rate] HAVING ((([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog]![Beginning Date] And ([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog 2]![Ending Date]) AND (([South Plant Filter Runtimes].[Filter #]) Is Not Null)); Consider changing the HAVING operator to WHERE - the WHERE operator is applied *before* you do calculations, the HAVING operator *afterward*. Using WHERE is not only more efficient, it sometimes gives different answers. Also change the name of the field from [Date] to some name that's not a reserved word. You *will* have problems with Access confusing the field named Date with the VBA system-date function Date. If you're counting on the FIRST() operator to get you the earliest date, you're overoptomistic. It's a VERY limited function - it gets the first record *IN DISK STORAGE ORDER*, which is arbitrary and uncontrollable. Instead, use an additional criterion: .... AND [South Plant Filter Runtimes].[Date] = (SELECT Min([South Plant Filter Runtimes].[Date] FROM [South Plant Filter Runtimes] AS X WHERE all your other criteria ) This subquery will retrieve only the earliest record in the set. John W. Vinson[MVP] |
#4
|
|||
|
|||
Troublesome query
"John Vinson" wrote: Well... just for starters, your table design is not properly normalized. If your PLANT FLOWS table has 23 filter status fields, you're storing data in fieldnames; should this not be split into three tables (Filters, with 23 (or 10??) records and FilterStatus? FROM [Plant Flows] RIGHT JOIN [South Plant Filter Runtimes] ON [Plant Flows].Day = [South Plant Filter Runtimes].Date GROUP BY [South Plant Filter Runtimes].Date, [South Plant Filter Runtimes].[Filter #], [South Plant Filter Runtimes].Time, [South Plant Filter Runtimes].Type, [South Plant Filter Runtimes].Hours, [South Plant Filter Runtimes].Comments, [South Plant Filter Runtimes].[Failure Step], [South Plant Filter Runtimes].Headloss, [South Plant Filter Runtimes].[Filter Flow Rate] HAVING ((([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog]![Beginning Date] And ([South Plant Filter Runtimes].Date)=[Forms]![Date Dialog 2]![Ending Date]) AND (([South Plant Filter Runtimes].[Filter #]) Is Not Null)); Consider changing the HAVING operator to WHERE - the WHERE operator is applied *before* you do calculations, the HAVING operator *afterward*. Using WHERE is not only more efficient, it sometimes gives different answers. Also change the name of the field from [Date] to some name that's not a reserved word. You *will* have problems with Access confusing the field named Date with the VBA system-date function Date. If you're counting on the FIRST() operator to get you the earliest date, you're overoptomistic. It's a VERY limited function - it gets the first record *IN DISK STORAGE ORDER*, which is arbitrary and uncontrollable. Instead, use an additional criterion: .... AND [South Plant Filter Runtimes].[Date] = (SELECT Min([South Plant Filter Runtimes].[Date] FROM [South Plant Filter Runtimes] AS X WHERE all your other criteria ) This subquery will retrieve only the earliest record in the set. John W. Vinson[MVP] Dear John, thank you for your reply. I am curious how you would approach the design of a table like Plant Flows. I know that this database has some major normalization issues, but I don't see another way to approach it. The filter status is only one part of my table. I am also recording Flow rates, tank levels, level setpoints, etc. Rick |
#5
|
|||
|
|||
Troublesome query
On Sat, 21 Jan 2006 18:35:02 -0800, "RWhittet"
wrote: Dear John, thank you for your reply. I am curious how you would approach the design of a table like Plant Flows. I know that this database has some major normalization issues, but I don't see another way to approach it. The filter status is only one part of my table. I am also recording Flow rates, tank levels, level setpoints, etc. If you're assuming that all of this information needs to be stored *in a single table* - much less in a single record! - please reconsider. Relational databases work by storing each type of Entity (real-life thing, event, or person) in its own table. I don't know what your Entities are, but I'm guessing that you should have a table of Tanks, related one to many to (perhaps several) related tables of measurements: each Tank might have any number of TankLevel records. AGAIN - *I don't know your business* - but I can imagine a structure like Tanks TankID Location Capacity any other info about the tank as a physical object TankLevel TankID which tank MeasurementTime date/time, when measured; joint Primary Key Level Comments TankSetpoints TankID SetpointNumber assuming each tank has several this might just be a sequential number, a joint Primary Key with TankID Setpoint I really don't know how you would model flows. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |