Table fields -to- part of a record

[Maddd0ggg]

Hi,
I think I know my way around a database, but my new employer has me doing kart-wheels. There is an Excel sheet of values which I have imported to Access tables, the table contains ledger values on it and the account numbers are in the fields as column headers.

I want to reflect those field values as part of the actual record in a summary query.

I think this can be done with just a few steps without creating more than one table or query???

Please help.

 

[Maddd0ggg]

So this step can not be done in the 'Normalize' wizard?

If I run an 'Append' query for each account, what am I appending? Is i the account value, or a new table with a new field called account?

Thanks for your quick reply!

 

[Maddd0ggg]

OK... here is some sample data from the original spreadsheet. The account numbers are toward the right.

 

[Maddd0ggg]

We thank you for your support!

 

[The_Doc_Man]

The only other approach I know for this requires you to open the spreadsheet as an application object. After that, you could write VBA code to enumerate the rows & columns of the sheet to extract data in some preferred order/structure using recordsets. If you are going to do this once, do it Pat's way. If this turns into a recurring event, perhaps you need to write a little bit of VBA code for the project.

If you are interested in following this idea, the topics you need to address in the Help Files are:

Access Help: Collections; Application Objects; Methods (for the app object); Recordsets; References;

Excel Help: Programming in VBA; Collections (Excel has them too!); Methods (from the Excel side of the house);

And of course you will need the Excel libraries in your Access reference list.

Search the forum for Excel Application topics and Collection topics.