Reporting data between 6 'master' tables and 1 'child' table

[valhil2003]

I want to have seven tables (relationship between them already exists) that report the data as follows;

Table 1-6 - Masters: data is input here
Table 7 - Child: data from Master is relayed here

I have tried with a Query to no avail, and even though the master tables have test records populating them I cannot seem to get the necessary fields shown in the Child table... Help!

[zmbd]

You wouldn't do that with Table7 you would do that by query.
So what we need is a little more detail with tables 1 - 6, takeing just three for example:

tbl1
[tbl1_pk]
[tbl1_field1]

tbl2
[tbl2_pk]
[tbl2_field1]
[tbl2_fk_tbl1] (1:M with tbl1)

tbl3
[tbl3_pk]
[tbl3_field1]
[tbl3_fk_tbl1] (1:M with tbl1)
[tbl3_fk_tbl2] (1:M with tbl2)

Then we need to know what you have already attempted, so if you have the SQL (open the query in design view, right click in the show table area, select SQL view from the popup) then click on the [CODE/] button in the post toolbar and then copy and past the SQL script between the [code] [/code] tags.

From there tell us what fields from tbl1-3 you want to show (or in your case tables 1 thru 7 (^_^) ).

[valhil2003]

The SQL is as follows:

SELECT [Employee List].[Employee Name]
   , [Employee List].[Pay rate]
   , [Labor - Entry Scans].Station AS [Labor - Entry Scans_Station]
   , [Labor - Entry Scans].[Entry Scan] AS [Labor - Entry Scans_Entry Scan]
   , [Labor - Exit Scans].Station AS [Labor - Exit Scans_Station]
   , [Labor - Exit Scans].[Exit Scan] AS [Labor - Exit Scans_Exit Scan]
   , [Tracking - Entry Scans].Station AS [Tracking - Entry Scans_Station]
   , [Tracking - Entry Scans].[Entry Scan] AS [Tracking - Entry Scans_Entry Scan]
   , [Tracking - Exit Scans].Station AS [Tracking - Exit Scans_Station]
   , [Tracking - Exit Scans].[Exit Scan] AS [Tracking - Exit Scans_Exit Scan]
   , [Tracking Detail].[Work Ticket ID], [Tracking Detail].ID
FROM [Tracking Detail] 
   INNER JOIN ([Tracking - Exit Scans] 
      INNER JOIN ([Tracking - Entry Scans] 
         INNER JOIN ([Labor - Exit Scans] 
            INNER JOIN ([Labor - Entry Scans] 
               INNER JOIN ([Employee List] 
                  INNER JOIN [Work Tracking] 
                     ON ([Employee List].[Pay rate] 
                        = [Work Tracking].[Rate]) 
                        AND ([Employee List].[Employee Name] 
                           = [Work Tracking].[Employee Name])) 
                  ON [Labor - Entry Scans].[Entry Scan] 
                     = [Work Tracking].[Labor - Entry Scans]) 
               ON [Labor - Exit Scans].[Exit Scan] 
                  = [Work Tracking].[Labor - Exit Scans]) 
            ON [Tracking - Entry Scans].[Entry Scan] 
               = [Work Tracking].[Tracking - Entry Scan]) 
         ON ([Tracking - Exit Scans].[Station] 
             = [Work Tracking].[Station])
            AND ([Tracking - Exit Scans].[Exit Scan] 
                  = [Work Tracking].[Tracking - Exit Scan]))
      ON ([Tracking Detail].[ID] 
          = [Work Tracking].[ID])
         AND ([Tracking Detail].[Work Ticket ID] 
            = [Work Tracking].[Work Ticket ID]);

>>

If you need more details let me know!!

[zmbd]

Exactly how did this SQL fail you?
The more related information you can give, the fewer posts it should take to help you reach your goal.

[valhil2003]

Well, the table is used for tracking inventory. This query picks up all the needed data for reports, views etc and is supposed to be a single place repository for all the needed data to then be used, edited and changed by the users. The issue that I have with the SQL is that I need an action that can report the data input in every single one of the selected fields from the populated master tables.

As of this moment, the relationships work and I get the heading views I want, but none of the already input test data is reporting in the related fields... Help...

[zmbd]

1) Which Version of Access/Office

2) Do you have the privilage of changing the field names?
The spaces are allowed now (however, I still advise against them) but using the dash sign can lead to issues.
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

3) It' will take a little bit to digest your SQL

4) It would help if you would list your tables as I outlined in Post#2 . This way we can see if there are any logic errors in your joins.

5) When you set up your table relationships, did you use the "Wizard" at table level or did you use the Database tools and set the relationships using the relationships tool?

[valhil2003]

1)Access 2013/Office 365

2)I am the sole user (for now) creator and administrator of the file.

3)Sorry about that. The whole idea is to have a query or a central table reporting the required fields from all the other tables. This would be a view only (no edit capability) table for the end users to see.

4)(Due to confidentiality agreements I have changed the names, yet I've left the relationships) * = Primary Key. ** = Current relationship join to main table:

Main Table:
ID*
ENTRY SCAN-A
EXIT SCAN-B
ENAME
RATE
ENTRY SCAN-C
EXIT SCAN-D
STATION
PART_ID

Table 1:
ID**
DESCRIPTION
SIZE
CNAME
CID
ATIME
EPROC

Table 2:
RECORD#*
ID**
PART_ID
ENAME
EID
STATION
ENTRY SCAN-A

Table 3:
RECORD #*
EID
ENAME**
STATION
RATE

Table 4:
RECORD #*
ID**
PART_ID
ENAME
EID
STATION
EXIT SCAN-B

Table 5:
RECORD #*
ID**
PART_ID
STATION
ENTRY SCAN-C

Table 6:
RECORD #*
ID**
PART_ID
STATION
EXIT SCAN-D

That is the table structure. The tables in which RECORD # is the PK is due to the fact that the regular PK, ID will be used multiple times in the same table, as the job moves from station to station, each ID can repeat with different stations, times, etc.

Sorry about the complexity of it :/

[valhil2003]

And 5)I used the relationship wizard on the database tools ribbon (I selected who's related to who)

[zmbd]

I think you need to renormalize\design your database and then your entire issue will dissolve; however, I really can't make heads nor tails of your workflow...

Compounding the issue is that the SQL you posted in 3 doesn't match the tables in posted in 7 so attempting to related the information just doesn't work...

I understand confidentiality; however, we can change Company and part names to generics like "Company" and "BlueWidget1" then post back the SQL and tables so that they make sense.

Please indicate the relationships as I did in post 2

Tbl_Example
[field] PK
[field2] FK((1:M with Tbl_Example2)