Multiple Table Query

[BigNate]

Hello,

I want to create a query will result in two columns. The first column will be a count of the field called "Claim_Type" where Table A and Table B are have a common field Claim_Type. The second column for the result will be the exact same thing, only instead of using table A and Table B, we will be using Table A and Table C.

Can someone please tell me how I would go about doing this? Tables B and C are unrelated, so they should not impact each other, which is my current issue.

Thanks!

 

[Fazza]

hi, Nate. The original description isn't clear - please clarify the requirement. Thanks

 

[BigNate]

Hi Fazza,

I track customer claims every day. I have a database with a few years of data but only want to analyze the current month and . I want to make a table with three columns:

First column: Type of claim (example shipping, packing, counting, and so on...)
Second Column: count Claims for the current month (month to date)
Third Column: count of Claims for the current year (year to date)

So As an example, it would look something like this

Claim Type	Month Count	Year Count
Shipping	3	14
Packing	1	8
Counting	2	7

<tbody>
</tbody>

**I know how to find column 2 and 3 individually, but my issue here is I cannot present this info on the same table

 

[Joe4]

I know how to find column 2 and 3 individually, but my issue here is I cannot present this info on the same table

How are you doing that?

You can do this all in one query if you created two calculated fields, one for Month count and one for Year count.
Make this calculated IIF fields that return a 1 if it is in the current Month or Year, and a zero otherwise.
Then you can do an Aggregate Query, grouping on the Claim Type and summing up the values from your Month and Year count calculated fields.

 

[Joe4]

Cross-posted here: Year Count and Month Count Same Query

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

 

[BigNate]

Joe,

Looks like my response didn't go through. I apologize about the cross posting and thank you for your help. I should have not posted on two different sites without referencing one another. I'll pay better attention to the rule next time. Thank you!

 

[Joe4]

No worries.

So, were you able to work with the suggestion I gave you up in post #4, or do you need assistance with any part of that?

 

[BigNate]

All good now. Thank you.

It's an interesting method but now that I know, it seems obvious.

 

[Joe4]

It's an interesting method but now that I know, it seems obvious.

Yes, there are other ways too. Like make two queries (one for Month and one for Year), and then create a third query based on these two queries. Conceptually, it might be a little easier to understand, but that seems like more work to me...