Conceptual Problem I need Help With

[Bill Bisco]

In my database I have tblStations and tblProcesses. The Primary Key for tblStations is StationID. Every Station has many Processes.

However, there are two fields that link tblProcesses to tblStations. CurrentID and ProposedID. For example. I could have a form which displayed Station 1 with all the Processes that had StationID = CurrentID or another form that displayed Station 1 and all the Processes that had StationID = Proposed ID.

Sometimes the CurrentID will be the same as ProposedID and sometimes it won't.

What I want to do is in my form, display all the Processes associated with station with StationID=ProposedID, but also have a Field which denotes where the Process is Currently.

It would look something like this:

Station          Process Name      Current Location     Time
Station 1 LS   Jumping Up          Station 4 RS           0.5
Station 1 LS   Jumping Down      Station 1 LS           0.8
Station 1 LS   Jumping Left        Station 1 LS           0.6
Station 1 LS   Jumping Right      Station 2 LS            0.2

My mental issue in figuring out how to display information similar to the above is that my StationID is a Number. For Instance, the StationID for Station 1 LS is 1 not Station 1 LS.

I don't want to display data that looks like this:

Station          Process Name      Current Location     Time
Station 1 LS   Jumping Up          8                          0.5
Station 1 LS   Jumping Down      1                          0.8
Station 1 LS   Jumping Left        1                          0.6
Station 1 LS   Jumping Right       3                         0.2

Any help in lifting my mental block so that I can display this information the way I want to would be appreciated.

Sincerely,
Bill

 

[Bill Bisco]

I have solved this issue on my own. The key is to add yet another query containing CurrentID and connect it to the CurrentID in my main query. I get the feeling that Access is just a very strange interconnected tangled web of queries that we keep on making. Strange that subqueries don't have their own category in the hierarchy of Tables, Forms, Reports, etc.

 

[boblarson]

Strange that subqueries don't have their own category in the hierarchy of Tables, Forms, Reports, etc.

Not really because just like subforms are just a form or subreports are just a report, they are just another query.

 

[Bill Bisco]

Right, but now my list of queries is very big and it is going to grow bigger as I add more forms and want to organize information differently!

Edit: And well, darn, it seems that another problem has occurred because the totals in my main query are wrong. It thinks that I'm adding up all the Processes in my subquery to my Main Query, and some things are twice the amount it should be and others even more!

Edit2: placed the subquery differently and everything's okay now.