Formatting and importing Excel spreadsheet into table (1 Viewer)

[JemCain]

I am trying to set-up a booking system for a kids' holiday during the summer. At present, I collect data in JotForm, from where I can export it into Excel, but not Access. So I have an Excel spreadsheet that looks like this:

and would like to import it into an Access table that looks like this:

Please would someone tell me how to do it? Or is there a better way?

Thanks very much.

 

[arnelgp]

i think you will need custom VBA to export that.
or use Union Query.

 

[Pat Hartman]

You would link to the spreadsheet. Then you would create a union query that includes 14 select queries (assuming you have 14 activities).

Select ChildNumber, 1 As ActivityNumber From YourLinkedSpreadsheet
Where [Activity 1] = "Yes"
Union Select ChildNumber, 2 As ActivityNumber From YourLinkedSpreadsheet
Where [Activity 2] = "Yes"
Union Select ChildNumber, 3 As ActivityNumber From YourLinkedSpreadsheet
Where [Activity 3] = "Yes"
...

Then Create an Append query that uses this saved Union query as its source

 

[Uncle Gizmo]

I'm puzzled because your conversion appears to to have lost the yes/no values?

Have a look at my my transpose tool:-

Normalization Tool - Nifty Access

Normalization Tool Introducing the "Transpose Tool" or "Database Normalization Tool," your go-to solution for seamlessly converting spreadsheet-structured data into MS Access-structured data. This transformation not only equips you to construct exceptionally powerful MS Access databases, but is...

www.niftyaccess.com

It may well convert to data for you not sure because of your missing yes/no Fields. ..

 

[JemCain]

You would link to the spreadsheet. Then you would create a union query that includes 14 select queries (assuming you have 14 activities).

Select ChildNumber, 1 As ActivityNumber From YourLinkedSpreadsheet
Where [Activity 1] = "Yes"
Union Select ChildNumber, 2 As ActivityNumber From YourLinkedSpreadsheet
Where [Activity 2] = "Yes"
Union Select ChildNumber, 3 As ActivityNumber From YourLinkedSpreadsheet
Where [Activity 3] = "Yes"
...

Then Create an Append query that uses this saved Union query as its source

This works like a dream! Thank you so much.

 

[Pat Hartman]

You're welcome

 

[CJ_London]

I'm puzzled because your conversion appears to to have lost the yes/no values?

OP only wants the yes values - correct normalisation

although I did notice child 91 has imported activity 3 when the spreadsheet says no - assumed this was just an example