Web Development

General discussion

Gravatar
0 Votes
Locked

Access Query

By RichTee ·
I have a query that combines the results of two other queries:
One query runs data for the current month.
One query runs data for the past month.

The purpose is to obtain two columns of values associated with each department used in each month.

Department Current Month Past Month

If the same department is used in both months, the values are side by side in the same recordset. If only one month has a value, the value in the other month will be a zero.

The two original queries contain the same fields.
One of the fields is ?department? and one other is ?dollars?.
There are 200 departments ? BUT ? not all departments are used each period, NOR, are the same departments always used each period. The report is to show only the departments where there is activity.

Scenario 1: query 1 and query 2 have the same number and Department_ID in each. ? No problem with this.

My problem is how to deal with the next two scenarios.

Scenario 2: query 1 has more departments than query 2

Scenario 3: query 2 has more department than query 1

If I set the join relationship for Scenario 2, then Scenario 3 returns skewed data (and vice versa).

Is there any way of resolving this join problem?

Thanks, Rich

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

Access Query

by JTS_GURU In reply to Access Query

DEAR RICH,
CHOOSE SCENAIRIO 1 AND STICK WITH IT.


GOOD LUCK

gravatar
Collapse -

Access Query

by RichTee In reply to Access Query

I misunderstand you or you misunderstand the question.

gravatar
Collapse -

Access Query

by john_wills In reply to Access Query

SELECT key, curDetails, lastDetails
FROM current LEFT JOIN last ON current.key=last.key
UNION
SELECT key, null AS curdetails, lastdetails
FROM last WHERE NOT EXISTS(SELECT curDetails FROM current WHERE current.key=last.key);
I have not tested this, but I am sure I have done something similar.

gravatar
Collapse -

Access Query

by RichTee In reply to Access Query

I apologize but I could not follow your answer in my particular situation.

gravatar
Collapse -

Access Query

by Todd Parker In reply to Access Query

Well the guy with answer two is on the right track but I fear it's not well explained. You really need an identifier field when using UNIONS because unions will combine recordsets, but they will also MERGE like data together. Ack! So use an identifier field.

Yes, you need two queries..

Query1:
Field1: IdentifierFld (0 as Integer)
Field2: Department (Varchar)
Field3: CurrMonth (Dollars)
Field4: PriorMonth (0 as Dollars)

Query1:
Field1: IdentifierFld (1 as Integer)
Field2: Department (Varchar)
Field3: CurrMonth (0 as Dollars)
Field4: PriorMonth (Dollars)

Then UNION the two with another Query:

Query3:
select IdentifierFld , Department, CurrMonth, PriorMonth from Query1
UNION
select IdentifierFld , Department, CurrMonth, PriorMonth from Query2

Then Summarize the data without the unique identifier.

Query4:
Select Department, CurrMonth, PriorMonth from
Query3 group by Department, CurrMonth, PriorMonth

That's it. You've combined the results of two queries into one. I would suggest you read up more on union queries. It'll really help you out, especially if you deal with combining results of otherwise unconnected data.

Questions? Just ask.

gravatar
Collapse -

Access Query

by RichTee In reply to Access Query

This started me on the right direction. I actually ended up doing the same as Answer #5 before I read his/her answer.

gravatar
Collapse -

Access Query

by Todd Parker In reply to Access Query

Well the guy with answer two is on the right track but I fear it's not well explained. You really need an identifier field when using UNIONS because unions will combine recordsets, but they will also MERGE like data together. Ack! So use an identifier field.

Yes, you need two queries..

Query1:
Field1: IdentifierFld (0 as Integer)
Field2: Department (Varchar)
Field3: CurrMonth (Dollars)
Field4: PriorMonth (0 as Dollars)

Query2:
Field1: IdentifierFld (1 as Integer)
Field2: Department (Varchar)
Field3: CurrMonth (0 as Dollars)
Field4: PriorMonth (Dollars)

Then UNION the two with another Query:

Query3:
select IdentifierFld , Department, CurrMonth, PriorMonth from Query1
UNION
select IdentifierFld , Department, CurrMonth, PriorMonth from Query2

Then Summarize the data without the unique identifier.

Query4:
Select Department, CurrMonth, PriorMonth from
Query3 group by Department, CurrMonth, PriorMonth

That's it. You've combined the results of two queries into one. I would suggest you read up more on union queries. It'll really help you out, especially if you deal with combining results of otherwise unconnected data.

Questions? Just ask.

gravatar
Collapse -

Access Query

by RichTee In reply to Access Query

This started me on the right direction. I actually ended up doing the same as Answer #5 before I read his/her answer.
Double answer: Gave an acceptable answer #3
Thanks

gravatar
Collapse -

Access Query

by dsa In reply to Access Query

Use your Dept table or create a table with the 200 departments.
Create query3 using Dept table, query1 and query2
Join query1 to dept table using join property 2
Join query2 to dept table using joing property 2
Use Dept Field in dept table in the new query
add PmDlr field
PmDlr: Iif(query1![Dlr]<>0,query1![Dlr],0)
CmDlr: Iif(query2![Dlr]<>0,query2![Dlr],0)

This should give you the result you want.

gravatar
Collapse -

Access Query

by RichTee In reply to Access Query

I accomplished my resolution with the same procedure you suggested. Thanks

Back to Web Development Forum

Start or search

Related Discussions

Related Forums