Using Northwind, the CrossTab SQL below is more what I'm dealing with:
TRANSFORM First(Orders.Freight) AS FirstOfFreight
SELECT [Order Details].OrderID, [Order Details].ProductID
FROM Customers INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID =
Orders.CustomerID
WHERE (((Customers.CustomerID)="DRACD" Or (Customers.CustomerID)="FOLIG" Or
(Customers.CustomerID)="FURIB"))
GROUP BY [Order Details].OrderID, [Order Details].ProductID
PIVOT Customers.CustomerID;
--
Thanks, Kevin
:
Try use this solution in Northwind to see how it works. Create a new query in
Northwind and set its SQL to:
SELECT Customers.*
FROM Customers
WHERE (((InStr([Enter Customer IDs],[CustomerID]))>0));
View the datasheet of the query and enter
DRACD FOLIG FURIB
This should return the three records with those customer IDs. How is your
query different from this query?
--
Duane Hookom
Microsoft Access MVP
:
Something in your suggested where doesn't work:
WHERE Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0
This where returns 0 records.
If I replace it with:
WHERE (((StoreMaster.storename)="H2345"))
I'm able to get the records for H2345. This indicates the rest of the query
is working, just not your suggested where above.
Again, "StoreName" is a text field for store ID's. 12345, H2345, M6789
--
Thanks, Kevin
:
I would expect the StoreID to contain the values like 1234, H0123,... and the
StoreName to be something like: Best Buy, OfficeMax, Sharper Image,....
I think I just noticed my error...
PARAMETERS [ENTER STORE NUMBERS] Text ( 255 );
TRANSFORM First([PriceTable]![baseprice]/[PriceTable]![basepricemult]) AS
[BASE-RTL]
SELECT ItemMaster.itemnbr AS ITMNbr, ItemMaster.description AS [DESC]
FROM (PriceTable LEFT JOIN StoreMaster ON PriceTable.storeid =
StoreMaster.storeid) LEFT JOIN ItemMaster ON PriceTable.itemid =
ItemMaster.itemid
WHERE Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;
My expectation would be that you could enter a value like
1234,H0123
to compare these to stores.
--
Duane Hookom
Microsoft Access MVP
:
Data stored in [StoreName] field is a text ID
Indexed, Text, A8 (12345, H0123, M1234, etc)
When your suggested SQL is ran, I get a “ENTER STORE NUMBERS†prompt, I
enter 12345 (store ID), 0 records found. (does not prompt again for 2nd
store ID)
Run again, at prompt I leave blank, 0 records found.
The same data in a select query with the following parameter prompt works
fine.
[ENTER STORE NUMBER] Or Like [ENTER STORE NUMBER] Is Null
I hope this answers your questions.
--
Thanks, Kevin
:
Can you take the time to:
1) explain what you mean by "it's still not working". What happens? What
displays? What errors do you get?
2) Describe the data stored in StoreName. We still don't know if these are
numbers or text or short abbreviations or what.
--
Duane Hookom
Microsoft Access MVP
:
I cut & pasted your suggested SQL & it's still not working.
Access is changing the SQL to:
WHERE ((([StoreMaster].[storename]=InStr(Nz([ENTER STORE
NUMBERS],[StoreName]),[StoreName]))>0))
& you had:
WHERE StoreMaster.storename=Instr(Nz([ENTER STORE NUMBERS], StoreName),
StoreName)>0
--
Thanks, Kevin
:
Try something like this where the user can enter 1, 2, 3,... store numbers in
the same prompt. The success of this depends on your store number values.
Your expression might need to be modified to accomodate your data values.
PARAMETERS [ENTER STORE NUMBERS] Text ( 255 );
TRANSFORM First([PriceTable]![baseprice]/[PriceTable]![basepricemult]) AS
[BASE-RTL]
SELECT ItemMaster.itemnbr AS ITMNbr, ItemMaster.description AS [DESC]
FROM (PriceTable LEFT JOIN StoreMaster ON PriceTable.storeid =
StoreMaster.storeid) LEFT JOIN ItemMaster ON PriceTable.itemid =
ItemMaster.itemid
WHERE StoreMaster.storename=Instr(Nz([ENTER STORE NUMBERS], StoreName),
StoreName)>0
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;
--
Duane Hookom
Microsoft Access MVP
:
In a select query, [ENTER STORE NUMBER] Or Like [ENTER STORE NUMBER] Is Null
will work.
The user can click enter, returning all records or
A single StoreNumber, returning one store's recorder or
Enter 1st StoreNumber & 2nd StoreNumber a 2nd prompt, returning records for
2 stores.
What I'm looking for is a parameter in a CrossTab that can do the same so
that 2 store's pricing can be compared side by side for the same item record.
Or all or 1.
--
Thanks, Kevin
:
Are you looking for two different stores? Why would you want two different
prompts? Why are you asking for store number and comparing it to the
StoreName field?
Did you try my suggestion? If you enter nothing in the prompt, it should
display all stores.
--
Duane Hookom
Microsoft Access MVP
:
What I'm looking for is a parameter in a CrossTab that can accept an input
from the user, that will return all records if no input is made. Or prompt
again if an imput is made, treating them as separate parameters. The user
would see two input prompt boxes.
[ENTER STORE NUMBER] Or Like [ENTER STORE NUMBER] Is Null
--
Thanks, Kevin
:
PARAMETERS [ENTER STORE NUMBER] Text ( 255 );
TRANSFORM First([PriceTable]![baseprice]/[PriceTable]![basepricemult]) AS
[BASE-RTL]
SELECT ItemMaster.itemnbr AS ITMNbr, ItemMaster.description AS [DESC]
FROM (PriceTable LEFT JOIN StoreMaster ON PriceTable.storeid =
StoreMaster.storeid) LEFT JOIN ItemMaster ON PriceTable.itemid =
ItemMaster.itemid
WHERE StoreMaster.storename=Nz([ENTER STORE NUMBER], StoreMaster.StoreName)
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;
--
Duane Hookom
Microsoft Access MVP
:
How do I pass a parameter with a null response in a CrossTab Query?
I have the following SQL. I am unable to get the PARAMETERS right.
PARAMETERS [ENTER STORE NUMBER] Text ( 255 ), Like [ENTER STORE NUMBER] Is
Null Text ( 255 );
TRANSFORM First([PriceTable]![baseprice]/[PriceTable]![basepricemult]) AS
[BASE-RTL]
SELECT ItemMaster.itemnbr AS ITMNbr, ItemMaster.description AS [DESC]
FROM (PriceTable LEFT JOIN StoreMaster ON PriceTable.storeid =
StoreMaster.storeid) LEFT JOIN ItemMaster ON PriceTable.itemid =
ItemMaster.itemid
WHERE (((StoreMaster.storename)=[ENTER STORE NUMBER])) OR
(((StoreMaster.storename) Like [ENTER STORE NUMBER] Is Null))
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;
