Query date from tbl 1 with date range in tbl2

[zdog]

Hi Guys, I was wondering if there is any way to solve this problem.
I have two tables in my db.
tbl1 has fields name, qty, date
tbl2 has fields start date, end date, classification

eg
tbl1
xxx 2 12/03/03
yyy 4 01/04/04

tbl2
11/29/03 12/31/03 j11
01/01/04 02/02/04 f01

What I need to do is design a query that returns
name, qty, classification.
resulting query should look like

xxx 2 j11
yyy 4 f01

the problem I'm having is getting the classification field. what I need is some sort of statement that compares the date field in tbl1 with the start and end dates in tbl2 and selects the corresponding classification.
something like if ([date] between [start date] and [end date], [classification], "No Match")
Any help will be much appreciated.
Thanks a lot

 

[zdog]

Thanks for the suggestion John. I shall try it when i get back to work tomorrow. It looks like a real no brainer, i guess i was just brain dead. Thanks again.

 

[john471]

Take Two.

For those who are wondering, I offered ZDog a solution, but then deleted it, as I realised I hadn't catered for his no-match requirement.... Here is a solution which does cater for the No Match requirement....

I offer first of all the following suggestions:-

Best not to use "name" or "date" as field names (I have modified these in the below to "TheName" and "TheDate"). You might use something more meaningful like "ProductName" and "PurchaseDate". Best also not to use embedded spaces in field (or object; e.g. table, query etc) names. I have modified "end date" and "start date" to "EndDate" and "StartDate", respectively.

Now, that said...

To get your full result set (including no-matches) you will have to build it in three parts .

Part 1:
Amend/save the following SQL with a name of qryIncluded - gives results that do generate a matched classification:-

SELECT tbl1.TheName, tbl1.qty, tbl1.TheDate, tbl2.Classification
FROM tbl1, tbl2
WHERE (((tbl2.StartDate)<=[tbl1].[TheDate]) AND ((tbl2.EndDate)>=[tbl1].[TheDate]));

Part 2:
Amend /save the following SQL with a name of qryExcluded - gives the results from tbl1 that do not have a matched classification

SELECT DISTINCTROW tbl1.TheName, tbl1.qty, tbl1.TheDate, "No Match" AS Classification
FROM tbl1 LEFT JOIN qryIncluded ON (tbl1.TheDate = qryIncluded.TheDate) AND (tbl1.qty = qryIncluded.qty) AND (tbl1.TheName = qryIncluded.TheName)
WHERE (((qryIncluded.TheName) Is Null) AND ((qryIncluded.qty) Is Null) AND ((qryIncluded.TheDate) Is Null));

Part 3:
Join the two result sets together with a union query (only displaying the required fields).

SELECT qryIncluded.TheName, qryIncluded.qty, qryIncluded.Classification
FROM qryIncluded

UNION ALL SELECT qryExcluded.TheName, qryExcluded.qty, qryExcluded.Classification
FROM qryExcluded;

(I have assumed that tbl1 has a composite primary key made up of the three stated fields. The join expression in part2 could be simplified if, for example, tbl1 had an AutoNumber ID field as a primary key.)

HTH

Regards

John

 

[zdog]

Thanks once again John. Your solution worked like a charm. I had another question that maybe you could provide me some input.

I have a table where the first row contains dates, the remaining rows in the table have sales volumes. What I would like to do is group the sales by particular periods (weeks, months, years etc). Is there a way this can be done given the current table structure.
Maybe if I could transpose the data, i.e. have all the dates in the first row appear in just one column then I'm sure this would be pretty easy.
Using either approach is fine. Any ideas or suggestions as to what might be the easiest way to accomplish this.
ZDog

 

[john471]

Thanks once again John. Your solution worked like a charm.

You are welcome. I'm Glad it helped.

I had another question that maybe you could provide me some input.

I have a table where the first row contains dates, the remaining rows in the table have sales volumes.

Refer Pat Hartman's post, above. Do you really have a "table" with such a structure, or is it a spreadsheet ?

If you are able to provide a little more detail, someone may be able to help.

Regards

John.

 

[zdog]

Hi Guys, I agree and understand your question, a field can only contain one single data type. In the case of the table that I'm dealing with the fields actually are text fields although most of the data consists of numbers. I'm pretty sure the data wasn't meant to be used in a db table however thats what I've been asked to work with, not my choice

Here's what the table looks like

the first field has part numbers, the first record is dates, all subsequent records are sales qty's

1/3/05 1/4/05 1/5/05 .... 2/1/05 3/1/05
a1 10 26 25 300 356

I guess a better method would have been if the first record had been the field names then all the fields except for the first one could have been "numbers"

The big issue with the current format is that since the field type is text even if i figured out a way of grouping the fields, the data type for the fields would have to be numbers in order to sum the qty's.

Now that I think about this some more I'm not really sure if this is feasible in access. Come to think of it, it would be nice if there was an application that had the spreadsheet features like excel and the query features like access. Looks like I've hit a brick wall with this one. Let me know if you guys see something that I'm missing.
Thanks for being so patient and trying to help me out.
ZDog

 

[zdog]

Pat, I exported the table into excel and ran some code to group the data the way I needed it. It all worked out. Thanks for the help though.
ZDog