Retrieve date from different tables

[Maran]

I want to retrieve data from different tables in a single query based on a
input given by user.

Example. Product A sold for different price in different markets at
different dates.

Tables - Market 1, Market 2, Market 3
Fields - Product Name, Date, Price

I want to retrieve the price in all markets in a single query. Condition -
The latest price, based on the date, given by the user in the input box.

note: Dates will not be the same in all markets.

It is not possible to have all markets in one table, as the fields may
differ in my actual database. It is only a example.

thanks
maran

 

[Jeanette Cunningham]

Hi Maran,
I can see the names of your tables from your post. Would you post back with
the following:
For each table say its Primary Key and Foreign Key (for all tables with
foreign keys)
Explain the relationships between the tables - the one-to-many or the
many-to-many bits.
The above will help us to help you with this query.
Jeanette Cunningham

 

[John Spencer]

The only way I see to handle this would be to have multiple queries that you
then combine into one using a UNION query.

To get the latest Date for each product in each of the tables - you would
need a query like

Parameters [Enter Cutoff Date] DateTime;
SELECT "Market1" as TheSource, [Product Name], Market1.Date, Market1.Price
FROM Market1
WHERE Market1.Date =
(SELECT Max([Tmp.Date]
FROM Market1 as Tmp
WHERE Tmp.[Product Name] = Market1.[Product Name]
AND Tmp.[Date] <= [Enter Cutoff Date])
UNION ALL
SELECT "Market2" as TheSource, [Product Name], Market1.Date, Market1.Price
FROM Market2
WHERE Market2.Date =
(SELECT Max([Tmp.Date]
FROM Market2 as Tmp
WHERE Tmp.[Product Name] = Market2.[Product Name]
AND Tmp.[Date] <= [Enter Cutoff Date])
UNION ALL
SELECT "Market3" as TheSource, [Product Name], Market3.Date, Market1.Price
FROM Market3
WHERE Market3.Date =
(SELECT Max([Tmp.Date]
FROM Market3 as Tmp
WHERE Tmp.[Product Name] = Market3.[Product Name]
AND Tmp.[Date] <= [Enter Cutoff Date])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..