Exporting Query to Excel

[chugo]

I hope this is in the right forum.

I have built a app for a client (big hotel chain). This app is a type of project management app that helps them manage the start-up process for hotels. There are 6 major startup phases and each phase has a number of milestones and each milestone has a number of tasks. we track the completion status of each task and that automatically determines the completion status of the milestones and phases. There are about 200 hotels at any given time going through this process. We have a weekly report which shows the Phase and Milestone status for every hotel undergoing start-up. The report uses a query that allows it to sort first by hotel then by phase then by milestone. The output of the query contains multiple entries per hotel. They now want to export these results to custom made mail engine that sends a status report to each hotel (can't talk them out of this process). The problem is that the mail engine needs the information in an Excel spreadsheet and all the information for a hotel on a single line.

For example (small subsection of output):

Instead of this:

Inn Code Milestone Milestone Status
ABQNJ 1 Complete
ABQNJ 2 Complete
ABQNJ 3 Complete
ABQNJ 4 Complete
ABQNJ 5 In Progress


They want this:

Inn Code Milestone1 Milestone1Status Milestone2 Milestone2Status ...
ABQNJ 1 Complete 2 Complete


It seems like a screwy request but there has to be a way to do this. I just can't figure it out.

Any thoughts? :banghead:

 

[billmeye]

You can concatenate the fields using a function and in the query add the function as a column with whatever else is included in the data.

Function Concatenate(GetKey) As String
Dim Rst As DAO.Recordset, MySql As String
Concatenate = ""
MySql = "SELECT * FROM " & tblMilestones & " WHERE ((( tblMilestones.PKID)=" & GetKey & "));"
Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
Rst.MoveLast
Rst.MoveFirst
     Concatenate = Rst!Inncode & " " & Rst!Milestone1  & " " & Rst!Milestone1Status & " " & Rst!Milestone2 & " " & Rst!Milestone2Status &                              " " & Rst!Milestone3  & " " & Rst!Milestone3Status & " " & Rst!Milestone4 & " " & Rst!Milestone4Status & " " & Rst!Milestone5  & " "  & Rst!Milestone5Status & " " & Rst!Milestone6 & " " & Rst!Milestone6Status
Rst.Close
Set Rst = Nothing
End Function

 

[chugo]

Thanks for the reply. I actually solved this by creating a temporary table that violates all rules of data normalization, populating it with a function that cycles thru all the tables I need and then exports it to Excel. It works, but I think I like your solution better. It seems cleaner. I will play around with it and report back.

Thanks again!