Tables in DB

[TotallyConfused]

I am currently using an Access Template from Microsoft. As I am going
through the database, there are 3 tables that are being used in a query that
are exactly the same. Two of the tables have different names. However,
these two copied tables are not listed in the tables tab? There are no
queries that show "Make Table". How is this done and why? Thank you for any
help you can provide.

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

I'm guessing that the other two tables end in something like _1 and _2 .
This is the same table as the root name, but there's been self-join on them.

 

[TotallyConfused]

Thank you for responding. I am trying to understand this design to determine
if I can apply to my data. Thank you so much.


SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " &
[Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened
By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By
Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date],
[Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail],
[Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail],
Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To],
Issues.[Opened By], Issues.ReqFNM, Issues.ReqLNM, Issues.ReqPH, Issues.ReqFX,
Issues.ReqID, Issues.DateReq, Issues.Purpose, Issues.BusReason,
Issues.RptTypeNM, Issues.RptTypeSource, Issues.TimeFrame, Issues.StartDate,
Issues.EndDate, Issues.Products, Issues.[Business Line], Issues.NetworkID,
Issues.Region, Issues.NetworkNM, Issues.ProviderType, Issues.Language,
Issues.Specialties, Issues.Completed, Issues.SQL, Issues.Sample,
Issues.Information, Issues.RptTypeInfo, Issues.PrvdrInfo, Issues.NtwrksInfo,
Issues.DescOther, [Contacts_Opened By].*
FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS
[Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID =
Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]
WHERE (((Issues.Status)<>"Closed"));

 

[John W. Vinson]

Thank you for responding. I am trying to understand this design to determine
if I can apply to my data. Thank you so much.

It is the same table under two different alias names. The FROM clause in the
SQL gives it away:

FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS
[Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID =
Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]

The query includes the contacts table twice - once to display the person who
opened the issue, and once to display the person to whom the issue was
assigned. Both these people are in the contacts table; they might be the same
person or different people, but including the table in the query twice lets
you display personal information about the people performing these two roles
as separate fields.

John W. Vinson [MVP]