Access Query - Create Crosstab?

What is the difference between tbl_spend and tbl_rev?

To make a crosstab from multiple tables, I usually create a select query first to select the columns and join the tables.  Then I use the crosstab wizard to build the crosstab.  The wizard limits you to three Row Heading fields, so just pick the first three.  Then open the query in design view and add the other five.  Make sure you set the Crosstab line to "Row Heading" when you add these columns manually.  The query also allows you to create column header values that the crosstab will use for the "Value" field.

The database I have been providing is just an example. My actual db has a spend and revenue we are managing for vendors and clients.

Much more complicated. I am using the example to try and build out my actual processes and data. I can create the crosstab but only for spend and then one for revenue. Do I need to do a union to put the data together?

Look at sample (qryCross)
sample_3.241.accdb

Hi als315- How would I create the same for rev and join the two crosstabs?

You never explained rev and spend or why you used two tables rather than one with a type code.

I thought it would be easier since i get certain attributes from a spend system and certain attributes from our revenue system.

Also spend is updated at a separate time then revenue. Not every vendor has revenue and not every client has spend.

So i have vendors and client names in the same table but separated the revenue and spend. So I wll try to put revenue and spend in the same table.

Hard-coding parameters in queries is poor practice if the parameters can change.  Use a form to run the query and add a control to the form to control the date range.  It is OK to hard-code unchanging stuff.  So for example, if you had a report that you ran that was only ever supposed to include active customers then hard-coding the criteria needed to select "Active" would be fine.  However, if you wanted to use the report for Active, Inactive, or all, then you should make the criteria variable and use a form.  I don't ever rely on prompts since they don't allow for pre-validation which you probably want to do, and you will get prompted at least one more time if you go from print preview to print.

Thanks again, Pat! Good advice on the last bullet and I will keep in mind for the future. For now, I will be combining the clients and vendors into one table as suggested and added a field to identify either.