Another SQL QUERY PROBLEM[RESOLVED BY RANDEM]

[mxnmx]

Hey,
I have a table1 with such fields:
Date of Birth
ID Number
Name

Currently the name field is empty. I have another table2 which has these fields:
ID NUMBER
Name
Date of Birth

How can I write such a query for table1 that it takes all the values from Table2 where ID number & Date of Birth match in both the tables?

[randem]

mxnmx,

You seem to have a few SQL problems. A tool that you can use to help you with these queries and can help you learn SQL is MS Access.

If you use the query design with your tables and get the answer you need you can just view the SQL that created the answer. In this way you can get a fel for the syntax and the way things happen.

Now the answer is to use joins on the fields that are the same.

[mxnmx]

OK this problem is quite complex I even dont know how to write such a query. So I would really appreciate if anyone could help. I have two tables:
Groups- which has these fields:

GroupName
GroupID
Sibling1Name
Sibling2Name
Sibling3Name
Sibling1DOB-DATE OF BIRTH
Sibling2DOB
Sibling3DOB
And I have another table called Records, which has all these fields:
GroupID
DOB
Record
Name

This table has some 20,000 records and all the fields have data apart from one- Name Field. So now what I wish to do is that if the groupid field of this table (records) matches the groupid of the other table and the date of birth matches with any siblings DOB and if it does then get the name and paste it into this table. Please help me with this, I have no clue!!!

[randem]

mxnmx,

This is an Update query and a Select query. You need to join the tables on the GroupId field to Select all the DOB's that match then using the Update query update the DOB's using the GroupID.

[mxnmx]

Thanks randem,
An example would be realllllly helpful...I'm going to search the forums as well but if you have got one pls post it. Thanks!!

[randem]

mxnmx,

Post a database with some data.

[mxnmx]

I have attached a DB with 2 tables Groups, VaccinationRecords. The Table Groups has the general info, and in the table of VaccinationRecords the field name is empty thats where I wish to insert the value. The Group records are actually family records where Sibling1 is the first child and SIBLING1SEX is the gender for that child and SIBLING1DOB is date of birth for the first child. Now The DOB field and the GROUP ID field has to match and then whichever child's DOB it matches with in the Table Group it shd paste the NAME field of VaccinationRecords

[mxnmx]

Sorry forgot to attach in the last one, here it is

[randem]

mxnmx,

Are you designing these tables or are they part of some inherited project. I ask because the tables in the database you posted are not efficient and should be a different structure.

If you are designing the database you should change these structures, If an inherited database you should think about redesigning the relationships between the tables and create a new table for the Sibling info.

[mxnmx]

Yes randem
To tell you frankly, I really did not know how to structure it. So I just went ahead and made what I understood. So how do you suggest they be?

[randem]

mxnmx,

I restructured your database tables to reflect the correct way to structure repeating information. all your tables have been altered. There is a query 'qryGet Vacination Info' that I made to show you how to retrieve the info.

You will need to learn how to normalize data or you will create a lot of un-neccessary headaches when you wnat to store and retrieve data.


If you have any question.... You know where to find me.

Good Luck.

[mxnmx]

Thanks randem, yes I do have one question...What do the following queries do:

SELECT [Sibling Org].[GroupID] AS Expr1, [Sibling Org].[Name] AS Expr2, [Sibling Org].[Sex] AS Expr3, [Sibling Org].[DOB] AS Expr4 INTO Sibling
FROM [Sibling Org]
GROUP BY [Sibling Org].[GroupID], [Sibling Org].[Name], [Sibling Org].[Sex], [Sibling Org].[DOB];

********************************
INSERT INTO Sibling ( GroupID, Name, Sex, DOB )
SELECT Groups.GroupID, Groups.Sibling7 AS Expr1, Groups.Sibling7Sex AS Expr2, CVDate([Sibling7DOB]) AS Expr1
FROM Groups
WHERE ((([Groups].[Sibling7]) Is Not Null));

THANKS FOR THAT!!!!

[randem]

mxnmx,

Those were queries left over from me moving your data out of you data structures and into mine. I left them in case you needed to do the same for more data than you had in the database you posted.

They are just there for reference so that you might see how I moved your data, that's all.

[mxnmx]

Thanks randem for all your help. Yes now the structure looks much better and efficient.

[mxnmx]

I need to move more data from the Groups Table to Sibling. But when I run those queries that you have provided it says: DUPLICATE OUT PUT ALIAS. How shd I go about re-structuring. Currently my DB is 20MB. Thanks!!!

[randem]

mxnmx,

Forget the query name 'Query2'. Concentrate on the query named Query1. If you open it in design mode, you will find that all the fields reference Sibling7 (except the GroupId field). All you need to do is change the refrence for each sibling number then run the query. It will add all the siblings for that reference.


Ex.

Start with empty Sibling table.

Change query references from Sibling7 to Sibling1
Run Query

Change query references from Sibling1 to Sibling2
Run Query

Change query references from Sibling2 to Sibling3
Run Query


Keep up the process until you are done with all the siblings that you reference in your Groups Table.

Don't worry about duplicates, You might have a few. If not everything is still OK.

[mxnmx]

Thanks randem, my problem has been fully resolved. All thanks to you.