Combine related records into flat file

[Bonnie]

Hi,

I have 2 tables with multiple related records. To simplfy:

Table1

RelatedID
Fname
Lname


Table2

RelatedID
PartyLocation
Date

I need to combine them into a single flat file. So it would look like this:

RelatedID
Lname
Fname
PartyLocation1
Date1
PartyLocation2
Date2
PartyLocation3
Date3

Any help with the SQL would be appreciated.

Thanks in advance,

Bonnie

 

[Douglas J. Steele]

And what happens if there are 4 parties related to the same ID?

 

[Bonnie]

Hi Doug,

I'll look at the records and see what my max has to be and adjust the SQL
accordingly. Do you have any ideas for me? It isn't my choice, I have to
supply the customer in their desired format. I designed it related.

 

[CraigH]

Well I won't go into how much is really related - if it was you need another
table at least.

I am not up on my SQL for very advanced things -but I don't think a Crosstab
would work in your situation - could be wrong.

My next idea is to use code to output the file. You then don't have to
worry about increasing number of locations. But that will be involved.

The other way is to make a query tht is limited for each location (The
fields should also be aliased for proper Column heading [PartyLocation1:
Partylocation]) and then put each of those queries into another query with
the person table and link them. You must select each link and set the Join
Properties so that it is "All data from the People table and ..." or you
won't get everything if the person does not have all the locations.

Not an elegant solution but will work for a limited number of locations.

 

[Douglas J. Steele]

It's going to be almost impossible to achieve that result using SQL,
especially if not all RelatedID values have the same number of parties.

If there are always 3 parties, the following should work:

SELECT Table1.RelatedID,
Table1.Fname, Table1.Lname,
Party1.PartyLocation AS PartyLocation1,
Party1.PartyDate AS Date1,
Party2.PartyLocation AS PartyLocation2,
Party2.PartyDate AS Date2,
Party3.PartyLocation AS PartyLocation3,
Party3.PartyDate AS Date3
FROM ((Table1 LEFT JOIN Table2 AS Party1
ON Table1.RelatedID = Party1.RelatedID)
LEFT JOIN Table2 AS Party2
ON Table1.RelatedID = Party2.RelatedID)
LEFT JOIN Table2 AS Party3
ON Table1.RelatedID = Party3.RelatedID
WHERE (Party1.PartyDate <= Party2.PartyDate
AND Party1.PartyDate <= Party3.PartyDate)
AND (Party1.PartyLocation <= Party2.PartyLocation
AND Party1.PartyLocation <= Party3.PartyLocation)
AND Party2.PartyDate <= Party3.PartyDate
AND Party2.PartyLocation <= Party3.PartyLocation

Otherwise, you're going to have to use VBA and use a number of different
recordsets in what's referred to as balance line processing.

Note that I renamed the one field from Date. Date is a reserved word and you
should never use reserved words for your own purposes. For a comprehensive
list of names to avoid (as well as a link to a free utility to check your
application for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Bonnie]

Thanks for replying Craig. Yes that what I was thinking but asking for some
help with the SQL.

Thanks,

Bonnie

Well I won't go into how much is really related - if it was you need another
table at least.

I am not up on my SQL for very advanced things -but I don't think a Crosstab
would work in your situation - could be wrong.

My next idea is to use code to output the file. You then don't have to
worry about increasing number of locations. But that will be involved.

The other way is to make a query tht is limited for each location (The
fields should also be aliased for proper Column heading [PartyLocation1:
Partylocation]) and then put each of those queries into another query with
the person table and link them. You must select each link and set the Join
Properties so that it is "All data from the People table and ..." or you
won't get everything if the person does not have all the locations.

Not an elegant solution but will work for a limited number of locations.

Hi Doug,

I'll look at the records and see what my max has to be and adjust the SQL
accordingly. Do you have any ideas for me? It isn't my choice, I have to
supply the customer in their desired format. I designed it related.

 

[Bonnie]

Thank you Douglas. I know that about reserved words - these actually aren't
my fields names just an example.

I figured out a way to do it in excel in the meantime but I'm going to play
with this SQL. I thought what you are saying about it but thought maybe I
overlooked a way to approach it.

Thanks again for your time.

Bonnie