Archived

This topic is now archived and is closed to further replies.

ms access

This topic is 5502 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, How should I go agout the following problem in MS Access? I have three tables, one containing info about pumps, another info about pumpung stations and the third info about check-ups on any one of the pumps. I need to construct a query that: 1) Selects each pump by its serial number (easy) 2) Finds out which pumping station it is in (not so easy, but doable) 3) Finds the description and date of each pump (both the description and date are stored in the same (check-ups) table) - this is the tricky part What I figure is that I need a for-each kind of query that queries all of the underlying tables for each individual pump. Can I do this using only the query structures supplied by MS Access or do I have to compile the query piece by piece in VBA? In that case, how do I bind the resulting recordset to a report? Prompt help would be very much appreciated, Crispy

Share this post


Link to post
Share on other sites
Hey,

I have never used MS Access but it might be the standard way..
So... You do have an ID for each pump i guess, one main thats located in the pump table, one in the station and one in the checkups?

if so, cant you just do something like


!resultarray = query (
SELECT * FROM pump as p, pump_station as ps, pump_checkups as pc

WHERE
p.Index = ps.PumpIndex AND
p.Index = pc.PumpIndex
GROUP
BY p.Index
ORDER BY whatever
)


< print $array["p.name"]''s station $array[ps.name]

do while $resultarray != null (or whatever)

print $array["p.name"]''s station $array[ps.name] check
end do




My homemade languange but you get the point.

fatal error C1004: unexpected end of file found

Share this post


Link to post
Share on other sites
While in the Query Design screen click on the view menu and select "SQL View", from here you can do advanced queries by entering your query directly into SQL. If I had more information on your table structure (which items are stored in which tables and what exactly you want to get) I could help you in constructing an SQL query.

Share this post


Link to post
Share on other sites
Dies_Irae: can't get your method working - it returns wrong stuff. Maybe it's my fault, though - I'm not too experienced at creating sql statements... Here's an Access's mummification of the thing - enjoy

SELECT *
FROM TPumpChecksHistory AS pc, TPump AS p, TPumpingStation AS ps
WHERE (((ps.PUMP1ID)=[p].[PumpID]) AND ((pc.PumpID)=[p].[PumpID])) OR (((pc.PumpID)=[p].[PumpID]) AND ((ps.PUMP2ID)=[p].[PumpID])) OR (((pc.PumpID)=[p].[PumpID]) AND ((ps.PUMP3ID)=[p].[PumpID]))
ORDER BY pc.Date DESC;


Note that half of the syntax is not written by me...



As for the table structures:

TPump

      
ID
CheckPeriod
SerialNumber
//etc - the rest is unimportant



TPumpingStation

        
ID
Name
Pump1ID
Pump2ID
Pump3ID
//etc - rest unimportant



TPumpChecksHistory

        
ID
PumpID
Date
Description


I need the following output as a report:


PumpingStation.Name | Pump.SerialNumber | Pump.CheckPeriod | LastCheckupDescriptionAndDateForTheGivenPump


Basically, I need to find the latest entry for each pump in the TPumpChecksHistory table - this should be clearer. A slightly complicating matter is the fact that the pump is stored in a pumping station, not vice versa, forcing all the pumping stations to be checked (and all of the three pumps in them) in order to find out where the pump is at.

Crispy

edit: typo


[edited by - crispy on November 19, 2002 3:44:31 PM]

Share this post


Link to post
Share on other sites
SELECT TPumpingStation.Name,TPump.SerialNumber, TPump.CheckPeriod, TPumpChecksHistory.Description, TPumpChecksHistory.Date


That should give you the order for your output.

One other thing have you thought about putting a pumpstation id into the TPumpChecksHistory table this might make it a lot easier.

Cheers

Share this post


Link to post
Share on other sites
quote:
Original post by Themonkster
SELECT TPumpingStation.Name,TPump.SerialNumber, TPump.CheckPeriod, TPumpChecksHistory.Description, TPumpChecksHistory.Date



The problem is in getting the latest TPumpChecksHistory.Description and TPumpChecksHistory.Date for each pump (there can be any number of check-ups for each pump) - your''s just selects everything.

Thanks for the reply, anyhow,
Crispy


Share this post


Link to post
Share on other sites