How to add 3 queries that are not connected

  • Thread starter Thread starter zaskodnik
  • Start date Start date
Z

zaskodnik

Hi,
I have 3 queries.

1.
Summary of backlog:
looks like this:
Territory__QTY___Ext.Price
Korea______50____200
Taiwan_____10____100
Europe_____5_____50

2.
summary of shipments:
Territory__QTY___Ext.Price
Korea______60____500
Taiwan_____90____200
Europe_____6_____650
USA________65____542

3.
Summary of forecast:
Territory__QTY___Ext.Price
Korea______45____853
Taiwan_____20____10

I need to create 1 query.... and then 1 report that will look lik
this:

_______________BACKLOG_______Shipped___________Forecasted
Territory__QTY___Ext.Price___QTY___Ext.Price____QTY____Ext.Price
Korea______50____200_________60______500_________45______853
Taiwan_____10____100_________90______200_________20______10
Europe_____5_____50___________6______650__________0_______0
USA________0_____0___________65______542__________0_______0

I can not add it in one query, since wizard can not connect records.
Also, sometimes the values are zero and then it does not show it i
query. (for example USA has no forecast)

Any suggestions?
Thank you
 
Zaskodnik,

Assuming you have a Territories table (or whatever it is called in your
database), make a query in query design view, based on this table, plus
all 3 of the existing queries. Then, join the 3 existing queries to the
Territories table on the Territory field. And then make these into Left
Joins, i.e. if you double-click on the loin line, and then select the
option that says something like "all from Territories, and only matching
records from the existing query". Then put all relevant fields from the
existing queries into the query design grid, and in the case of fields
where there may not be data, use the Nz() function to convert to 0. SO
the SQL for this query might look something like this...

SELECT Territories.Territory, Nz([Backlog].[QTY],0) AS BacklogQty,
Nz([Backlog].[ExtPrice],0) AS BacklogExt, Nz([Shipped].[QTY],0) AS
ShippedQty, Nz([Shipped].[ExtPrice],0) AS ShippedExt,
Nz([Forecasted].[QTY],0) AS ForecastQty, Nz([Forecasted].[ExtPrice],0)
AS ForecastExt
FROM ((Territories LEFT JOIN Shipped ON Territories.Territory =
Shipped.Territory) LEFT JOIN Forecasted ON Territories.Territory =
Forecasted.Territory) LEFT JOIN Backlog ON Territories.Territory =
Backlog.Territory
 
First of all, you will need a table (or query) that has all territories. If
you have such a table fine, otherwise the safest way to do it (so it gets
dynamiccally updated as you add to the other tables) is to create a union
query on that field form the other three, and use that instead. Whichever is
the case, I'll assume it is called Territories for the sake of this example.
Then, make a new query and add table (or query) Territories, and the three
summary queries, and join them on territory, then double click on the join
line and select option "Include all records from Territories and only those
records from XXXX where ..." - you should see the join line change from a
plain line to an arrow pointing towards each of the three summary queries.
This done, get the Territory field from Territories down to the grid, and
then the Qty and Ext.Price fields from each of the three queries, and you're
done!

HTH,
Nikos
 
I did what you told me and it works! I have a table, but some fields ar
blank and I need to fill it with zeros.... cause I can not creat
report using what I have.

Steve gave me some SQL code and wrote:
"Then put all relevant fields from the
existing queries into the query design grid, and in the case of fields
where there may not be data, use the Nz() function to convert to 0. SO
the SQL for this query might look something like this..."

Sorry, I am a newbie... so how do I get to see the SQL code? I kno
that "ctrl+G" throws me to VBA code. But how to get to SQL code?

Is there only one way how to fill zeros to blank cells in th
spreadsheet created by my query? Do I have to go to code or can
somehowe set it in some property window?

Thank you! You helped me to do the bigger step! I just need to fix th
zeros and I'm done.
Thank you again
 
I did what you told me and it works! I have a table, but some fields ar
blank and I need to fill it with zeros.... cause I can not creat
report using what I have.

Steve gave me some SQL code and wrote:
"Then put all relevant fields from the
existing queries into the query design grid, and in the case of fields
where there may not be data, use the Nz() function to convert to 0. SO
the SQL for this query might look something like this..."

Sorry, I am a newbie... so how do I get to see the SQL code? I kno
that "ctrl+G" throws me to VBA code. But how to get to SQL code?

Is there only one way how to fill zeros to blank cells in th
spreadsheet created by my query? Do I have to go to code or can
somehowe set it in some property window?

Thank you! You helped me to do the bigger step! I just need to fix th
zeros and I'm done.
Thank you again
 
Thank you Douglas!

I was able to add the SQL code and the table now has zeros in the blan
cells.

There is only one problem left. When I try to make report using m
query, I add everything in report wizard and then I am ready to previe
report, it says this error message:
"The wizard is unable to preview your report, possibly because anothe
user has a source table open in exclusive mode. Your report will b
opened in design view."
I know for sure that nobody is sharing my database with me at thi
time. Do you have any experience with this error?

Otherwise the query looks great with the zeros added! Thank you
 
It also says:
"The speciefied field 'Territories.Territories' could refer to mor
than one table listed in the 'FROM' clause of your SQL statement."

I don't get it. Wht can not I make simple report using simple query?
Query looks good.... it has all the data. I just want to put it al
into simple report
 
Here is the code of the query.... that can not be displayed in report:

SELECT [Territories].[Territories], Nz([SUMMARY JUNE backlog
(by territory)].[Sum Of Quantity],0) AS BacklogQTY, NZ([SUMMARY JUNE
backlog (by territory)].[Sum Of ExtPrice],0) AS BacklogExtPrice
Nz([SUMMARY JUNE shipments (by territory)].[Sum Of Quantity],0) A
ShipQTY, Nz([SUMMARY JUNE shipments (by territory)].[Sum O
ExtPrice],0) AS ShipExtPrice, Nz([SUMMARY JUNE forecast (b
territory)].[Sum Of Quantity],0) AS ForecQTY, Nz([SUMMARY JUNE
forecast (by territory)].[Sum Of ExtPrice],0) AS ForecExtPrice
FROM [SUMMARY JUNE forecast (by territory)] RIGHT JOIN ([SUMMAR
JUNE shipments (by territory)] RIGHT JOIN ([SUMMARY JUNE
backlog (by territory)] RIGHT JOIN Territories ON [SUMMARY JUNE
backlog (by territory)].[Territories]=[Territories].[Territories]
ON [SUMMARY JUNE shipments (b
territory)].[Territories]=[Territories].[Territories]) ON [SUMMARY
JUNE forecast (b
territory)].[Territories]=[Territories].[Territories]
 
Did you ever consider a naming convention so that your table and field names
aren't the same? Consider reviewing the naming convention link at Tony Toews
site http://www.granite.ab.ca/access/tipsindex.htm.
I don't know if this will fix your issue but it might start you off in the
proper direction for the future.
 
That is exactly what I wanted to do, but when I changed column in tabl
Territories from "Territories" to "Territory" and then I want to vie
my reports, it is asking for "Enter parameter valu
"Territories.Territories".
How do I change it so that it will update it in every query, form an
report
 
That is exactly what I wanted to do, but when I changed column in tabl
Territories from "Territories" to "Territory" and then I want to vie
my reports, it is asking for "Enter parameter valu
"Territories.Territories".
How do I change it so that it will update it in every query, form an
report
 
I can oficially end this thread!
I just played with it a bit more and fixed it.
Thank you for the naming conventions link. It is really helpful to nam
columns reasonably.
Thank you all for being so kind and helpful
 
I can oficially end this thread!
I just played with it a bit more and fixed it.
Thank you for the naming conventions link. It is really helpful to nam
columns reasonably.
Thank you all for being so kind and helpful
 
Back
Top