Help on query too difficult for me

[Mike Gramelspacher]

Hello,

This query problem is a bit too much for me. Maybe someone else could look
at it

Marriages
---------
ApplicantName Text
FemaleIndicator Yes/No
SpouseName Text
ApplicantBirthDate Date/Time
SpouseBirthDate Date/Time
MarriageDate Date/Time
BookNo Number
PageNo Number

Current data (same column order as above):
Brown, John| |Jones, Mary|12-12-1900| |5-5-1922|15|348
Jones, Mary|x|Brown, John|11-11-1899| |5-5-1922|15|348

Result needed:
Brown, John| |Jones, Mary|12-12-1900|11-11-1899|5-5-1922|15|348
Jones, Mary|x|Brown, John|11-11-1899| |5-5-1922|15|348

The person who originally did this made two rows for each marriage. This is
extra work and only a small percentage of the total is finished. I want to
use only one row for each marriage and use a union query to get the second
record for the spouse. The original data did not have FemaleIndicator and
SpouseBirthDate colums. The name fields are no set up right, but I will fix
that later.

(MarriageDate,BookNo,PageNo) is unique for each marriage. Probably
(BookNo,PageNo) is unique.

Anyone have any ideas for solving this problem?

Mike Gramelspacher
Ferdinand, Indiana

 

[Michel Walsh]

Hi,


It is *generally* wise to keep one row per people, so two rows for a
marriage, in the TABLE, and to use a QUERY in times where you need the whole
marriage in just one line. You can use thus use the QUERY, rather than to
modify the table, when you need one row per marriage, "as if" it was a table
(in most cases).


Assuming the actual fields name (one row per people, two rows for a
marriage) are

ApplicantName FemaleIndicator SpouseName ApplicantBirthDate
SpouseBirthDate MarriageDate BookNo PageNo


what is missing is the SpouceBirthDate, but we can get it with a query like

-----------------------------------
SELECT a.ApplicantName,
a.FemaleIndicator,
a.SpouceName,
a.ApplicantBirthDate,
b.ApplicantBirthDate As SpouceBirthDate,
a.MarriageDate,
a.BookNo,
a.PageNo

FROM myTable As a INNER JOIN myTable As b
ON a.MarriageDate=b.MarriageDate AND
a.BookNo=b.BookNo AND
a.PageNo=b.PageNo AND
a.SpouceName = b.ApplicantName

WHERE b.FemaleIndicator

 

[Mike Gramelspacher]

Thanks. This query does, in fact, get the columns I need, but now I need to
save the query results as a table. If I cannot get the one table per
marriage record, it will be double work for the person to enter the
thousands of remaining records as two rows. This is why I want to do this.

Mike Gramelspacher
Ferdinand, Indiana

 

[Michel Walsh]

Hi,


From the query designer, menu or toolbar, change the query type from SELECT
to MAKE TABLE.



Hoping it may help,
Vanderghast, Access MVP

 

[Mike Gramelspacher]

Michel,

The make table query worked. I had tried that earlier, but had an error
message. This time it worked.

Thanks for taking the time to help me. I had been approaching this as an
update query, but that always proved to be non-updatable.

Mike Gramelspacher
Ferdinand, Indiana