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

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

p.Index = ps.PumpIndex AND
p.Index = pc.PumpIndex
BY p.Index
ORDER BY whatever

< print $array[""]''s station $array[]

do while $resultarray != null (or whatever)

print $array[""]''s station $array[] 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

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

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

As for the table structures:


//etc - the rest is unimportant


//etc - rest unimportant



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.


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.


Share this post

Link to post
Share on other sites
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,

Share this post

Link to post
Share on other sites