Crosstab? Maybe? Listing items of a package all on the same record.

[snipe]

Hey guys,


I have 2 tables in a ODBC that I am using to create a list of all of the packages in our system. As you can see in the snip, there is a package SKU, then the component SKUs, and their respective quantity and prices. We are switching software systems, and we have to populate a bunch of excel documents to push our current stuff (packages and components in this case) into the new system. The excel file has a different layout. instead of a different record for each package component, they use 1 record for each package sku, and then the package components and respective info goes off in the fields to the right. The entry in the excel snippet below matches up with the highlighted records in the query snippet above.

We have around 3000 packages, so I need to find a way to do this through Access.

Notes:
1. The excel file has room for up to 12 components


2. Our packages have between 2 and 12 components

[June7]

Denormalizing data can be challenging. I don't think a normal crosstab will serve. Review https://www.accessforums.net/program...ias-21972.html

And then there is Allen Browne's ConcatRelated function.

[snipe]

Thanks, June7. Good amount of reading to do in that link you posted. I'll go over this tonight.

Much of what I have found online so far is about concatenating the multiple records into 1 field. I need to break them out into separate fields; no concatenation.

[CJ_London]

you could do it with 5 small queries, one SELECT DISTINCT for item, three crosstabs, one each for component, quantity and price, then combine these in a fifth select query, linking on item with left joins from the first table to each of the three crosstabs, and drag the columns down in the order you want them - you can even rename the headings to match the spreadsheet requirements

For the crosstabs, you would group/row header for item, left(component,4) for column and first component/qty/price for value. You would need to set column headings for each of the components (presuming this is the value to the left of the -)

Don't you just love it when databases communicate, they do so in a denormalised way

[June7]

Ah yes, multiple crosstabs. Be aware, still limited to 255 fields in the final query. How many components could be possible with each kit?

[CJ_London]

agreed but the OP has said excel sheet has room for 12 components, so 36 columns plus a few row headers

[snipe]

Ajax, I am about to start giving this a go. Let's see if I can figure it out.

[snipe]

Ok so I am slightly stuck. I did the cross tab query for just 1 of the packages (the one highlighted in the first pic in my original post) and got the following result:
Small edit: I did 'last' on the value instead of 'first' for this snippet. 'First' yield the same result except with D553-02 in that field.

Here is the design of the query:

That second field is just an expression to determine the string before the "-" since sometimes its 4 characters, sometimes 5, and sometimes there isn't a "-". This string is referred to us as the "Series." (i.e. D553 is the series of the example above)

Ajax, you said "You would need to set column headings for each of the components (presuming this is the value to the left of the -)" and you kind of lost me at that point; so I am assuming that is what I am missing. I know that you have to open up properties and "column headings" is there, but I am lost on what you want me to put in there. I tried using "1";"2";"3" up to nine. but the components didn't populate those fields.

[snipe]

Woo! Got it working! Built a query on the base package/component that added a line item number to each component. That new field is now used as the column headers.

I think I got it from here. Thanks guys!