Crosstab or something else

[ryetee]

I'll keep this simple to begin with!!

I have various tables but basically a list of items sold against a date. I want by month how many items were sold per month. here is one order per month To get this I've used the query wizard. I did a bit of research and it seemed I needed a crosstab. THis works but now some complications.
I actually want the number sold and total price as well. The query wizard only takes one column value and in design mode it's the same.

I now have 2 crosstab queries, one for value and one for total prie. How can I merge them together.

Another complication is that each order there will be 1 or more shipments. The user wants to put this in the column heading together with dates sent. I hold this data and link it to the order itself. For example if there were 2 shipments in July comprising of 4 and 6 boxes respectively the heading would be
July 9 +9a post 22/26.06.2018 4+6 x Box

The latter I'm not 2 bothered about but I could with working out how to get more than 1 value under the column heading.

 

[theDBguy]

Hi. It might help to have a sample db with test data and a mock up of what you want as a result. Otherwise, it's a little hard to tell you how to proceed. One thing for sure, having a dynamic heading might be a real challenge. But, I am sure someone here can figure it out.

 

[June7]

Cannot have two sets of data under 1 column heading. There must be 2 column headings. You can do 2 CROSSTAB queries then join them. Also, review http://allenbrowne.com/ser-67.html#MultipleValues

The column heading you describe might involve concatenating fields and literal text.

If you want to provide db, follow instructions at bottom of my post.

 

[isladogs]

Crosstab queries using the wizard can have up to 3 fields as row headings, 1 field for column heading and 1 field for value
You can add additional row headings yourself in the query designer BUT its not possible to add more than 1 column heading field

However don't be misled by that. Say you choose a date field (in month year format) you will get every possible month & year in your data (unless of course you filter it) e.g. Jan 2018, Feb 2018 etc

If you need both number sold & total price, you could concatenate the values e.g. 130 / £275.00 ; 25 / £43.50

Dynamic column headers are easy enough when you know how to do them.
I have various examples you can look at when you are ready

 

[ryetee]

thanks to all replies. i'm holiday at the moment and will take a look soon!!

 

[ryetee]

Crosstab queries using the wizard can have up to 3 fields as row headings, 1 field for column heading and 1 field for value
You can add additional row headings yourself in the query designer BUT its not possible to add more than 1 column heading field

However don't be misled by that. Say you choose a date field (in month year format) you will get every possible month & year in your data (unless of course you filter it) e.g. Jan 2018, Feb 2018 etc

If you need both number sold & total price, you could concatenate the values e.g. 130 / £275.00 ; 25 / £43.50

Dynamic column headers are easy enough when you know how to do them.
I have various examples you can look at when you are ready

I couldn't resist in trying this. I can concatenate my numbers in the query but can't separate them
So if i put Expr1: [Units] & [totalwithtax] i get the righty figures but if I put Expr1: [Units] & " " & [totalwithtax] i get data mismatch

 

[isladogs]

The concatenated field will be a string.
To extract the data once again, use e.g. left, mid, right and, where appropriate, reformat as the correct datatype using CInt or CCur etc

I've used this idea in a few very complex crosstab reports to display one set of data in each 'cell' but apply conditional formatting based on the other field which isn't displayed
For example a student exam grades crosstab showing the grades for each subject but with CF applied which indicated the residual (difference between actual grade and target grade) based on a traffic light colour code

EDIT
I suggest you separate the two fields with a colon or a slash. Makes it easier if you need to separate them again afterwards