This should be simple but I don't know how!

[Guest]

Dear All

I have to tables with the schema below (see end of e-mail). I would like to
create a query that has an output like the following:

ID SaleDate Period
1 05/04/2006 1
2 05/05/2006 2
3 08/06/2006 3

What I am not sure about is how to calculate this without either code or a
DLookup. This would be very slow when I am looking at many millions of
records. I have a known date value in table 1 and I want to return the period
for that particular date in table 2 when I only have a date range as a guide.

Thanks again for your help.

Alastair MacFarlane


Table 1

ID SaleDate
1 05/04/2006
2 05/05/2006
3 08/06/2006

Table 2

ID Year Period StartDate EndDate
27 2006/07 1 01/04/2006 28/04/2006
28 2006/07 2 29/04/2006 26/05/2006
29 2006/07 3 27/05/2006 23/06/2006

 

[Marshall Barton]

I have to tables with the schema below (see end of e-mail). I would like to
create a query that has an output like the following:

ID SaleDate Period
1 05/04/2006 1
2 05/05/2006 2
3 08/06/2006 3

What I am not sure about is how to calculate this without either code or a
DLookup. This would be very slow when I am looking at many millions of
records. I have a known date value in table 1 and I want to return the period
for that particular date in table 2 when I only have a date range as a guide.

Thanks again for your help.

Alastair MacFarlane


Table 1

ID SaleDate
1 05/04/2006
2 05/05/2006
3 08/06/2006

Table 2

ID Year Period StartDate EndDate
27 2006/07 1 01/04/2006 28/04/2006
28 2006/07 2 29/04/2006 26/05/2006
29 2006/07 3 27/05/2006 23/06/2006

I think this can do that:

SELECT table1.ID, table1.SaleDate, table2.Period
FROM table1 INNER JOIN table2
ON table1.SaleDate >= table2.StartDate
And table1.SaleDate <= table2.EndDate

Note that you can not specify this kind of join in the query
design window, so you will have to use the query's SQL view.

 

[John Spencer]

One method would be to use a query that looks like the following.

SELECT Table1.Id,
Table1.SaleDate, Table2.Period
FROM Table1 INNER JOIN Table2
ON (Table1.SaleDate >= Table2.StartDate and Table1.SaleDate <=
Table2.EndDate)

You cannot constuct that type of join using the query grid, but must do so
in the SQL view.

You can start in the query design view (the grid)
-- Add both tables
-- Select the field you want to display
-- Join SaleDate to StartDate
-- Switch to SQL view (View: SQL on the menu)
-- Find the text where Table1 is joined to Table2
-- Change the On ... to read similar to the above On

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

 

[Guest]

Thanks again Marshall.

I have altered the query above to suit my situation and it works perfectly.
I never knew that there were SQL queries (other than Union) that you could
type in and not see the result in the designer. I quess SQL Server View
designer is much better!

Thanks again.

Alastair

 

[Guest]

John,

I appreciate the time you took to reply to my email. Is this inability to
see the design the same for all versions of Access? I currently use Access
2003. Why do you think that MS would build this into SQL and not Access?

Thansk again.

Alastair

 

[Marshall Barton]

I have altered the query above to suit my situation and it works perfectly.
I never knew that there were SQL queries (other than Union) that you could
type in and not see the result in the designer. I quess SQL Server View
designer is much better!

The QBE is just a "convenient" UI for entering/selecting the
information required for Access to create an SQL statement.
If you deviate (UNION, non-equi joins, complex subqueries,
....) from the simple SQL statements that Access can create,
then you have to create the SQL yourself. As you gain
experience with SQL, you will probably prefer to work in SQL
view for any but the really simple queries.

 

[John W. Vinson]

I appreciate the time you took to reply to my email. Is this inability to
see the design the same for all versions of Access? I currently use Access
2003. Why do you think that MS would build this into SQL and not Access?

The language in which Access (and SQL/Server, and Oracle, and MySQL, and DB2,
and all modern database programs) stores queries *IS* SQL. The query grid is
not the query; it's just a (somewhat limited) tool which lets you create SQL
strings. The real query - even in Access - is the SQL string.

John W. Vinson [MVP]