Union Query requesting parameter

  • Thread starter Thread starter Tige Brown
  • Start date Start date
T

Tige Brown

I have the following union query to consolidate transactions from an actual
sales table located on a Microsoft SQL server database table with budget
transactions located on an access table. The actual transactions are linked
to the access database.

select * from Qry_Aus_Budget_Sales
UNION select * from Qry_Aus_Actual_Sales;

When I run this query I am requested to provide a parameter for
"Query1.unique_no". I have no query entitled "Query1". "unique_no" is the
primary key in the actual sales table.

For the budget table I created a similar key with the name ID. In the
Qry_Aus_Budget_Sales query I used table_name.ID as unique_no to rename the
field.

Some further information, I am working with two countries with separate
tables for both actual and budget data. I first created the tables needed
and queries for one country and then used this as a template to create the
tables & queries for the second country. The first country is working fine
and does not ask me for a parameter input, only the second country's union
query.

I have tried renaming the relevant field in the budget sales table as
unique_no thereby not requiring a name change but this has had no effect on
the parameter request.

Thanks in advance for any assistance.
 
Suggestions:

1. Make sure Name AutoCorrect is turned off.
Explanation of why:
http://allenbrowne.com/bug-03.html
Then compact/repair the database.

2. Open each of the following in turn, and clear the Filter and OrderBy
properites:
a) the budget table
b) the sales table
c) Qry_Aus_Budget_Sales
d) Qry_Aus_Actual_Sales
e) the UNION query.

Post back if still stuck.
 
Thanks for your response Gina, none of the preceding queries has an ORDER BY
clause in them.

I forgot to mention that I have used a query on the general ledger table,
selecting year and period, to identify the transactions to be included in my
actual sales listing query, but here again, no ORDER BY clause.
 
Tige said:
I have the following union query to consolidate transactions from an actual
sales table located on a Microsoft SQL server database table with budget
transactions located on an access table. The actual transactions are linked
to the access database.

select * from Qry_Aus_Budget_Sales
UNION select * from Qry_Aus_Actual_Sales;

When I run this query I am requested to provide a parameter for
"Query1.unique_no". I have no query entitled "Query1". "unique_no" is the
primary key in the actual sales table.
Click to expand...


This is not an answer, but a warning to never use * in a
UNION's SELECT statements. The tables/queries used in a
UNION query NUST have the same number of fields and they
must be the same data type (Number, Text, Date, etc). Using
* can cause trouble if and underlying table/query is
modified to add or remove a field.
 
Allen,

Qry_Aus_Actual_Sales

SELECT DISTINCTROW aussys_scheme_opsahistm.unique_no, "Actual" AS Source,
"AUS" AS Country, Qry_aus_Sales_Select.Year, Qry_aus_Sales_Select.Period,
Tbl_Ind_Codes.Ind_Parent, aussys_scheme_slcustm.analysis_codes2 AS Industry,
Tbl_aus_Cust_Rep_Mgr.Mgr, Tbl_aus_Cust_Rep_Mgr.Rep,
aussys_scheme_opsahistm.customer, aussys_scheme_slcustm.name,
aussys_scheme_opsahistm.product,
IIf(IsNull([aussys_scheme_stockm.alpha]),[aussys_scheme_opsahistm.product],[aussys_scheme_stockm.alpha])
AS Description, aussys_scheme_opsahistm.quantity,
aussys_scheme_opsahistm.val, aussys_scheme_opsahistm.cost, [val]-[cost] AS
margin, [NZD/USD]/[AUD/USD] AS [NZD/AUD],
Tbl_Buckman_Xrates_Period.[AUD/USD], Round([val]/[NZD/AUD],2) AS [AUD Sales],
Round([cost]/[NZD/AUD],2) AS [AUD cost], Round([margin]/[NZD/AUD],2) AS [AUD
margin], Round([AUD Sales]/[AUD/USD],2) AS [USD Sales], Round([AUD
cost]/[AUD/USD],2) AS [USD cost], Round([AUD margin]/[AUD/USD],2) AS [USD
margin]
FROM (((((aussys_scheme_opsahistm INNER JOIN aussys_scheme_slcustm ON
aussys_scheme_opsahistm.customer = aussys_scheme_slcustm.customer) INNER JOIN
Qry_aus_Sales_Select ON aussys_scheme_opsahistm.invoice =
Qry_aus_Sales_Select.journal_number) LEFT JOIN aussys_scheme_stockm ON
(aussys_scheme_opsahistm.warehouse = aussys_scheme_stockm.warehouse) AND
(aussys_scheme_opsahistm.product = aussys_scheme_stockm.product)) LEFT JOIN
Tbl_aus_Cust_Rep_Mgr ON aussys_scheme_slcustm.customer =
Tbl_aus_Cust_Rep_Mgr.Customer) LEFT JOIN Tbl_Ind_Codes ON
aussys_scheme_slcustm.analysis_codes2 = Tbl_Ind_Codes.Industry) INNER JOIN
Tbl_Buckman_Xrates_Period ON (Qry_aus_Sales_Select.Year =
Tbl_Buckman_Xrates_Period.Year) AND (Qry_aus_Sales_Select.Period =
Tbl_Buckman_Xrates_Period.Period);

Qry_Aus_Budget_Sales

SELECT DISTINCT Tbl_Aus_Sales_Budget.unique_no, "Budget" AS Source, "Aus" AS
Country, Tbl_Budget_Pds.Year, Tbl_Budget_Pds.Period,
Tbl_Ind_Codes.Ind_Parent, Aussys_scheme_slcustm.analysis_codes2 AS Industry,
Tbl_Aus_Cust_Rep_Mgr.Mgr, Tbl_Aus_Cust_Rep_Mgr.Rep,
Tbl_Aus_Sales_Budget.customer, Aussys_scheme_slcustm.name,
Tbl_Aus_Sales_Budget.product,
IIf(IsNull([Aussys_scheme_stockm.alpha]),[Tbl_Aus_Sales_Budget.product],[Aussys_scheme_stockm.alpha])
AS Description, Tbl_Aus_Sales_Budget.quantity, Tbl_Aus_Sales_Budget.val,
Tbl_Aus_Sales_Budget.cost, [val]-[cost] AS margin,
[Tbl_Budget_Pds.NZD/USD]/[Tbl_Budget_Pds.AUD/USD] AS [NZD/AUD],
Tbl_Budget_Pds.[AUD/USD], Tbl_Aus_Sales_Budget.val AS [AUD Sales],
Tbl_Aus_Sales_Budget.cost AS [AUD cost], [margin] AS [AUD margin], Round([AUD
Sales]/[AUD/USD],2) AS [USD Sales], Round([AUD cost]/[AUD/USD],2) AS [USD
cost], Round([AUD margin]/[AUD/USD],2) AS [USD margin]
FROM ((((Tbl_Budget_Pds INNER JOIN Tbl_Aus_Sales_Budget ON
(Tbl_Budget_Pds.Year = Tbl_Aus_Sales_Budget.Year) AND (Tbl_Budget_Pds.Period
= Tbl_Aus_Sales_Budget.Period)) LEFT JOIN Aussys_scheme_slcustm ON
Tbl_Aus_Sales_Budget.customer = Aussys_scheme_slcustm.customer) LEFT JOIN
Aussys_scheme_stockm ON Tbl_Aus_Sales_Budget.product =
Aussys_scheme_stockm.product) LEFT JOIN Tbl_Ind_Codes ON
Aussys_scheme_slcustm.analysis_codes2 = Tbl_Ind_Codes.Industry) LEFT JOIN
Tbl_Aus_Cust_Rep_Mgr ON Aussys_scheme_slcustm.customer =
Tbl_Aus_Cust_Rep_Mgr.Customer
WHERE (((Tbl_Budget_Pds.Select_Pd)=On));

Sorry about the size, that is why I didn't post them originally.

Thanks for your efforts.

Tige
 
Hmm. That's not getting to the bottom of it either. These queries draw from
other queries also (e.g. Qry_aus_Sales_Select), so the problem could be
deeper down.

If it's not merely a Name AutoCorrect issue, you might be able to use a
utility like this to track down where the name is used:
Where is a field used?
at:
http://allenbrowne.com/ser-73.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Qry_Aus_Actual_Sales
====================
SELECT DISTINCTROW aussys_scheme_opsahistm.unique_no,
"Actual" AS Source,
"AUS" AS Country,
Qry_aus_Sales_Select.Year,
Qry_aus_Sales_Select.Period,
Tbl_Ind_Codes.Ind_Parent,
aussys_scheme_slcustm.analysis_codes2 AS Industry,
Tbl_aus_Cust_Rep_Mgr.Mgr,
Tbl_aus_Cust_Rep_Mgr.Rep,
aussys_scheme_opsahistm.customer,
aussys_scheme_slcustm.name,
aussys_scheme_opsahistm.product,
IIf(IsNull([aussys_scheme_stockm.alpha]),
[aussys_scheme_opsahistm.product],
[aussys_scheme_stockm.alpha]) AS Description,
aussys_scheme_opsahistm.quantity,
aussys_scheme_opsahistm.val,
aussys_scheme_opsahistm.cost,
[val]-[cost] AS margin,
[NZD/USD]/[AUD/USD] AS [NZD/AUD],
Tbl_Buckman_Xrates_Period.[AUD/USD],
Round([val]/[NZD/AUD],2) AS [AUD Sales],
Round([cost]/[NZD/AUD],2) AS [AUD cost],
Round([margin]/[NZD/AUD],2) AS [AUD margin],
Round([AUD Sales]/[AUD/USD],2) AS [USD Sales],
Round([AUD cost]/[AUD/USD],2) AS [USD cost],
Round([AUD margin]/[AUD/USD],2) AS [USD margin]
FROM (((((aussys_scheme_opsahistm INNER JOIN aussys_scheme_slcustm
ON aussys_scheme_opsahistm.customer = aussys_scheme_slcustm.customer)
INNER JOIN Qry_aus_Sales_Select
ON aussys_scheme_opsahistm.invoice
= Qry_aus_Sales_Select.journal_number)
LEFT JOIN aussys_scheme_stockm
ON (aussys_scheme_opsahistm.warehouse
= aussys_scheme_stockm.warehouse)
AND (aussys_scheme_opsahistm.product
= aussys_scheme_stockm.product))
LEFT JOIN Tbl_aus_Cust_Rep_Mgr
ON aussys_scheme_slcustm.customer
= Tbl_aus_Cust_Rep_Mgr.Customer)
LEFT JOIN Tbl_Ind_Codes
ON aussys_scheme_slcustm.analysis_codes2 = Tbl_Ind_Codes.Industry)
INNER JOIN Tbl_Buckman_Xrates_Period
ON (Qry_aus_Sales_Select.Year = Tbl_Buckman_Xrates_Period.Year)
AND (Qry_aus_Sales_Select.Period = Tbl_Buckman_Xrates_Period.Period);


Qry_Aus_Budget_Sales
===================
SELECT DISTINCT Tbl_Aus_Sales_Budget.unique_no,
"Budget" AS Source,
"Aus" AS Country,
Tbl_Budget_Pds.Year,
Tbl_Budget_Pds.Period,
Tbl_Ind_Codes.Ind_Parent,
Aussys_scheme_slcustm.analysis_codes2 AS Industry,
Tbl_Aus_Cust_Rep_Mgr.Mgr,
Tbl_Aus_Cust_Rep_Mgr.Rep,
Tbl_Aus_Sales_Budget.customer,
Aussys_scheme_slcustm.name,
Tbl_Aus_Sales_Budget.product,
IIf(IsNull([Aussys_scheme_stockm.alpha]),
[Tbl_Aus_Sales_Budget.product],
[Aussys_scheme_stockm.alpha]) AS Description,
Tbl_Aus_Sales_Budget.quantity,
Tbl_Aus_Sales_Budget.val,
Tbl_Aus_Sales_Budget.cost,
[val]-[cost] AS margin,
[Tbl_Budget_Pds.NZD/USD]/[Tbl_Budget_Pds.AUD/USD] AS [NZD/AUD],
Tbl_Budget_Pds.[AUD/USD],
Tbl_Aus_Sales_Budget.val AS [AUD Sales],
Tbl_Aus_Sales_Budget.cost AS [AUD cost],
[margin] AS [AUD margin],
Round([AUD Sales]/[AUD/USD],2) AS [USD Sales],
Round([AUD cost]/[AUD/USD],2) AS [USD cost],
Round([AUD margin]/[AUD/USD],2) AS [USD margin]
FROM ((((Tbl_Budget_Pds INNER JOIN Tbl_Aus_Sales_Budget
ON (Tbl_Budget_Pds.Year = Tbl_Aus_Sales_Budget.Year)
AND (Tbl_Budget_Pds.Period = Tbl_Aus_Sales_Budget.Period))
LEFT JOIN Aussys_scheme_slcustm
ON Tbl_Aus_Sales_Budget.customer
= Aussys_scheme_slcustm.customer)
LEFT JOIN Aussys_scheme_stockm
ON Tbl_Aus_Sales_Budget.product
= Aussys_scheme_stockm.product)
LEFT JOIN Tbl_Ind_Codes
ON Aussys_scheme_slcustm.analysis_codes2
= Tbl_Ind_Codes.Industry)
LEFT JOIN Tbl_Aus_Cust_Rep_Mgr
ON Aussys_scheme_slcustm.customer
= Tbl_Aus_Cust_Rep_Mgr.Customer
WHERE (((Tbl_Budget_Pds.Select_Pd)=On));
 
Allen,

Ahaaaaah, see in particular the items below I have flagged with *****

Here is the results of the findfield() grouped and headed by further comment;

These tables & queries were the original I set up from scratch. They relate
to the NZ entity and do not produce the error;

nzsys_scheme_nltranm.unique_no
nzsys_scheme_opsahistm.unique_no
Qry_NZ_Actual_Sales.unique_no
Qry_NZ_Actual_Sales.OrderBy: Qry_NZ_Actual_Sales.unique_no -> I've rechecked
this query and cannot find anywhere where the order of the output is set.
Qry_NZ_Budget_Sales.unique_no
Qry_NZ_Cons_Actual_and_Budget.unique_no

The following are the queries and tables related to the Aust entity;

aussys_scheme_nltranm.unique_no
aussys_scheme_opsahistm.unique_no
Tbl_Aus_Sales_Budget.unique_no
Qry_Aus_Actual_Sales.unique_no
Qry_Aus_Budget_Sales.unique_no
Qry_Aus_Cons_Actual_and_Budget.unique_no
Qry_Aus_Cons_Actual_and_Budget.OrderBy: Query1.unique_no *****
Qry_NZ_Actual_Sales.unique_no
Qry_NZ_Actual_Sales.OrderBy: Qry_NZ_Actual_Sales.unique_no
Qry_NZ_Budget_Sales.unique_no
Qry_NZ_Cons_Actual_and_Budget.unique_no


The following queries are consolidations of the above queries or reports
thereon;

Qry_Aus_NZ_Cons_Act_and_Bdgt.unique_no
Qry_Aus_NZ_Cons_Act_and_Bdgt.OrderBy: Qry_Aus_NZ_Cons_Act_and_Bdgt.unique_no
Qry_Summary.OrderBy: Qry_Aus_NZ_Cons_Act_and_Bdgt.unique_no
Tabular Summary Report.OrderBy: Qry_Aus_NZ_Cons_Act_and_Bdgt.unique_no
Qry_Summary Report.OrderBy: Query1.unique_no *****

The flagged items "Query1.unique_no" is the exact parameter that is
requested. I cannot find a "Query1" anywhere in my database although
obviously it is there somewhere.

Tige
 
Tige,

Remove that from the Order By of the Report. It could be that you have
since creating the Query renamed or deleted it by it wasn't removed from the
Order By section of the report. Usually that happens to me in a query but
it can also happen in a report.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Allen,

I found it. In the query properties the Order By field and same for the
Qry_Summary report.

I was not even aware these fields existed.

Thank you so much for your help.

Tige
 
Excellent! You've solved it.

Yep: those properies will do it.
(Item 2 of original response.)
 
Back
Top