Access Query not functioning completely.

[Guest]

I created a query with two tables, ID numbers in common. I joined the IDN in
both, but only the information from one table is showing in the results. I've
done a hundred other queries like this. Did I "set" something that I
shouldn't have? Hope someone can help.

 

[Guest]

Do you have any fields from the other table in the Field row of the QBE grid?
If so is there a checkmark in the Show row?

If that doesn't answer the question, show us the SQL. Open the query in
design view. Next go to View, SQL View and copy and past it here. Information
on primary keys and relationships would be a nice touch too.

 

[Guest]

Thank you for your quick reply!!
All Show boxes are checked. I have all the columns requested for the other
table, but they are empty.

SQL as requested. Keys in both tables are the IDNs.
SELECT [AENT Submissions].IDN, [AENT Submissions].[Last Name], [DB--GR
Student Master].[First Name], [AENT Submissions].Term, [AENT
Submissions].Application, [AENT Submissions].Eligible, [AENT
Submissions].[AENT Notes], [AENT Submissions].[Fam Low Inc], [AENT
Submissions].Consideration, [DB--GR Student Master].[Phone Number], [DB--GR
Student Master].[EMail Address], [DB--GR Student Master].Major, [DB--GR
Student Master].Street, [DB--GR Student Master].City, [DB--GR Student
Master].State, [DB--GR Student Master].ZipCode
FROM [AENT Submissions] LEFT JOIN [DB--GR Student Master] ON [AENT
Submissions].IDN = [DB--GR Student Master].IDN
WHERE ((([AENT Submissions].Term) Like "FY07*"))
ORDER BY [AENT Submissions].[Last Name];

 

[Guest]

In that case it just means that there isn't any matching data. You have a
Left Join otherwise no records would be returned. Double check that there are
matching records in the both IDN fields. Are there really two dashes in the
[DB--GR Student Master] table name?

It could also be that your WHERE clause eliminates any records with matches
between the two tables. For testing remove the WHERE clause and see what
happens.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thank you for your quick reply!!
All Show boxes are checked. I have all the columns requested for the other
table, but they are empty.

SQL as requested. Keys in both tables are the IDNs.
SELECT [AENT Submissions].IDN, [AENT Submissions].[Last Name], [DB--GR
Student Master].[First Name], [AENT Submissions].Term, [AENT
Submissions].Application, [AENT Submissions].Eligible, [AENT
Submissions].[AENT Notes], [AENT Submissions].[Fam Low Inc], [AENT
Submissions].Consideration, [DB--GR Student Master].[Phone Number], [DB--GR
Student Master].[EMail Address], [DB--GR Student Master].Major, [DB--GR
Student Master].Street, [DB--GR Student Master].City, [DB--GR Student
Master].State, [DB--GR Student Master].ZipCode
FROM [AENT Submissions] LEFT JOIN [DB--GR Student Master] ON [AENT
Submissions].IDN = [DB--GR Student Master].IDN
WHERE ((([AENT Submissions].Term) Like "FY07*"))
ORDER BY [AENT Submissions].[Last Name];

Do you have any fields from the other table in the Field row of the QBE grid?
If so is there a checkmark in the Show row?

If that doesn't answer the question, show us the SQL. Open the query in
design view. Next go to View, SQL View and copy and past it here. Information
on primary keys and relationships would be a nice touch too.

 

[Guest]

Yes, there are two dashes in the name.
Your response didn't make sense at first, but it got me thinking in a
different direction. I rechecked my tables (I knew the problem was with the
newer one) and I found my error. I had formatted the IDN in the Master table,
but not in the second table.
And sometimes, it's just as simple/stupid as that.
Thanks so much for your time! I appreciate the help.

In that case it just means that there isn't any matching data. You have a
Left Join otherwise no records would be returned. Double check that there are
matching records in the both IDN fields. Are there really two dashes in the
[DB--GR Student Master] table name?

It could also be that your WHERE clause eliminates any records with matches
between the two tables. For testing remove the WHERE clause and see what
happens.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thank you for your quick reply!!
All Show boxes are checked. I have all the columns requested for the other
table, but they are empty.

SQL as requested. Keys in both tables are the IDNs.
SELECT [AENT Submissions].IDN, [AENT Submissions].[Last Name], [DB--GR
Student Master].[First Name], [AENT Submissions].Term, [AENT
Submissions].Application, [AENT Submissions].Eligible, [AENT
Submissions].[AENT Notes], [AENT Submissions].[Fam Low Inc], [AENT
Submissions].Consideration, [DB--GR Student Master].[Phone Number], [DB--GR
Student Master].[EMail Address], [DB--GR Student Master].Major, [DB--GR
Student Master].Street, [DB--GR Student Master].City, [DB--GR Student
Master].State, [DB--GR Student Master].ZipCode
FROM [AENT Submissions] LEFT JOIN [DB--GR Student Master] ON [AENT
Submissions].IDN = [DB--GR Student Master].IDN
WHERE ((([AENT Submissions].Term) Like "FY07*"))
ORDER BY [AENT Submissions].[Last Name];

Do you have any fields from the other table in the Field row of the QBE grid?
If so is there a checkmark in the Show row?

If that doesn't answer the question, show us the SQL. Open the query in
design view. Next go to View, SQL View and copy and past it here. Information
on primary keys and relationships would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I created a query with two tables, ID numbers in common. I joined the IDN in
both, but only the information from one table is showing in the results. I've
done a hundred other queries like this. Did I "set" something that I
shouldn't have? Hope someone can help.