How to get a consolidated table?

[brickbuilder]

Primary key	ID	Category	Type	Date	Cost
563	105	par	M	7/15/2014	$20.00
858	105	par	M	1/24/2015	$-00
1604	105	par	M	8/24/2012	$34.00
4175	105	par	M	11/14/2013	$67.00
5203	105	other	LS	5/24/2012	$36,43.00
5939	105	other	O	7/19/2014	$33,45.00
9298	105	bip	LS	6/2/2013	$18,36.00
9410	105	bip	LS	12/31/2014	$13,98.00

<tbody>
</tbody>

Hi, I'm very new to access. I have data from 2012 to 2015 that looks like the above for 1 ID. I need to get the following:

A-freq of all par >0 in 2012 that had no LS in 2012
B-freq of all LS in 2013 to 2015 that had par >0 in 2012

The category and type mixture are really messing me up.
I can get all the par from 2012 that had no LS in that year.
I can get all the LS in 2013-2015 that had par in 2012.
But then I'm stuck in what to do next.

I'm wondering about a few things but I'll start here:

1-When I do A and B I notice that there's some ID from query A that are not in query B. It's expected but how do I get a single table that shows them together? Some freq LS 2013-2015 will be zero but all par will be greater than 0 for a given ID. What I mean is below.

ID	freq par 2012	freq LS 2013-2015
105	1	2

<tbody>
</tbody>

Thanks for your help!

 

[Micron]

Difficult to follow, but it looks like a Union query would work. I say this because it seems like you're saying you've solved the individual pieces but need to bring them together. If that's true, then build a Union query to bring the parts together, then use that query as the basis for a make table or append query.

 

[brickbuilder]

Yes I do have the individual tables I need. I read that a Union can only be done via SQLview? Will the Union only work in this case where the ID's are unique? What happens if they're not?

Thanks for your help and sorry for not being clearer in the OP!

 

[Micron]

If you have two or more queries that work, grab the sql statement for the first, open a new query, close the 'add table' dialog, go to sql view and paste in the first sql statement. Remove the ending semicolon, add Union to a new line, start a new line and paste in the second sql statement. Repeat for as many original queries that you need to. The caveat is that each sql part has to have the same number of fields, preferably with the same names. If the names are different but the data types are compatible, you can use aliases for field names that don't jive from one query to the next. I say that the data types have to be alike because you intend (I believe) to make or append these results to a table.

My posts are based on satisfying this requirement:
When I do A and B I notice that there's some ID from query A that are not in query B. It's expected but how do I get a single table that shows them together?
with the understanding that you have figured out how to get results individually but not how to put them together:
A-freq of all par >0 in 2012 that had no LS in 2012
B-freq of all LS in 2013 to 2015 that had par >0 in 2012

 

[brickbuilder]

Correct, I'm trying to put the results together. Is it correct that to make the union I'll have to add empty fields to both results A and B so that they both have the same # of fields and in the same order? How will it know to match the IDs because the rows are not in order and there's a different number of rows in each result? Thanks again

 

[Micron]

Is it correct that to make the union I'll have to add empty fields to both results A and B so that they both have the same # of fields and in the same order?

Answered already: The caveat is that each sql part has to have the same number of fields

How will it know to match the IDs because the rows are not in order and there's a different number of rows in each result? Thanks again

I don't understand what you're getting at about matching id's. We seem to agree that you have two or more queries that provide what you want - just not together at the same time. The Union query will 'assemble' them. It's like you have a container of blue marbles and one of red. You dump each into a new bucket and you have your melded group. There is no matching to be done. If that doesn't relate to your scenario, then I don't know what's going on in spite of you agreeing with my guesses. I cannot tell you if you have to add dummy fields or not since your posts don't include much source data or result data. As I mentioned, I struggle with understanding the exact requirements.

At least try to create a Union query from your other Select queries, based on the information given, and see what happens. You might get on to something and figure it out. Right now I just hear wheels spinning. If your sql statements aren't too long and difficult to follow, post those for us to look at, but do try your own Union query first.

 

[brickbuilder]

Hi,

I made some Union queries and it's not what I'm looking for.

I don't understand what you're getting at about matching id's

I know that this is because I haven't worded my question clearly yet. Thanks for your patience!

I originally made 2 crosstab queries (named A and B) which shows the frequency of the IDs with the given conditions. What I want to do is match crosstab A with crosstab B based on IDs. What I mean by that is the "new query" (don't know what this will be...) looks at each ID from crosstab A and then tries to find the matching ID in crosstab B. If there is a match then return the matching value from crosstab B. If there is no match then return 0. The end result is that, in the "new query", there are additional fields appended to the crosstab A query and the number of rows in the "new query" is the same as crosstab A.

In excel this could be accomplished with VLOOKUP or INDEX MATCH. I'd like to know how to do this in Access.

Thanks again!

 

[Micron]

Did you look at the Find Duplicates query wizard (finding duplicate ID from A in B)? Sounds like you'd need to add a calculated field that either returns 0 or the count (or whatever the "matching" value is).