Export a Query to Excel in VBA

[RayMilhon]

I have a query written in Access 2010 That pulls the data I need.

The format of the data that comes out is

encounter
Name
dob
AdmitDate
DischargeDate
DiagDesc
Attending Provider
Hospital
Seq
Notes

The purpose is to generate a list of all Members that went to our Urgent Care and within 24 hours were admitted to the Hospital. The Query works fine the issue is that the last 2 fields can have multiple records For example,
If I omit the last 2 fields the query generated 8 Rows. Including the last 2 fields the query generated 31 Rows. The first 8 rows were repeated depending on how many Notes were entered.

I need to format the export so that the first 8 fields appear Once for each Patient and then below that the Notes Appear in order

Something like
Encounter Name dob admitdate dischargedate diagdesc attending Provider Hospital
1 this is note1
2 this is note2

Encounter Name dob



I thought the best way was to use VBA Open the recordset and loop through outputting the first 8 fields in a row and then looping through and outputting each note
when the mbr and admit date change then again output the first 8 fields and continue for the recordset.

My problem is that it's been far too long since I had to output a query in this manner and I don't remember how to export to EXCEL without using the transferspreadsheet command and that one won't work for me.

I've included the EXCEL Reference in VBA for the 14.0 Object Library. But not sure where to go from here anybody can point me to where I can research this. Thank you in advance.

[ranman256]

Transferspreadsheet works every time.

[RayMilhon]

Not if you have to restructure the data.

[ranman256]

the query does this.

[RayMilhon]

How The Query Results look like this

08/02/2018 6:00 AM John Doe 12/23/1942 07/31/2018 8/01/2018 123.11 Primary Diagnosis My Hospital 1 This is note1
08/02/2018 6:00 AM John Doe 12/23/1942 07/31/2018 8/01/2018 123.11 Primary Diagnosis My Hospital 2 This is note2
08/02/2018 6:00 AM John Doe 12/23/1942 07/31/2018 8/01/2018 123.11 Primary Diagnosis My Hospital 3 This is note 3

It Should look like this
08/02/2018 6:00 AM John Doe 12/23/1942 07/31/2018 8/01/2018 123.11 Primary Diagnosis My Hospital
1 This is note1
2 This is note2
3 This is note3

There is no way to get the results in this format in a query that I know of.

[ranman256]

I would export using Transferspreadsheet,
then open the XL file,scan the rows,
then delete the 7 cells after the 'main' record. Shifting the last cell to col.1.
until the end.

[RayMilhon]

What I posted was a sample of the data the query returns 32,000 rows.

[ssanfu]

Since you have a specific format in which the records need to be exported, transferspreadsheet won't work. Normally, this would be done by grouping in a report.

I like the idea of using recordsets and Excel automation using VBA, but it depends on the table designs/structures.
Are the fields "Seq" & "Notes" in a separate table?


EDIT:

The idea being with tables

tblMembers -----> tblVisits ----> tblNotes

you could then do something like


(pseudo code)

 sub ExportCustomRecords

Open a record set named rsMembers that has fields "PKField", "Encounter", "Name", "dob", "admitdate", "dischargedate", "diagdesc", "attending Provider", "Hospital"

rsMembers.MoveLast
(check if records)
rsMembers.MoveFirst

Do until rsMembers.EOF

  write the 8 fields to Excel

  Next open a record set named rsNotes with fields "FKField", "Seq", "Notes" Where rsNotes.FKField = rsMembers.PKField
  (check for records)
  Now loop through the recordset, writing the notes

  Close rsNotes 

  rsMembers.MoveNext

Loop


clean up stuff


Exit sub