Broken Query

[lkevinc42036]

Hi, I have two tables which both contain a 'Field 1'. From the second table, 'Field 1' is entered through a dropdown box that comes from the values entered in the first table. I have linked the two tables together but when I make a query to display all 'Field 1' from the second table, and the matching 'Field 1' from the first table, only some from the first table show. Does anybody know why this is?

[R_Badger]

you might want to try copy/pasting the SQL for your query in, it might give us a little idea whats going on.

[dandoescode]

Hard to tell without seeing the data or query, but it sounds like it could be a join issue.

What happens if you do a query like

 SELECT [field 1]  FROM Table2 Left Join Table1 On [field 1] = [field 1]

[lkevinc42036]

my SQL is
SELECT [CPNPP FSAR SOFs1 fixed].[FSAR SOF Number], [CPNPP FSAR SOFs1 fixed].ID1, [CPNPP FSAR SOFs1 fixed].[FSAR SOF Basis of Concurrence], [CPNPP FSAR SOFs1 fixed].[LSD Number and Rev] AS [CPNPP FSAR SOFs1 fixed_LSD Number and Rev], [LSDs & OWNERS_OLD].[LSD Number and Rev] AS [LSDs & OWNERS_OLD_LSD Number and Rev], [LSDs & OWNERS_OLD].[LSD Title]
FROM [CPNPP FSAR SOFs1 fixed] LEFT JOIN [LSDs & OWNERS_OLD] ON [CPNPP FSAR SOFs1 fixed].[LSD Number and Rev] = [LSDs & OWNERS_OLD].[LSD Number and Rev];

Where Table 1 is LSDs & Owners and Table 2 is CPNPP FSAR SOFs and Field 1 is LSD Number and Rev

[dandoescode]

As far as I can tell that should display all values from table 2 and the matching ones from table 1. You shouldn't get every value from tabl1 unless the values for table1.field1 and table2.field2 are identical.

[lkevinc42036]

Thats where the problem arises because every value of field 1 from both tables are identical. Also, because it comes from a dropdown box based on table 1, the values of field 1 from table 2 must be identical.

[R_Badger]

What dandoescode is saying is, since table 1 contains all potential entries, since you data is based on table 2 and only showing matching entries from table 1 if no matching entry to an item in table 1 has been made in table 2, then that item wont be shown.

At least that's what everything seems to point to for me!

[lkevinc42036]

Yes but the thing is that every entry from table 2 has a field 1 that matches with table 2. So theoretically, if I run a query that shows all entries of field 1 from table 2, along with those matching from table 1, it should display the exact same data

[dandoescode]

That is correct, there must be something else going on. You wouldn't mind printing a screen shot of the two tables, and one of the query results would you?

[lkevinc42036]

I am sorry but for security reasons I am not allowed to do that. But do you have any possible ideas on why they are not matching correctly? I also have a third table that also has 'field 1' and when I create a query showing both fields from both tables, absolutely nothing shows up. This makes me believe that there is something going on with Table 1.