Statistics in Query

[John Foster]

I have 2 tables. one has a product, time started and time ended, the other
table has time and temp data. not sure how to join the tables so that I can
do statistics on the temp date for each product.

 

[KARL DEWEY]

Join on Product.
If you may have products that are not listed in both or all products in one
to use in a left join then pull product from both in a union query and use
the union query left joined to the other two.

 

[John Spencer]

If you mean you are trying to relate records based on the time in the other
table being between the Start time and End time in the first table then you
need a query with SQL that looks like

SELECT P.*, T.*
FROM [TableProduct] as P INNER JOIN [TableTempData] As T
ON T.[Time] <= P.[Time Ended] AND T.[Time]>=P.[Time Started]

I used aliases for the table names so typing the SQL would be easier. This
type of join cannot be made in the Query Design view, but must be constructed
in the SQL view.

If you want to do this in Query Design view
== Add both tables with NO JOIN between them
== Add the fields you want to see
== Under the Time field enter criteria
Between [ProductTable].[Time Started] and [ProductTable].[Time Ended]
(Use your table and field names)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County