Union query Question

[Guest]

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

How do i add to the above
'[All]', -1
so that my combobox can have [all] if I want to select all the the records?

 

[Guest]

Create a local table that has two fields [CompanyName], [City] write in the
company "ALL"
And then add this table to the Union table

 

[Guest]

I am not sure I understand how this works, because I want my combobox to have
all the companies, such that if i select a specific company, I get
information for that company alone, but if I want all the companies
information, then I will have the option of selecting [All]

Create a local table that has two fields [CompanyName], [City] write in the
company "ALL"
And then add this table to the Union table

--
\\// Live Long and Prosper \\//
BS"D

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

How do i add to the above
'[All]', -1
so that my combobox can have [all] if I want to select all the the records?

 

[George Nicholson]

SELECT [CompanyName], [City]

FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

Remove the closing semicolon and add:
UNION SELECT "(All)" as [CompanyName], -1 As [City]
FROM [Customers]
ORDER BY [CompanyName];

Also: Remove the ALL from UNION ALL unless you want duplicate CompanyNames
and Cities in your combo box.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

How do i add to the above
'[All]', -1
so that my combobox can have [all] if I want to select all the the
records?

 

[Guest]

Thanks for the answer, I was trying to apply that same knowledge in my
combobox, but its not working here is my actual code.....

SELECT DISTINCT Format$([TxDocDateRcvd],"ww") AS wks, IIf(Not
IsNull([TxDocDateRcvd]),Format(DateValue(DateAdd("d",1-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0)
& " -- " &
Format(DateValue(DateAdd("d",7-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0))
AS Weeks, Format$([TxDocDateRcvd],"yyww") AS Expr1,
Format$([TxDocDateRcvd],"ww") AS expr2
FROM tblTaxDoc
GROUP BY Format$([TxDocDateRcvd],"ww"), IIf(Not
IsNull([TxDocDateRcvd]),Format(DateValue(DateAdd("d",1-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0)
& " -- " &
Format(DateValue(DateAdd("d",7-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0)), Format$([TxDocDateRcvd],"yyww"), Format$([TxDocDateRcvd],"ww")
ORDER BY Format$([TxDocDateRcvd],"yyww") DESC;

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

Remove the closing semicolon and add:
UNION SELECT "(All)" as [CompanyName], -1 As [City]
FROM [Customers]
ORDER BY [CompanyName];

Also: Remove the ALL from UNION ALL unless you want duplicate CompanyNames
and Cities in your combo box.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

How do i add to the above
'[All]', -1
so that my combobox can have [all] if I want to select all the the
records?

 

[Guest]

Create a table - 2 fields [CompanyName], [City]
Call the table TempTable
In the table, insert "ALL" in the field [CompanyName], just one record

To the query add

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers] UNION
SELECT [CompanyName], [City]
FROM [TempTable]


That will add All to the output of the query
--
\\// Live Long and Prosper \\//
BS"D

I am not sure I understand how this works, because I want my combobox to have
all the companies, such that if i select a specific company, I get
information for that company alone, but if I want all the companies
information, then I will have the option of selecting [All]

Create a local table that has two fields [CompanyName], [City] write in the
company "ALL"
And then add this table to the Union table

--
\\// Live Long and Prosper \\//
BS"D

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

How do i add to the above
'[All]', -1
so that my combobox can have [all] if I want to select all the the records?

 

[Guest]

Check this link

http://www.mvps.org/access/forms/frm0043.htm

--
\\// Live Long and Prosper \\//
BS"D

I am not sure I understand how this works, because I want my combobox to have
all the companies, such that if i select a specific company, I get
information for that company alone, but if I want all the companies
information, then I will have the option of selecting [All]

Create a local table that has two fields [CompanyName], [City] write in the
company "ALL"
And then add this table to the Union table

--
\\// Live Long and Prosper \\//
BS"D

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

How do i add to the above
'[All]', -1
so that my combobox can have [all] if I want to select all the the records?

 

[George Nicholson]

maybe something like:

SELECT...
....(through end of GROUP BY clause)
UNION SELECT "(All)" AS wks, "(All)" AS Weeks, "(All)" AS Expr1, "(All)"
AS Expr2
FROM tblTaxDoc
ORDER BY Format$([TxDocDateRcvd],"yyww") DESC;

I counted 4 fields being returned by your query (wks, Weeks, Exp1, Exp2),
but that might be wrong. Add or subtract "(All)" s until they are
equivalent to the field count.

You don't actually need to add the Aliases (i.e., "..AS xxx") in this part
of the query since only those in the first SELECT have any affect on the
output, but it does no harm either. I did so strictly for clarity.


HTH,
--
George Nicholson

Remove 'Junk' from return address.

Thanks for the answer, I was trying to apply that same knowledge in my
combobox, but its not working here is my actual code.....

SELECT DISTINCT Format$([TxDocDateRcvd],"ww") AS wks, IIf(Not
IsNull([TxDocDateRcvd]),Format(DateValue(DateAdd("d",1-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0)
& " -- " &
Format(DateValue(DateAdd("d",7-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0))
AS Weeks, Format$([TxDocDateRcvd],"yyww") AS Expr1,
Format$([TxDocDateRcvd],"ww") AS expr2
FROM tblTaxDoc
GROUP BY Format$([TxDocDateRcvd],"ww"), IIf(Not
IsNull([TxDocDateRcvd]),Format(DateValue(DateAdd("d",1-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0)
& " -- " &
Format(DateValue(DateAdd("d",7-Weekday([TxDocDateRcvd]),[TxDocDateRcvd])),"mm/dd/yyyy",0,0)),
Format$([TxDocDateRcvd],"yyww"), Format$([TxDocDateRcvd],"ww")
ORDER BY Format$([TxDocDateRcvd],"yyww") DESC;

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

Remove the closing semicolon and add:
UNION SELECT "(All)" as [CompanyName], -1 As [City]
FROM [Customers]
ORDER BY [CompanyName];

Also: Remove the ALL from UNION ALL unless you want duplicate
CompanyNames
and Cities in your combo box.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

SELECT [CompanyName], [City]
FROM [Suppliers]
UNION ALL SELECT [CompanyName], [City]
FROM [Customers];

How do i add to the above
'[All]', -1
so that my combobox can have [all] if I want to select all the the
records?