Access Query - Create Crosstab?

How can I create a query to show spend and revenue by vendor? I have the following in the database attached.

1) tbl_spend - spend by supplier
2) tbl_rev - revenue by supplier
3) other vendor tables

I need to have the following layout attached.
example_qry.xlsx
sample_3.241.accdb
kwarden13Asked:
Who is Participating?
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.

PatHartmanCommented:
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.
kwarden13Author Commented:
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?
als315Commented:
Look at sample (qryCross)
sample_3.241.accdb
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

kwarden13Author Commented:
Hi als315- How would I create the same for rev and join the two crosstabs?
PatHartmanCommented:
You never explained rev and spend or why you used two tables rather than one with a type code.
kwarden13Author Commented:
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.
PatHartmanCommented:
But if you want to use them together and sum them, it will be easier to keep them in the same table wit a flag field that identifies which is which.  There is no way to join these two tables since they are not related to each other.

Think of it like this.  You have a table of people and instead of making a table of pets, you make separate tables for cats and dogs.  Although both tables are related to people, they don't have anything that allows you to join them together so if you want to see them in the same report, you either must use a union query or two separate subreports.

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
kwarden13Author Commented:
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.
als315Commented:
You can do it, but in this case you should manually change resulting query every time when year range will be changed. Look at sample (qryRevSpent). It is task for pivot table, but it is deprecated in Access 2016, so you can export or link data to Excel and do it there.
sample_3.241.accdb
PatHartmanCommented:
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.
kwarden13Author Commented:
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.
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.