Beginner to Access: Query Question (1 Viewer)

nsa10124

New member
Local time
Today, 07:09
Joined
Nov 10, 2017
Messages
7
Hello All,

This is my first post here, so I will give a bit of background on myself. I am a Mechanical Engineer a few years out of college, and I am working for a small company that repairs Aero-derivative gas turbine parts. I am trying to design an Access database to store information and track each job that goes through our shop (each set of parts we repair).
I have a tiny bit of Access experience, and "a lot" of Excel/VBA experience. I designed one "database" for managing all of the calibrated tooling we have, but it uses Access, but was more of a VBA implementation. I did not have any relationships established, and used all VBA/SQL to enter data from forms into tables.

My Question:

If I run a query on 3 filled tables I get some results. But if I add a 4th table with zero records, I do not get any results.
Could someone please explain this to me, I am clearly missing something.
In some cases the 4th table will have data that should be pulled, but in other cases the 4th table will not have data.
See the attached picture for the example Query and associated Tables.



Thanks!
Nathan
 

Attachments

  • Query1.png
    Query1.png
    62.9 KB · Views: 96

plog

Banishment Pending
Local time
Today, 07:09
Joined
May 11, 2011
Messages
11,611
INNER JOINS (which is the type you have) act as criteria between tables. When you JOIN tables like that, only records that match between the two tables will show in the results. You have no records in tbl_EOMNS, therefore there are no matches that can be made between tbl_EOMNS and tbl_SN.

If you remove tbl_EOMNS from the query, then you have essentially eliminated a criteria from the query and you get the results you see in the second query.

Extra credit--if you change the INNER JOIN to a LEFT JOIN which shows all records from tbl_SN and just those matching records in tbl_EOMNS you will get the results of the second query while still including tbl_EOMNS in the query.
 

nsa10124

New member
Local time
Today, 07:09
Joined
Nov 10, 2017
Messages
7
plog,

Thank you for your response. That answers the why, though I need to read more on what all of the different SQL functions do.
How would I build this query if I want to have results, even if there is no data in the tbl_OEMSN?
I know that I could add a record to tbl_OEMSN with a Null value in fld_OEMSN for each record in tbl_SN, but this doesn't seem like the right thing to do. I would end up with thousands of Null records.

Thanks,
Nathan
 

plog

Banishment Pending
Local time
Today, 07:09
Joined
May 11, 2011
Messages
11,611
In Design View of the query, right click on the line that connects tbl_OEMSN and tbl_SN and do what I described in the extra credit section of my first post.
 

nsa10124

New member
Local time
Today, 07:09
Joined
Nov 10, 2017
Messages
7
plog,

Awesome! Somehow I overlooked the Extra Credit portion on my first read-through.
Now I need to go through my relationships and evaluate when I need left/right joins as opposed to inner joins.

Tyvm,
Nathan
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:09
Joined
Feb 28, 2001
Messages
26,996
nsa10124

If you REALLY want to get things interesting, read up on relationships. If you build a query and you have previously defined relationships among the fields of your various tables (where appropriate, of course), the query builder tool SEES and USES the relationship and will get the JOINs either exactly right or very close to right. Saves time and confusion. You just have to get the relationship right first. Which is why I said to read up on it. There ARE pitfalls, but once you get it, establishing a relationship accurately becomes a massive tool in your tool box.
 

Users who are viewing this thread

Top Bottom