multiple Excel rows into one row

[SUSANVV]

Have an Access database. Have 3 tables all linked to an ID. This is a tenant database. Have one tenant with 2 occupants and 2 cars. Have exported to Excel the 3 tables. I now have two rows for the one tenant for the second occupant and the second car. Want to make one row with both occupants and both cars on the same row so that the column headings could be Occ1 and Occ2 and car1 and car2. That is the simplified version of my problem. There would be a Do While for the ID so as to loop through but unsure where to go to put the row information into one row and/or separate columns or output everything to a new table. Thanks for any help.

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SUSANVV]

Thanks for your response. Hadn't really started it yet. Been looking at other examples. Have manipulated ranges with VBA (hiding, unhiding, changing font color,etc) but have not moved cell information from one place to another that was not a direct cell reference. And it must stay with the first ID record to maintain the relationship. I get looping through the rows, then setting the ID, Do While ID's are the same, offset to where the occupant info is. For the next step would I have added the columns in where I wanted the data before starting, and then offset the corresponding info to that blank cell? I think that by explaining this to you I may be on the right track. Using VBA has not been my problem. How to move the info has and I think this just helped. Any more information will be appreciated.

 

[SkipVought]

You want a REPORT in Excel to display one row per tenant. Data > get external data > other > Microsoft query... And drill down to your workbook.

I'd use a query to return the distinct tenants to a new sheet, and then post process the occupants & vehicles in a loop per tenant.

 

[PHV]

Whny boring with VBA when you can do the job in SQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SUSANVV]

I thank both of you for your posts. I am using VBA only because that is what I am familiar with. Sounds like I should get comfortable with SQL. What is the advantage to SQL?

 

[SUSANVV]

What I am seeing is that I create new tables for the occupants and cars in Access using SQL so that the occupants become occ1 and occ2 (etc.) and then export all three tables to Excel like before except all information for each tenant should then be on one row. Please let me know if I am missing something. And I can now see the advantage to SQL. Thanks again to both of you.

 

[PHV]

and then export all three tables to Excel
Export a query instead of the 3 tables.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Actually you can query your Access DB directly from Excel.

 

[SUSANVV]

I thank everyone again. I have just started working on using SQL. Just to reiterate my problem to be sure SQL can accomplish it I will simplify it to two tables (if it works for 2 it will work for any number): I have one table with only one entry for an ID and Tenant name. I have a second table for the occupants that can three occupant records for one id. I need to combine the three records into one for the one ID. right now the field for the occupant names is Occupant. I need to have three fields instead so that the first occupant name is now in field occ1, the second occupant name is now in field occ2, etc. I am asking again because I am not sure if my explanation of the problem is clear. Knowing what you want to do and explaining it can be two different things. Not looking for further instructions or you to build my code. Just confirm that SQL can do that and I will figure it out. Thanks again.

 

[PHV]

Again, what is your actual SQL code exported to Excel ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886