Need help with query/sql statement

G

Guest

Hi all..

Here is what I have:

I have a query that is running a search form. The query is made up of three
tables:

1.tblMain
2.tblSpouse
3.tblChildren

When I have the query looking at just tables 1 and 2 it works fine. But when
I put in table three my query doesn't work correctly.

Here is what it is doing. On my search form I have a main window that lists
all of the entries into the database. I then have two subforms one for spouse
and one for children. The spouse now shows this:

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name
Janine
Janine
Janine
Sally
Sally
Sally

(two wives, 1 current, 1 past)

And the children show this

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name

David
David
Mines
Mines
Foy
Foy

And the one entry that does not have any children is no longer showing up in
the main window with the other entries.

Here is the sql of the query:

SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber =
tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber =
tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblMain.DriversLicNumber) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.MInitial)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber, tblChildren.EntryNumber;

Sorry for the long post but I wanted to make sure most if not everything was
covered.

Thanks
R~
 
J

John Spencer

Try
-- changing the SQL to return Distinct rows
-- changing the joins from INNER JOIN to LEFT JOIN (This may fail to give
you the desired results since your criteria is applied against the Spouse
and Children tables.)

Your WHERE Clause could also use some work.

This doesn't work the way you think it does.
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

Perhaps you mean
WHERE ((([tblMain]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] &
"*" AND
[tblSpouse]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] & "*"
AND
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

Or maybe you mean
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

But it seems redundant to do that since if TblMain.EntryNumber is a match
then by definition the other two will be a match based on your join
statement

SELECT DISTINCT
tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain LEFT JOIN tblChildren
ON tblMain.EntryNumber = tblChildren.EntryNumber)
LEFT JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber
WHERE
((([tblMain]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblChildren.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")))

ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber,
tblChildren.EntryNumber;


Rhett_Y said:
Hi all..

Here is what I have:

I have a query that is running a search form. The query is made up of
three
tables:

1.tblMain
2.tblSpouse
3.tblChildren

When I have the query looking at just tables 1 and 2 it works fine. But
when
I put in table three my query doesn't work correctly.

Here is what it is doing. On my search form I have a main window that
lists
all of the entries into the database. I then have two subforms one for
spouse
and one for children. The spouse now shows this:

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name
Janine
Janine
Janine
Sally
Sally
Sally

(two wives, 1 current, 1 past)

And the children show this

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name

David
David
Mines
Mines
Foy
Foy

And the one entry that does not have any children is no longer showing up
in
the main window with the other entries.

Here is the sql of the query:

SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber =
tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber =
tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))
OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblMain.DriversLicNumber) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.MInitial)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber,
tblChildren.EntryNumber;

Sorry for the long post but I wanted to make sure most if not everything
was
covered.

Thanks
R~
Click to expand...
 
G

Guest

John..

I tried what you recommended but I am getting these type of results now:

Spouse 1
Child 1
Spouse 1
Child 2
Spouse 1
Child 3

Example 2
Spouse 1
Child 1
Spouse 1
Child 2

Spouse 2
Child 1
Spouse 2
Child 2

If the person has more then one child it is showing up like the above....

Hope this makes sense...

R~

John Spencer said:
Try
-- changing the SQL to return Distinct rows
-- changing the joins from INNER JOIN to LEFT JOIN (This may fail to give
you the desired results since your criteria is applied against the Spouse
and Children tables.)

Your WHERE Clause could also use some work.

This doesn't work the way you think it does.
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

Perhaps you mean
WHERE ((([tblMain]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] &
"*" AND
[tblSpouse]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] & "*"
AND
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

Or maybe you mean
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

But it seems redundant to do that since if TblMain.EntryNumber is a match
then by definition the other two will be a match based on your join
statement

SELECT DISTINCT
tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain LEFT JOIN tblChildren
ON tblMain.EntryNumber = tblChildren.EntryNumber)
LEFT JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber
WHERE
((([tblMain]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblChildren.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")))

ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber,
tblChildren.EntryNumber;


Rhett_Y said:
Hi all..

Here is what I have:

I have a query that is running a search form. The query is made up of
three
tables:

1.tblMain
2.tblSpouse
3.tblChildren

When I have the query looking at just tables 1 and 2 it works fine. But
when
I put in table three my query doesn't work correctly.

Here is what it is doing. On my search form I have a main window that
lists
all of the entries into the database. I then have two subforms one for
spouse
and one for children. The spouse now shows this:

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name
Janine
Janine
Janine
Sally
Sally
Sally

(two wives, 1 current, 1 past)

And the children show this

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name

David
David
Mines
Mines
Foy
Foy

And the one entry that does not have any children is no longer showing up
in
the main window with the other entries.

Here is the sql of the query:

SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber =
tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber =
tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))
OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblMain.DriversLicNumber) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.MInitial)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber,
tblChildren.EntryNumber;

Sorry for the long post but I wanted to make sure most if not everything
was
covered.

Thanks
R~
Click to expand...
Click to expand...
 
G

Guest

John...

What I want it to do is this:

employee #1
Spouse 1
Child 1
Child 2

Or

employee 1
spouse 1
spouse 2
child 1
child 2
child 3
child 4

not

employee 1
spouse 1
spouse 2
child 1
child 1
child 2
child 2

I hope this makes sense

R~

John Spencer said:
Try
-- changing the SQL to return Distinct rows
-- changing the joins from INNER JOIN to LEFT JOIN (This may fail to give
you the desired results since your criteria is applied against the Spouse
and Children tables.)

Your WHERE Clause could also use some work.

This doesn't work the way you think it does.
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

Perhaps you mean
WHERE ((([tblMain]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] &
"*" AND
[tblSpouse]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] & "*"
AND
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

Or maybe you mean
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))

But it seems redundant to do that since if TblMain.EntryNumber is a match
then by definition the other two will be a match based on your join
statement

SELECT DISTINCT
tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain LEFT JOIN tblChildren
ON tblMain.EntryNumber = tblChildren.EntryNumber)
LEFT JOIN tblSpouse ON tblMain.EntryNumber = tblSpouse.EntryNumber
WHERE
((([tblMain]![EntryNumber] Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR
(((tblChildren.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")))

ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber,
tblChildren.EntryNumber;


Rhett_Y said:
Hi all..

Here is what I have:

I have a query that is running a search form. The query is made up of
three
tables:

1.tblMain
2.tblSpouse
3.tblChildren

When I have the query looking at just tables 1 and 2 it works fine. But
when
I put in table three my query doesn't work correctly.

Here is what it is doing. On my search form I have a main window that
lists
all of the entries into the database. I then have two subforms one for
spouse
and one for children. The spouse now shows this:

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name
Janine
Janine
Janine
Sally
Sally
Sally

(two wives, 1 current, 1 past)

And the children show this

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name

David
David
Mines
Mines
Foy
Foy

And the one entry that does not have any children is no longer showing up
in
the main window with the other entries.

Here is the sql of the query:

SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber =
tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber =
tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))
OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblMain.DriversLicNumber) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.MInitial)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber,
tblChildren.EntryNumber;

Sorry for the long post but I wanted to make sure most if not everything
was
covered.

Thanks
R~
Click to expand...
Click to expand...
 
G

Guest

Hi again..

I have got the child portion working.. but now I am getting the following
results:

employee #1
Spouse #1
child #1
Child #2

Spouse #2
Child #1
Child #2

I would like it to just show me the spouse and children without repeating
the spouse. Any ideas?

Here is the sql I am working on:

SELECT DISTINCT tblMain.EntryNumber, tblMain.DriversLicNumber,
tblMain.EmpNumber, tblMain.FName, tblMain.LName, tblMain.MInitial,
tblSpouse.LName, tblSpouse.FName, tblSpouse.MInitial,
tblSpouse.DriversLicNumber, tblChildren.LName, tblChildren.FName,
tblChildren.MInitial, tblChildren.DriversLicNumber,
tblStates.StateAbbreviation, tblCities.CityName, *
FROM tblStates, tblCities, (tblMain LEFT JOIN tblChildren ON
tblMain.EntryNumber = tblChildren.EntryNumber) LEFT JOIN tblSpouse ON
tblMain.EntryNumber = tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR (((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR (((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblSpouse.MInitial) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblChildren.LName) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblChildren.FName) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblChildren.DriversLicNumber) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.MInitial) Like "*" &
[Forms]![frmSearch]![Search2] & "*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber, tblChildren.EntryNumber;


Thanks
R~


Rhett_Y said:
Hi all..

Here is what I have:

I have a query that is running a search form. The query is made up of three
tables:

1.tblMain
2.tblSpouse
3.tblChildren

When I have the query looking at just tables 1 and 2 it works fine. But when
I put in table three my query doesn't work correctly.

Here is what it is doing. On my search form I have a main window that lists
all of the entries into the database. I then have two subforms one for spouse
and one for children. The spouse now shows this:

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name
Janine
Janine
Janine
Sally
Sally
Sally

(two wives, 1 current, 1 past)

And the children show this

Last Name
Washington
Washington
Washington
Washington
Washington
Washington

First Name

David
David
Mines
Mines
Foy
Foy

And the one entry that does not have any children is no longer showing up in
the main window with the other entries.

Here is the sql of the query:

SELECT tblMain.EntryNumber, tblMain.DriversLicNumber, tblMain.EmpNumber,
tblMain.FName, tblMain.LName, tblMain.MInitial, tblSpouse.LName,
tblSpouse.FName, tblSpouse.MInitial, tblSpouse.DriversLicNumber,
tblChildren.LName, tblChildren.FName, tblChildren.MInitial,
tblChildren.DriversLicNumber, *
FROM (tblMain INNER JOIN tblChildren ON tblMain.EntryNumber =
tblChildren.EntryNumber) INNER JOIN tblSpouse ON tblMain.EntryNumber =
tblSpouse.EntryNumber
WHERE ((([tblMain]![EntryNumber] And [tblSpouse]![EntryNumber] And
[tblChildren]![EntryNumber]) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
OR (((tblMain.EmpNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblMain.MInitial) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.LName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.FName) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblSpouse.DriversLicNumber) Like "*" & [Forms]![frmSearch]![Search2] &
"*")) OR (((tblMain.DriversLicNumber) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblSpouse.MInitial) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.LName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.FName) Like "*" &
[Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.DriversLicNumber)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((tblChildren.MInitial)
Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR
(((tblChildren.EntryNumber) Like "*" & [Forms]![frmSearch]![Search2] & "*"))
ORDER BY tblMain.EntryNumber, tblSpouse.EntryNumber, tblChildren.EntryNumber;

Sorry for the long post but I wanted to make sure most if not everything was
covered.

Thanks
R~
Click to expand...
 
Top