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

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

"Marshall Barton" wrote:

Alastair MacFarlane wrote:
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.

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: trouble with SQL syntax
    ... Dim mySQL As String ... and created your query in the query designer? ... designer, save it in the MDB, and then just open the query from VB by ... name instead of using an SQL statement. ...
    (microsoft.public.vb.general.discussion)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)