Combining multiple tables with multiple records

[biggcc]

Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -

I have units A, B, C
Parking units p1,p2,p3,p4,p5,p6
Storage units s1,s2,s3.

Unit A uses parking units p1, p2, p5 and storage unit s2.

Unit B uses parking unit p3 and storage unit s1.

Unit C uses parking unit p4, p6 and storage unit s3.

How do I write a query/report that would show:

Unit Parking Storage
Unit A p1, p2, p5 s2
Unit B p3 s1
Unit C p4,p6 s3

Thanks,
Chester Campbell
ccampbell@jfreed.com

 

[pbaldy]

Depends on your table structure, but here's one way, if they're set up appropriately:

http://support.microsoft.com/kb/210163/en-us

 

[Cosmos75]

I assume you have something like this in a table

Product ID, Reference, Description
2211, C1, 10uF 15V
2211, C3, 10uF 15V
1392, C6, 22uF 15V
0100, R3, 10K
0100, R4, 10K
013, R2, 4K7

And you want to end up with a query that returns something like this

Product ID, Reference, Description
2211, "C1,C3", 10uF 15V
1392, "C6", 22uF 15V
0100, "R3,R4", 10K
013, "R2", 4K7

Then this might be helpful.

DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause . (old link).

pbaldy, nice link! I'll have to try that one out someday!

 

[biggcc]

Many Thanks Cosmos75

What you gave me worked great! (aside from a few tweaks) My only question is if there is a way to speed it up. It runs sort of slow and the database will only get bigger. I was thinking that I could have it first create a table and then set it to append that table but when it did the append it went through the whole recordset again just like when it created the table.

Anyway what you gave me was exactly what I was looking for.

Thanks Again,
Chester Campbell