Problems writing a linked query in SQL

[Thorben Grosser]

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

[John Winterbottom]

select r.rack, f.folder_id
from folders as f
inner join racks as r on f.rack = r.rack
where r.department = 'FKA/PGK'
and f.[date] < #02/01/2001#

Thorben Grosser wrote:

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

[Larry Linson]

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

[Thorben Grosser]

Hy Larry,

many thanks first for yourvery helpfull answer

On 31 Jul., 00:20, "Larry Linson" <[e-mail]:

And, also, I am not sure when you use <02/2001, whether you mean "newer than
February 2001" or "older than February 2001."

I'd like to apolgize for the confusion caused by my post. Both tables
are but examples and don't show the final design nor the names of the
tables, its just for the general understanding. Speaking about the
older/newer confusion, I acutally had a similar problem when comparing
dates as I didn't know how Access (and VBA) sees things. Trial and
error helped.

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.

I totally agree with you. I think that should be fine.

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.

I am quite amazed by the fact that one can recon one others Access
skills by checking the upper/lower case writing.
Indeed, I never considered the query builder as something usual (I am
quite used to PHP and MySQL where you usually dont have that luxury),
but it seems as I should take a deeper look,

thanks a lot,
and have a nice day
Thorben Grosser