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;

 

[Quehay]

Macros can be a big waste of time.


Let's forget the macros. If table 1 has a field that gives a unique join with table 2 on Server = Server AND Domain = Domain then create a query that looks for forms!theform!server (criterion in the server column) and forms!theform!Domain as criterion in the Domain Column. These will be the columns that come from table 1 (the table in the form). This query needs to join the two tables on these fields. Now add table three and join on the ID = ID.

By virtue of the first join you'll have just one ID available for joining with table three. All of the info should come from one query.

Use (my preference) an embedded hyperlink in your form (just use the command button for hyperlinks and choose &quot;Object in this database&quot; then your new query).

If I've misunderstood or explained the approach badly just ask for a restatement!!

 

[Guest_imported]

I think I understand, but I did try joing as you mentioned. I noticed that there are 3 directions to join tables together...

---
<--
-->

does this make a difference? also should I join these fields in the database itself, or I can join in each query?

I swear the problem is in the query not the join

The forms/macro is the least of my problems, I have to get the logic working; I apprecaiate your reply - really

 

[Quehay]

The &quot;arrows&quot; are indicating whether you join:

Equijoin Table1.Field1 = Table2.Field1

Only table rows from either table that find a match
in the corresponding join table field will appear

Left Join Table1.Field1 > Table2.Field1

All rows from table1 will be shown, whether they find
a match from table2 or not

Right Join Table1.Field1 < Table2.Field2

All rows from table2 will be shown, whether they find
a match from table1 or not

Left and Right joins are both OUTER JOINS, the left or right just refers to the &quot;physically&quot; left or right table as the 'preserved' table, or the one that will have all of its rows appear, independent of whether a matching field value is found in the other table.

 

[clubdjlouie]

wow, that is confusing. Does it make a difference whether I set the join at the query level or at the main objects screen of access? I note that if I set them in the queries, they do not affect what is in the when I click the Relationships button.

 

[clubdjlouie]

It still returns no data! here is what I have...

a query named show xrefs

at the top I have ID joined with ID in Equijoin mode

in the query I tried what you said to relate a query that looks for forms!theform!server

Query looks like this...

Field: Domain
Table: XRef
Sort: <blank>
Show: <checked>
Critera: [Forms]![Server Information]![Domain]

next column reads...

Field: Server
Table: XRef
Sort: <blank>
Show: <checked>
Critera: [Forms]![Server Information]![Server]

then I have the columns for the Table C'

Field: ID
Table: Paper Reconciliation
Sort: <blank>
Show: <checked>
Critera: <blank>

etc...

when I run the query, it gives me no matches yet I know there should be many matches?

 

[Quehay]

In the query editor choose View > SQL. Paste the SQL statement that you get into a new post, and we'll look at that!

 

[clubdjlouie]

Good idea, didn't think of that.

SELECT [30P_XRef_Server_Name].Domain, [30P_XRef_Server_Name].Server, [30P Paper Reconciliation Data].ID, [30P Paper Reconciliation Data].[Generic ID(s)], [30P Paper Reconciliation Data].[Exception Type], [30P Paper Reconciliation Data].[Approve Date]
FROM SERVERS, [30P Paper Reconciliation Data] INNER JOIN 30P_XRef_Server_Name ON [30P Paper Reconciliation Data].ID = [30P_XRef_Server_Name].ID
WHERE ((([30P_XRef_Server_Name].Domain)=[Forms]![Server Information]![Domain]) AND (([30P_XRef_Server_Name].Server)=[Forms]![Server Information]![Server]));

What a mess!

 

[Quehay]

The usual lecture: You really ought to get some standard naming conventions in place:

-No spaces--user underscore if needed
- < 30 Characters total is best
- No characters other than text or underscore

Using aliases will greatly simplify the existing query:

[tt]SELECT
XRef.Domain,
XRef.Server,
Paper.ID,
Paper.[Generic ID(s)],
Paper.[Exception Type],
Paper.[Approve Date]
FROM SERVERS, [30P Paper Reconciliation Data] AS Paper
INNER JOIN 30P_XRef_Server_Name AS Xref ON Paper.ID = Xref.ID
WHERE (((Xref.Domain)=[Forms]![Server Information]![Domain]) AND
((Xref.Server)=[Forms]![Server Information]![Server]));[/tt]

 

[Quehay]

Also: There's no join for Servers--where does this fit in?

*Try running the query and get it working without the form parameters first.

 

[clubdjlouie]

Is it possible that the complicated table names/fields could be messing up my query?

 

[clubdjlouie]

I took your advice and simplifed most of the table names

30P Paper Reconciliation Data --> 30PPAPER
30P_XRef_Server_Name --> 30PXREF
Generic ID(s) --> GenericIDs
Exception Type --> ExceptionType
Approve Date --> ApproveDate

I can't change &quot;Server Information&quot; table name, too much linked to it.

Now that everything is easy to read, I would think that this query would look like something like this...

SELECT
30PXREF.Domain,
30PXREF.Server,
30PPAPER.ID,
30PPAPER.[GenericIDs],
30PPAPER.[ExceptionType],
30PPAPER.[Approve Date]
INNER JOIN 30PXREF ON 30PPAPER.ID = 30PXREF.ID
WHERE (((30PXREF.Domain)=[Forms]![Server Information]![Domain]) AND
((30PXREF.Server)=[Forms]![Server Information]![Server]));

>Try running the query and get it working without the form parameters first.
I have been testing it by just clicking on the query and typing sample data that I know should return some results. Just to rule out it is not the form or the macro.

>There's no join for Servers--where does this fit in?
Does there need to be? I don't know how to structure the query to do both servers and domain queries

 

[clubdjlouie]

SELECT [30PXREF].Domain,
[30PXREF].ID,
[30PXREF].Server,
[30PPAPER].ExceptionType,
[30PPAPER].ApproveDate,
[30PPAPER].GenericIDs
FROM SERVERS INNER JOIN (30PPAPER INNER JOIN 30PXREF ON [30PPAPER].ID = [30PXREF].ID) ON (SERVERS.Server = [30PXREF].Server) AND (SERVERS.Domain = [30PXREF].Domain) AND (SERVERS.Server = [30PXREF].Server)
WHERE ((([30PXREF].Domain)=[Forms]![Server Information]![Domain]) AND (([30PXREF].Server)=[Forms]![Server Information]![Server]));

Returns no results when I enter a Domain that I know is in the '30PXREF' table 'Domain' field)

 

[clubdjlouie]

I do not wish to take up any more of your time, maybe if we simplifed everything

Table1
server, domain

Table2
id, data

TableXref
server, domain, id

How do I do a simply query to match table1 to TableXref to obtain the id?

I will be doing two queries since to make sure that I checked for id from both angles (server and domain matches)

?

 

[Quehay]

This is visually more intuitive using ANSI 89 SQL (Access will run this fine):
[tt]
Select cols
From table1, tableXref, table2
WHERE
table1.server = tableXref.Server AND
tableXref.id = table2.ID
[/tt]

Make this work then use the GUI to change the join types, then add your form criteria.

Post again if needed--that's what the forum's for!

 

[clubdjlouie]

So far I can relate to everything but

&quot;Select cols&quot;

? yes I am that thick

 

[clubdjlouie]

I'm sorry, but even that simple sql code doesnt work on access

Select cols
From table1, tableXref, table2
WHERE
table1.server = tableXref.Server AND
tableXref.id = table2.ID

I plugged my names into it

Select cols
From SERVERS, 30PXReF, 30ppaper
WHERE
30ppaper.server = 30PXReF.Server AND
30PXReF.id = 30ppaper.ID

gives a syntax error missing operator


Since I learned that acccess is a real pain with brackets, I fixed it myself...

Select cols
From SERVERS, 30PXReF, 30ppaper
WHERE
[30ppaper].server = [30PXReF].Server AND
[30PXReF].id = [30ppaper].ID

it saves it, when I run it asks me for &quot;Col&quot; and &quot;30ppaper.server&quot;

 

[Quehay]

Select cols was just a generic reference to:

Select table1.Domain, table2.ID, etc.

Sorry to trip you up.

Pardon the pedantic advice, but you should find a good general Access reference book. There are a number out there, and you'll get this info. (I'm happy to spell it out, but you'll find a text reference much more useful.)

Recommended (General):

Using Access, QUE
Access Bible, IDG ?

 

[clubdjlouie]

I have never used access before and i hope not to ever have to do anything more complicated than this in the future. I searched the net and found nothing but access training courses.

This query asks me for the 30PPaper data? it should be asking me for

Table1
server, domain

SELECT [Servers].[Domain], [Servers].[Server], [30ppaper].[ID], [30ppaper].[GenericIDs]
FROM SERVERS, 30PXReF, 30ppaper
WHERE [30ppaper].server=[30PXReF].[Server] And [30PXReF].[id]=[30ppaper].[ID];

Thank you

 

[clubdjlouie]

Well I give up. I appreciate all your help. I still can't get this to work and am burnt out on the whole thing.

I really appreciate your time though