Dynamic query based on form selections in MS Access

I ran into a bump in the road and I'm looking for some advise.

I need to populate a form with data in a cross tab format.  I have the header which holds fields universal to all the records, and the detail will be the individual records.

 My source table has three fields
Series, Item, Attribute, Data

Each item has multiple attributes and data records so one item will have as many records are there are different attributes.

I also have a table that holds two fields, Series and Attributes.

I'm thinking I can build a form that when a series is selected, all the matching attributes populate.  I'd than like to check the attributes I want included in my query, and fire off the query.  So, if I select the series Magna1, I should see attributes a1, a2, a3.  Once I select a1 and a2 as my attributes, I than want the query to generate and display all the items in the Magna1 series with their a1 attribute, a1 attribute data, a2 attribute and a2 attribute data.

Finally, I need to populate a table or output to excel these pieces of data.

To be honest, as I write this, I can't even begin to phantom how I might do this...it's making my head hurt.  Any suggestions or examples are appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Forms do not easily lend themselves to display cross-tab data.

Forms are designed for data entry, ...and as you know, you cannot edit a cross tab query's results.
Also you also may know, ...cross-tab queries generate their columns "on the fly", where as forms have their columns "hard coded"

You can certainly embed a crosstab query in a "Report", ...if that is what you might need.

Not sure I understand the rest of your explanation there, ...or the reason why this might be needed...
MCaliebeAuthor Commented:
True...So here is the skinny.

We area working with an internet catalog company who wants to put our data up on their website.  I have to populate their template with x amount of data.  Some of this data is the same for all items, such as series, specifications, description, material.

However the items will have data unique to themselves i.e. item number, dimensional sizes, pressure, threads, ect.  These are all the "Attributes".  I can only supply a select number of attributes, so although I have 10 attributes for a series, I will only be able to provide them with 5, and I have to match these 5 to specific buckets in their template so basically I need to create a "Map" in the header, to show the correct attributes in the correct place in the cross tab.  

I won't be using the crosstab to change data, just to ouput it to a table matching their template.

My goal is to populate a table, or output to a spreadsheet all the header data and the unique data from the items so I'd have a basic catalog...

Series, description, spec, item, Attribute A1, Attribute A2, Attribute A3...
Magna1, Pump, ISO123, A55461, 6", 200LBS, 10HP...
Magna1, Pump, ISO123, A55498, 7", 225LBS, 15HP...
Magna1. Pump, ISO123, A56542, 10", 350LBS, 25HP...

This make any sense?
Hamed NasrRetired IT ProfessionalCommented:
Check if the idea is as follows:

Start with the original tables and their contents.
From based on original tables.
Select relevant controls.
Create cross tab query.
Display required form based on this dynamic query.
Take the output records to a separate table.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If all else fails, then use the Temporary Table method:

Create a table that looks like what you need to export, and then use VBA/SQL to fill that table with data, then export that table. More work, but you can tightly control the results, and you'll avoid some of the limitations of CrossTab queries.
I have a sample that does exactly what you want.  It looks like a crosstab but it is a bound form and the underlying data tables are correctly normalized.  I call it a "bound denormalized form".  The example "pivots" expense types.  To adapt this example for your use, you will probably have to create several forms with fixed columns but you might be able to get past that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MCaliebeAuthor Commented:
Thanks for everyone's suggestions. I started down the road of temp tables, splitting up the common values and the unique values which would need to be in the cross tab format.

Thanks for the sample Pat!  I'm eager to look at it and see if I can apply it.

I'll report back and let everyone know what works!
Good luck.  The really great thing is that NO CODE is required to implement this pseudo crosstab.  It is all done with queries.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.