Counting between two tables

What I am looking to do is be able to have two different tables, one a mailed customer list and the second the sold customer list.

In the first table I have these columns:

State
Car Year
Car Make

Second column has the exact same columns. I have been able to make a crosstab query for just one table to show something like this:

Acura BMW Ford
FL 2009 20000 20000 30000
FL 2008 10000 15000 40000

I would like to take the the sold customer list and count those up the same way on the same table to look something like this:

Acura Sold BMW Sold Ford Sold
FL 2009 20000 100 20000 87 30000 246
FL 2008 10000 150 15000 92 40000 197

Is this possible or am I reaching to far?

Thanks in advance!

I am a little confused by the query results. In the first table, what do the numbers represent? Sales dollars?

In the second table, do the large numbers represent sales and the smaller numbers units? A little bit confuse here. Perhaps an example of the data that is in the tables that gave you the results in your queries would be helpful in understanding what you are specifically trying to do. Your example is not quite clear to me.

Sorry I tried to make a table within the post box but didn't work very well. The larger number are the units mailed out, and the smaller number are the units that actually sold. I have attached an excel sheet with how I was imagining it to show up in access.

The results in the example file attached came from two different lists that would be two different tables within access to pull from. Let me know if I am still unclear.

I would envision this as one table set up as follows:

table
------
RecordID (PK) Autonumber
tState
tYear (Year is a reserved word and may cause issues if used)
tModel
tMailed
tSold


You can then develop a cross tab query to get your results. See attached as an example of how I did it.

Ok you should be able to join the two tables in a query to get all the information in one location. I suggest you join the tables on the Year, State and Make. A simple inner join should work. I did notice that the Dates for the two tables are not formatted the same. You will have to change one of them so that you can join the tables. I would do it in a query. I would then use that query as a substitute for the table it represents joining it to the other table. Once you have the data all in one place, I would then create a new query (cross tab without the wizard) similar to the one I showed in the attached db. It is a bit of legwork, but once completed, it will be easy to update and maintain and give you the specific information you seek.

Alan

An inner join will match the two tables where the fields that you join (by dragging from one table to the other in the Query design). Once you have them joined you will need to set them up similar to what I did in the example for a cross tab Mailed: IIf( etc.....

Look at the two queries here.

No problem. Let's take it a step at a time. You have joined the two tables in a query? Now you want to have a Mailed Field and a Sold Field by car type or model type? Is this correct? In the tables you presented, I wasn't able to discern the fields containing the information you wanted to extract. Which field represented mailed info and which field represented sold info?

I could not open the attachment. Says it was invalid.

I don't think you need to join the list code. If there is a list code in the AB file, does that mean that a mailing went out? And if there is a listcode in the sold table does that mean that a car was sold? If this is true, then we don't want them joined as they will be the criteria for the next step.

I'm still trying to figure out what determines the count of mailings. Does each record in the AB table represent 1 mailing?

Ok, then. Look at the qJoinQuery. Now this runs down and not accross. If you really want it across, then we can do some gyrations.

You will have to change the field names, as I used the earlier db with the other names.

The qfixYear query was because the two tables here had the years formatted differently. One table was text and the other was integer. Needed to get them the same for the join to work. Not sure why that is not showing up. I did it a little different way. Did two aggregate queries. One on mailing and one on sales and then joined those two queries to get the final result.

the results should be the same, but I could not figure out why the mercury was not appearing in the qJoinTables as sold. So I took a different approach. I ran an aggregate query to total the mailings. I then ran an aggregate query to total the sales. I then joined the two in QMailed vs Sold.

In Qmailed vs Sold, I have summarized on the Make not the Model. Accura, BMW, Cadillac, etc.

Do not need to use qJoin Tables. I did not remove it. Use only the QMailed vs Sold.

Your welcome. Glad to have been of help. If you are satisfied, then please mark your thread solved.