2 Table Query issues

[dohernan via AccessMonster.com]

Below is my current Query.
It behaves oddly. I am rather new to this all.
At this time there is only 1 Record in the Address table, and that address is
used for every name in the 2nd table. It should not be, as new addresses
will be entered for each SSN eventually.

When someone is using a Form to put info into table1 (the personnelForms2009
one),
and chooses Verification from a Drop down combobox, the 2nd Address table is
supposed to be triggered.
Only a Record that has Verification picked and an Address entered into Table2,
and only fields that are not null, should show in this query, so I can easily
run a Report of how many of these letters have been done, and a different
report/Letter that is the actual verification Letter.

Thanks again.


SELECT PersonnelForms2009.LastName, PersonnelForms2009.FirstName,
PersonnelForms2009.SSN AS PersonnelForms2009_SSN,
VerificationLetterInformation.DatesofEmployment,
VerificationLetterInformation.CivilServiceTitle,
VerificationLetterInformation.Salary, VerificationLetterInformation.Address,
VerificationLetterInformation.City, VerificationLetterInformation.State,
VerificationLetterInformation.PostalCode, VerificationLetterInformation.Note,
VerificationLetterInformation.HeleneorJean, VerificationLetterInformation.
CityStartDate, VerificationLetterInformation.VoluntaryRetirement,
VerificationLetterInformation.LastDayPaid, VerificationLetterInformation.
SeasonalEmployee, VerificationLetterInformation.LastDayWorked,
VerificationLetterInformation.ReturnedtoWork
FROM VerificationLetterInformation, PersonnelForms2009;

 

[dohernan via AccessMonster.com]

I added-

WHERE (((PersonnelForms2009.FormType)="Verification"));

 

[John Spencer]

You need a field in VerificationLetterInformation that allows you to tie a
record in that table back to PersonnelForms2009 table. For instance if you
have a PersonID in PersonnelForms2009, you would have a PersonID in
VerificationLetterInformation so you could relate the two tables together.

Right now you have no join specified between the two tables so you will get
one row returned for every combination of the records in the two tables. In
other words, if you have 10 records in PersonnelForms2009 and 5 records in
VerficationLetterInformation you are going to get 50 rows returned (5 * 10).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[dohernan via AccessMonster.com]

I wanted to join them via the SSN, is that doable?
While the SSNs repeat in the first table, they do not repeat in the address
table.

When is Join used versus Union All?
Thanks.

 

[John Spencer]

You use a join when you want to combine records horizontally in the same row.
That is you have one row with fields from table 1 and table 2.

You use UNION queries when you need to combine data vertically in different
rows. That is you have record 1 from table 1 and then record 1 from table 2
in separate rows (records)

It sounds as if you can use the SSN to join records from the two tables. You
MIGHT want to click on the join line and choose the option that shows ALL
records in PersonnelForms2009 table and matching records in
VerificationLetterInformation table. Whether you do or not will depend on
what data you actually need to show.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[dohernan via AccessMonster.com]

Thank you for the explanation.

I want to do a query that only shows Records where the FormType in Table 1 is
"Verification" and the
SSN in the 2nd address table matches a SSN in the 1st table that has a
FormType of Verification.

The SQL below is not working.
SELECT PersonnelForms2009.LastName, PersonnelForms2009.FirstName,
VerificationLetterInformation.Address, VerificationLetterInformation.City
(ETC am cutting out a lot of Fields)
PersonnelForms2009.SSN, VerificationLetterInformation.DatesofEmployment,
FROM VerificationLetterInformation
INNER JOIN PersonnelForms2009 ON VerificationLetterInformation.SSN =
PersonnelForms2009.SSN
WHERE (((PersonnelForms2009.FormType)="Verification") AND (
(VerificationLetterInformation.SSN)=[PersonnelForms2009].[SSN]));

 

[John Spencer]

" is not working" is not a good explanation of what is happening.

Are you getting the wrong records, no records, a syntax error, etc.?

Perhaps what you want is the following. I can't tell when you say "is not
working".

SELECT PersonnelForms2009.LastName
, PersonnelForms2009.FirstName
, VerificationLetterInformation.Address
, VerificationLetterInformation.City
, PersonnelForms2009.SSN
, VerificationLetterInformation.DatesofEmployment,
FROM VerificationLetterInformation
RIGHT JOIN PersonnelForms2009
ON VerificationLetterInformation.SSN =PersonnelForms2009.SSN
WHERE PersonnelForms2009.FormType="Verification"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you for the explanation.

I want to do a query that only shows Records where the FormType in Table 1 is
"Verification" and the
SSN in the 2nd address table matches a SSN in the 1st table that has a
FormType of Verification.

The SQL below is not working.
SELECT PersonnelForms2009.LastName, PersonnelForms2009.FirstName,
VerificationLetterInformation.Address, VerificationLetterInformation.City
(ETC am cutting out a lot of Fields)
PersonnelForms2009.SSN, VerificationLetterInformation.DatesofEmployment,
FROM VerificationLetterInformation
INNER JOIN PersonnelForms2009 ON VerificationLetterInformation.SSN =
PersonnelForms2009.SSN
WHERE (((PersonnelForms2009.FormType)="Verification") AND (
(VerificationLetterInformation.SSN)=[PersonnelForms2009].[SSN]));

You use a join when you want to combine records horizontally in the same row.
That is you have one row with fields from table 1 and table 2.

 

[dohernan via AccessMonster.com]

Thank you, that pulled every record with "Verification" in it. I set it to
sort alphabetically.

The issue is that there is atm just 1 address in the 2nd
VerificationLetterInformation table, and it didn't show up when the Query was
run.
It should have found the matching SSN and filled in the fields, twice,
because that one person got Verification Letters on 2 different dates.

I'm also thinking of separating the Last/First name Fields into their own
table and am afraid of the mess that will cause all of the
forms/queries/reports that Are working atm.

Thanks again.


SELECT PersonnelForms2009.LastName, PersonnelForms2009.FirstName,
PersonnelForms2009.SSN AS PersonnelForms2009_SSN,
VerificationLetterInformation.DatesofEmployment,
VerificationLetterInformation.CivilServiceTitle,
VerificationLetterInformation.Salary, VerificationLetterInformation.Address,
VerificationLetterInformation.City, VerificationLetterInformation.State,
VerificationLetterInformation.PostalCode, VerificationLetterInformation.Note,
VerificationLetterInformation.HeleneorJean, VerificationLetterInformation.
CityStartDate, VerificationLetterInformation.VoluntaryRetirement,
VerificationLetterInformation.LastDayPaid, VerificationLetterInformation.
SeasonalEmployee, VerificationLetterInformation.LastDayWorked,
VerificationLetterInformation.ReturnedtoWork
FROM VerificationLetterInformation RIGHT JOIN PersonnelForms2009 ON
VerificationLetterInformation.SSN = PersonnelForms2009.SSN
WHERE (((PersonnelForms2009.FormType)="Verification"))
ORDER BY PersonnelForms2009.LastName;