Denormalise in Query

[Kiwiman]

Afternoon

I am trying to denormalise a child table in a query for a specific task so that all child lines show on one line but the fields are not concatenated.

Table1 - Parent
PID Desc
1 Danny
2 Jonny
3 Andy

Table2 - Child
ID CID PID Destination
1 1 1 London
2 2 1 Paris
3 1 2 New York
4 1 3 Albany
5 2 3 Malaga
6 3 3 Texas

Query Output (7 separate fields)
PID Desc CID Destination CID2 Destination2 CID3 Destination3
1 Danny 1 London 2 Paris
2 Jonny 1 New York
3 Andy 1 Albany 2 Malaga 3 Texas

I can do this with subqueries linked together, but was wondering if there was a more dynamic option to do this via vba.

Thanks as always.

 

[Minty]

Try using a cross tab query. You can predefine the columns if you need to.

 

[CJ_London]

bit confused by the data - Parent 3 has three different names, child 1 is against London, New York and Albany - so why do you need the CID column when you are only using the PID column?

if you are not worried about being able to see the ID's I would have thought a crosstab would do this. If you do want to see the ID's to two crosstabs and link them together in a 3rd query

 

[plog]

was wondering if there was a more dynamic option to do this via vba.

Yes, but dynamic doesn't mean efficient or easier. I agree with Minty and CJ, the best solution would be a cross-tab once you've addressed CJ's issues.

However, yes, I am certain you could do this with VBA. It would be a pain and take a while, but you could dynamically construct an SQL statement to generate the output you wanted. Without giving it too much thought, my initial solution would involve running a DCount to find out how many fields you will end up with, creating a generic SQL statement, then loop through every record in Parent customizing that generic SQL statement for that record and then UNIONing all those SQL statements together.

With all of that said. What is your specific task--the ultimate goal? Perhaps we can help you reach that goal without this intermediate step at all.

 

[Cronk]

CJ: The CID field appears to be the trip number for the particular child. Eg Malaga is the second trip for the third child.

So the options are looping through a cross tab query or a loop on the first table with an inner loop on the second table.

 

[Kiwiman]

bit confused by the data - Parent 3 has three different names, child 1 is against London, New York and Albany - so why do you need the CID column when you are only using the PID column?

if you are not worried about being able to see the ID's I would have thought a crosstab would do this. If you do want to see the ID's to two crosstabs and link them together in a 3rd query

Thanks. Parent 3 only has 1 name (Andy) but 3 destinations (Albany, Malaga and Texas) in the child table.

The examples I have given is rather simplistic as each route (the child record) will have at least 30 different fields, not just the ones shown (ID (autonumber - not required for output), CID, Destination).

So using parent 3 as an example, the final output will show 3 x 30 records of the child record across the page. I can do this with queries, just looking for alternatives.


Sent from my iPad using Tapatalk

 

[Kiwiman]

Yes, but dynamic doesn't mean efficient or easier. I agree with Minty and CJ, the best solution would be a cross-tab once you've addressed CJ's issues.

However, yes, I am certain you could do this with VBA. It would be a pain and take a while, but you could dynamically construct an SQL statement to generate the output you wanted. Without giving it too much thought, my initial solution would involve running a DCount to find out how many fields you will end up with, creating a generic SQL statement, then loop through every record in Parent customizing that generic SQL statement for that record and then UNIONing all those SQL statements together.

With all of that said. What is your specific task--the ultimate goal? Perhaps we can help you reach that goal without this intermediate step at all.

Thanks for your reply

I will look into your suggestions

The ultimate goal is to produce a trade route file (1 line per parent record) showing all child records across the page - each child record will have at least 30 fields. So the final output per parent record will be the parent fields + (n x 30 child fields) where n is the number of routes relating to the parent. This will show across the page, not down.

Again I can do his with queries, so no biggie


Sent from my iPad using Tapatalk

 

[plog]

The ultimate goal is to produce a trade route file

You might try a pivot table in Excel then: https://support.office.com/en-us/ar...0a9-a8e9-f99134456576?ui=en-US&rs=en-US&ad=US

A Cross-tab query allows you to make the column headings from only 1 field. A pivot table allows multiple fields. Also, it might be eaiser to get your data into the proper final format from Excel.