Need help with query/sql statement

[rhett7660]

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~

 

[pdx_man]

You are doing an INNER JOIN on tblChildren. If there are no children, then the there is nothing for the main or spouse tables to match to. Try a LEFT JOIN on children.

 

[rhett7660]

pdx man..

Hi again.... I tried the left join and I am still getting the same results however I am now seeing the person who doesn't have any children.. So that problem is solved.

Is there something else I need to do in order for the names not to repeat like they are in my first post? I am also attaching a view of my relationship within the database. This maybe a part of the problem I dont' know....

Here is the revised sql statement:

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, tblSpouse.EntryNumber, tblChildren.EntryNumber, *
FROM (tblMain LEFT 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;

Thanks
R~

 

[KeithG]

Are they all duplicate records? You could insert Distint after Select in your SQL statement.

 

[rhett7660]

KeithG..

They are not duplicates... From what I can see it is doing this:

If there is an employee and a spouse it shows it, employee and child it shows it......spouse and child etc.....

Hope this makes sense.....

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, tblSpouse.EntryNumber, tblChildren.EntryNumber, *
FROM (tblMain LEFT 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;

R~

 

[rhett7660]

Hi..

I think I figured it out..... for the children portion, but now I am getting the same thing for the spouse still......

I am still getting one employee with three spouses if there are three children..

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

Make sense.. I attached a stripped down version.... Use the form frmSearch and qryNames

R~

 

[KeithG]

After looking at your sql statement I believe your problem is the Spouce Entity Field and Children Entity field. It looks like there are more than one SpouceEntityNumber or ChildreEntityNumber meeting the criteria of your query. How come you dont search for a specific number instead of using the *?

 

[rhett7660]

After talking to the person(s) who is going to be using this.. they want to be able to searh all the fields I have...because they may get info on the spouse, but if the spouse has a different last name they want to be able to search that last name from the spouse field and see who it belongs to..same with children etc.. they wanted to be able to search a bunch of different fields.....They may get a drivers lic/last name/first name.. etc

Hope I answered your question........ Hence the wild card "*".....

The way I set it up.. is you can have 1 employee with multipal spouses and children.... Due to marriages/divorces/adding a new one to the family etc...

I changed the sql code to the following:

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;

R~

 

[KeithG]

How come you have a one to many relation between the Spouce table and tblmain? You are getting the numerous records because each child is related to a spouce through the employee Number. So you are getting each Spouce/Child combination for each employee.

 

[rhett7660]

Hmmm....

So I should have a unique field for the employee that identifies the unique field in child?

IE

tblMain
SpouseNumber
ChildNumber

tblSpouse
SpouseNumber

tblChild
ChildNumber

Is that how you think I should do it and it would get help with the duplicates that I am seeing?

R~

 

[KeithG]

What are you wanting the query to show?

 

[rhett7660]

Hello..

I am wanting this..

Employee Info
if there is spouse info show it
if there is child info show it....

So I would have something like this:

Employee #1
spouse #1
spouse #2
child #1

or

Employee #1
child #1
child #2
(No spouse info)

or

Employee #1
spouse #1
(No child info)

or

Spouse #2
Employee #1
Spouse #1
Child #1
Child #2
Child #3

or

Child #1
Employee #1
Spouse #1

Etc..

So when I do the search it will tell me all the info tied to the employee or vise versa.. if I happen to have the spouses name.. it will show me everthing associated with that spouse...

R~

 

[rhett7660]

Hi all..

Keith did you have any luck with this.. I have been trying to work this out and I am not getting the results I would like to see.

Any other suggestions?

R~

 

[KeithG]

So it looks to me that your query needs to bring back a different amount of columns per employee depending of the amount of children? So basically one row per employee with child1 child two (if applicable),child 3 (if applicalbe),and so one.

 

[rhett7660]

Keith..

Correct and also depending on the amount of spouse for that employee.....

Hopes this makes sense..

Thanks again for your help...

R~

 

[KeithG]

I am not quite sure how to go about this. You would probably have to create a table and use append queries to append data to the table. Kinda like a Flat file database table you would need fields like spouce1,spouce2,child1,child2,child3. You might want to start up another thread asking for ideas.

 

[rhett7660]

Hmmm...

That kinda goes against the normalization.... There has got to be a way... KeithG I appreciate your help!!! Thanks a bunch...

R~

 

[KeithG]

  #17          Today, 07:03 AM  
rhett7660  
Still Learning....   Join Date: Aug 2005
Location: Good ol USA
Posts: 123 
 
 
Hmmm...

That kinda goes against the normalization.... There has got to be a way... KeithG I appreciate your help!!! Thanks a bunch...

I totally agree, imagine if an employee had 10 kids you would need columns for child1-child10. But I can think of no other way to show the information like you want.

 

[rhett7660]

Keith....

I see what you mean.... Oh boy.....Maybe I just might have to work with what I have.... Did you happen to take a look at the search form? I can setup the report how I want but on the search form is where I would really like to have this work.....

R~

 

[dwjz07]

Rhett...did you ever get your issue resolved? I am having the same problem, where my query is bringing back duplicate rows. I have a table that includes 3 options in one field. If one employee has two or three of these values and a value from a field in another table that I am joinging, the query will come back with duplicates.
Example:
Table 1 has 3 options: apple, orange, peach
Table 2 has 1 option: carrot
If I have 3 selection from Table 1 and then combine with Table 2, I get:
apple
orange
peach
carrot
carrot
carrot
I would appreciate anyone's help on this.
Thanks...