As a start, I'd comment that "date" should not be used as a Field Name
because it is an Access reserved word and can lead to confusion, and that
"05/02" is not a date, in any case, but a code for expressing (presumably)
month and year, or perhaps year and month (not obvious from your data,
though your description of the coimparison, later, leads me to suspect it is
month/year).
The 'date' field should be renamed to something unique like "FolderDate" --
it can either can be handled in the query to compare the year and month, or
the file can be processed with an update query to convert it to a date.
And, also, I am not sure when you use <02/2001, whether you mean "newer than
February 2001" or "older than February 2001."
Finally, numbers used for identification, such as your Folder ID and Rack,
are often stored as alpha characters... it's not unusual, in a list of
"racks" or such, to find a few later additions like "158A" and "158B" or
"U666". In this example, that shouldn't make a difference, because no
criteria is applied to either of those Fields.
You also need to clarify what you want to happen if there is a folder
specifying a rack number that is not in the 'racks' table. That will
influence the type of join you use in the Query. My suggestion would be to
show it without a department in a specific query used for exception
reporting, because it probably indicates either a mistake in data entry, or
a rack that has not yet been added to the 'racks' table.
Your use of lowercase for table and fieldnames leads me to think that you
are new to Access, and possibly not familiar with the Query Builder. If you
are writing Access SQL without taking advantage of the Query Builder, you
are doing yourself a disservice.
Once you clarify the points above, the solution to your problem should not
be difficult -- an expression to construct an actual date from the "date"
field, and a Query, relatively easy to create in the Query Builder (or in
SQL for that matter).
Larry Linson
Microsoft Access MVP
"Thorben Grosser" <th*************@gmail.comwrote in message
news:11**********************@w3g2000hsg.googlegro ups.com...
Hello Newsgroup,
I am doing some archive database and therefore got one table indexing
every folder and one table storing which rack belongs to which
department, eg:
table folders :
+-----------+------+-------+
| folder_id | rack | date |
+===========+======+=======+
| 123456789 | 325 | 05/02 |
+-----------+------+-------+
| 987654321 | 158 | 02/07 |
+-----------+------+-------+
| 987485221 | 666 | 01/05 |
+-----------+------+-------+
table racks:
+----+------+------------+
| id | rack | department |
+====+======+============+
| 1 | 158 | FKA/PKG |
+----+------+------------+
| 2 | 555 | KOV/GDA |
+----+------+------------+
| 3 | 666 | FKA/PKG |
+----+------+------------+
| 4 | 123 | ORG/RET |
+----+------+------------+
Now I have to select all the folders which have for example an equal
date belonging to a certain department, meaning:
- I have to find the racks belonging to eg "FKA/PGK" (158, 666)
- Then I'll got to find every folder belonging to the racks 666 and
158 (987485221, 987654321)
- Then I've got to find those which belong to a certain condition
(eg.
date <02/2001)
Sure I could do that in three queries, but somehow I've got the
feeling that SQL is powerfull enough to do this in one query.
But how?
thanks in advance
Thorben Grosser
