Need help with 3 table query

[clubdjlouie]

I have been pounding my head against the wall trying to figure out why this doesnt work! It's so simple yet I do not know how to do this? Any help would appreciated...

Microsoft Access Database

I have three tables...

Table A : "Servers"
Fields : Server, Domain

Table B : "XREF"
Fields : Server, ID, Domain

Table C : "Paper Reconciliation"
Fields : ID, Date, Account, etc


While in a form that shows the data of Table A, I have a button that says "show cross references" It calls a macro named "showxref" and does an OpenQuery on 2 queries (read only). The button works fine to start the macro to start the queries but it returns inaccurate information!

The goal is to Grab the "server: field of the current record that is open (only the one record). Then, Compare this with the "Server" field in the "XREF" table. If it matches, display the ID and a bunch of other info from the "Paper Reconciliation" table. It should continue to search through the database to find all matches. I've had some very limited success with this command "[Forms]![Server Information]![Server]"

Consequently, I need to run this twice, this time search by domain instead of server

Grab the current Domain name of the form that is currently open (only the one record). Then, Compare this with the "Domain" field in the "XREF" table. If it matches, display the ID and a bunch of other info from the "Paper Reconciliation" table. It should continue to search through the database to find all matches. I've had some very limited success with this command
"[Forms]![Server Information]![Domain]"

How do I set this up? I've messed with the relationship arrows and it still does not pull everything up.

an example of how I would expect it to work with dummy data...


Table A : "Servers"
Fields : Server, Domain
server1, domain1
server2, domain1
server3, domain2

Table B : "XREF"
Fields : Server, ID, Domain
server1, 29, domain1
server2, 13, domain1
server3, 6, domain2

Table C : "Paper Reconciliation"
Fields : ID, Date, Account, etc
6, 1/2/02, Adminstrator, blue
13, 2/2/00, Guest, Red
29, 5,13,01, Joe, Green

I open the form and select the see the following from table A...

"Server1, domain1"

I Hit the magic button <poof>
It should check the XREF table and match the server name &quot;Server1&quot;
so it will return ID 29
29 links to table C so it would display &quot;29, 5,13,01, Joe, Green&quot;
Now the second query kicks in
It should check the XFREF table and match &quot;domain1&quot;
This time it will return ID 29 and 13
29 and 13 link to table C so it would display
&quot;server1, 29, domain1&quot;
&quot;server2, 13, domain1&quot;

 

[clubdjlouie]

I GOT IT!! My boss and I (who neither of know anything about Access) sat down for 3.5 hours and finally got it working.

In order to do get it to work, I had to have 4 queries. 1-->2 for Server
and 3-->4 for Domain (note that Query 2 depends on 1 and 4 depends on 3)


Server Query part 1
===================
SELECT SERVERS.Server, SERVERS.Domain, [30PXReF].ID, [30PXReF].Server, [30PXReF].Domain
FROM SERVERS INNER JOIN 30PXReF ON SERVERS.Server = [30PXReF].Server;


Server Query part 2
===================
SELECT [Louie - Show 30Ps Test Selected Servers].SERVERS.Server, [30PPAPER].ID, [30PPAPER].GenericIDs, [30PPAPER].ExceptionType, [30PPAPER].DomainName, [30PPAPER].ApproveDate
FROM [Louie - Show 30Ps Test Selected Servers] INNER JOIN 30PPAPER ON [Louie - Show 30Ps Test Selected Servers].ID = [30PPAPER].ID
WHERE ((([Louie - Show 30Ps Test Selected Servers].SERVERS.Server)=[Forms]![Server Information]![Server]));


Domain Query part 1
===================
SELECT DISTINCT SERVERS.Domain, [30PXReF].ID, [30PXReF].Server, [30PXReF].Domain
FROM SERVERS INNER JOIN 30PXReF ON SERVERS.Domain = [30PXReF].Domain;


Domain Query part 2
===================
SELECT [30PPAPER].ID, [Louie - Show 30Ps Test Selected Domains].SERVERS.Domain, [30PPAPER].GenericIDs, [30PPAPER].ApproveDate, [30PPAPER].ExceptionType
FROM [Louie - Show 30Ps Test Selected Domains] INNER JOIN 30PPAPER ON [Louie - Show 30Ps Test Selected Domains].ID = [30PPAPER].ID
WHERE ((([Louie - Show 30Ps Test Selected Domains].SERVERS.Domain)=[Forms]![Server Information]![Combo142]));


And it works perfectly. The only problem is it dumps the results to two tables but I can live with that.

 

[clubdjlouie]

Note that Query 1 feeds into 2 and 3 feeds into 4

It acts as a prefilter of some sort. we had to match it in one table, get the results of that and match those results to another table to call up the match in the third table.