Troublesome query

R

RWhittet

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]));
 
R

RWhittet

I figured it out, thanks anyway.

RWhittet said:
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]));
Click to expand...
 
J

John Vinson

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]
Click to expand...

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));
Click to expand...

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]
 
R

RWhittet

:

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));
Click to expand...

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]
Click to expand...
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
 
J

John Vinson

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.
Click to expand...

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to record a sheet change showing row column sheet name and date? 1
Query Resulting in Duplicate Records 3
Template for 40-year quarterly cash flow mixed with some monthly flows 1
Problems Loading Large String Array into Array variable 6
Slow VBA-Generated SQL Involving Totals Query 9
Query is too complex 17
Query a Large Database - Form & Sub form 0
Question regarding Access 1

Top