query using 2 tables and a query

G

Guest

How do I create a query for a report that...

The project (table tProj) can have an impact (table tImpact) or a finance
(query q5Finance) OR both.

I want the query to show - all projects and all Impacts and all Finance per
project and month.

table - tProj table - tImpacts query - q5Finance
ProjID - Key Impact ID - Key FinID - Key
Customer ProjID ProjID
Category Scope Price
OrderNo ImpactMonth FinanceMonth

New query to look like this...

ProjID customer category orderno scope impactmo(jun07) Price
financemo(jun07)
ProjID customer category orderno scope (no impact or finance data)
ProjID customer category orderno scope impactmo(jun07) (only impact data)
ProjID customer category orderno Price financemo(jun07)(only finance data)

How an i make this happen?
 
G

Guest

Hi Deb

You can do this by using outer joins from the project table - this shows all
rows from projects whether they have a corresponding row in impacts or
finance.

The SQL below should do the trick.

I notice that you have listed impactmo and financemo. You don't say how you
are choosing which month to display so the SQL below prompts for this using
the parameter [Enter Report Month]. You will probably want to change that so
the month is typed into or picked from within a form.


select tProj.ProjID, tProj.Customer, tProj.Category, tProj.OrderNo, [Enter
Report Month], tImpacts.Scope, q5Finance.FinanceMonth
from (tProj left join tImpacts on tProj.ProjID = tImpacts.ProjID) left join
q5Finance
on tProj.ProjID = q5Finance.ProjID
where ((tImpacts.ImpactID is null) or (tImpacts.ImpactMonth = [Enter Report
Month]))
and ((q5Finance.FinID is null) or (q5Finance.FinanceMonth = [Enter Report
Month]))


hth

Andy Hull
 
G

Guest

I am not sure what you mean by [Enter Report Month] in the select area.
The impacts table has a month and the finance query has a month but the proj
table does not have a month it only stores the project data.

Which date is the select statment looking for and how do I enter it??

select tProj.ProjID, tProj.Customer, tProj.Category, tProj.OrderNo, [Enter
Report Month], tImpacts.Scope, q5Finance.FinanceMonth


Thank you for your help, It is making me crazy trying to work this problem
out.

--
deb


Andy Hull said:
Hi Deb

You can do this by using outer joins from the project table - this shows all
rows from projects whether they have a corresponding row in impacts or
finance.

The SQL below should do the trick.

I notice that you have listed impactmo and financemo. You don't say how you
are choosing which month to display so the SQL below prompts for this using
the parameter [Enter Report Month]. You will probably want to change that so
the month is typed into or picked from within a form.


select tProj.ProjID, tProj.Customer, tProj.Category, tProj.OrderNo, [Enter
Report Month], tImpacts.Scope, q5Finance.FinanceMonth
from (tProj left join tImpacts on tProj.ProjID = tImpacts.ProjID) left join
q5Finance
on tProj.ProjID = q5Finance.ProjID
where ((tImpacts.ImpactID is null) or (tImpacts.ImpactMonth = [Enter Report
Month]))
and ((q5Finance.FinID is null) or (q5Finance.FinanceMonth = [Enter Report
Month]))


hth

Andy Hull


deb said:
How do I create a query for a report that...

The project (table tProj) can have an impact (table tImpact) or a finance
(query q5Finance) OR both.

I want the query to show - all projects and all Impacts and all Finance per
project and month.

table - tProj table - tImpacts query - q5Finance
ProjID - Key Impact ID - Key FinID - Key
Customer ProjID ProjID
Category Scope Price
OrderNo ImpactMonth FinanceMonth

New query to look like this...

ProjID customer category orderno scope impactmo(jun07) Price
financemo(jun07)
ProjID customer category orderno scope (no impact or finance data)
ProjID customer category orderno scope impactmo(jun07) (only impact data)
ProjID customer category orderno Price financemo(jun07)(only finance data)

How an i make this happen?
Click to expand...
Click to expand...
 
G

Guest

Hi again

If you create the query as I've written it by creating a new query and going
into the SQL view and copying and pasting the query in...

When you run it, Access will treat [Enter Report Month] as an unknown
parameter so you can type it in. This may not be what you want in the end
but will allow you to test the query for now.

The select part uses the parameter and will display it in the results.
The WHERE part also uses the parameter and looks at tImpacts.ImpactMonth and
q5Finance.FinanceMonth and only picks those that match what was typed in (but
it will also show the project if it has no impact or finance). If this works
then you could create a form where the user can select the month from a drop
down list but test it as it is first.

I am assuming tImpacts and q5Finance hold their months in the same format.

If you have no luck, post back a couple of sample rows of data from the 3
tables.

Good luck

Andy Hull


deb said:
I am not sure what you mean by [Enter Report Month] in the select area.
The impacts table has a month and the finance query has a month but the proj
table does not have a month it only stores the project data.

Which date is the select statment looking for and how do I enter it??

select tProj.ProjID, tProj.Customer, tProj.Category, tProj.OrderNo, [Enter
Report Month], tImpacts.Scope, q5Finance.FinanceMonth


Thank you for your help, It is making me crazy trying to work this problem
out.

--
deb


Andy Hull said:
Hi Deb

You can do this by using outer joins from the project table - this shows all
rows from projects whether they have a corresponding row in impacts or
finance.

The SQL below should do the trick.

I notice that you have listed impactmo and financemo. You don't say how you
are choosing which month to display so the SQL below prompts for this using
the parameter [Enter Report Month]. You will probably want to change that so
the month is typed into or picked from within a form.


select tProj.ProjID, tProj.Customer, tProj.Category, tProj.OrderNo, [Enter
Report Month], tImpacts.Scope, q5Finance.FinanceMonth
from (tProj left join tImpacts on tProj.ProjID = tImpacts.ProjID) left join
q5Finance
on tProj.ProjID = q5Finance.ProjID
where ((tImpacts.ImpactID is null) or (tImpacts.ImpactMonth = [Enter Report
Month]))
and ((q5Finance.FinID is null) or (q5Finance.FinanceMonth = [Enter Report
Month]))


hth

Andy Hull


deb said:
How do I create a query for a report that...

The project (table tProj) can have an impact (table tImpact) or a finance
(query q5Finance) OR both.

I want the query to show - all projects and all Impacts and all Finance per
project and month.

table - tProj table - tImpacts query - q5Finance
ProjID - Key Impact ID - Key FinID - Key
Customer ProjID ProjID
Category Scope Price
OrderNo ImpactMonth FinanceMonth

New query to look like this...

ProjID customer category orderno scope impactmo(jun07) Price
financemo(jun07)
ProjID customer category orderno scope (no impact or finance data)
ProjID customer category orderno scope impactmo(jun07) (only impact data)
ProjID customer category orderno Price financemo(jun07)(only finance data)

How an i make this happen?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query giving table heading Cust Name and 12 monthly sales (ie J F 1
Combo Box Query: Listing a subset - Revisited 2
Using Multiple Parameters in a Query 2
Select query driving me mad (using *) 4
Query to find select columns in several tables....Help! 2
Query works but report has no data 2
Retrieving records with a query by form. 3
Using a Query to find multiple items 2

Top