473,725 Members | 2,238 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problems writing a linked query in SQL

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

Jul 30 '07 #1
3 2629
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
Jul 30 '07 #2
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.comwrot e in message
news:11******** **************@ w3g2000hsg.goog legroups.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


Jul 30 '07 #3
Hy Larry,

many thanks first for yourvery helpfull answer

On 31 Jul., 00:20, "Larry Linson" <boun...@localh ost.notwrote:
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

Jul 31 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
15189
by: James Allan | last post by:
Hello -- I'm trying to get SQL Server 2000 on a Windows 2000 Server to be able to query an Active Directory. We've got two domain servers one Win2000 and one Win2003. However, I'm having problems: I've run the following query to setup the linked server: sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
1
8560
by: A.M. de Jong | last post by:
When I perform a query on a linked Oracle server in the Query analyser I have no prboblem' to perform this query. However, when I create this query in a stored procedure I get a compilation error when saving this procedure. (Not when compiling; it has no errors) Server: Msg 7405, Level 16, State 1, Line 1 Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query...
9
4513
by: JMCN | last post by:
hi- i have inherited an access 97 database that keeps track of the loans. i have been running into referential intergrity problems when i try to append new loans to table. first of all is a linked table from another ms database. when i try to run the append query, i receive an error message that it cannot append all the records due to key violations to the . so i looked at all of the relationships and
2
6108
by: neptune | last post by:
I currently link a table to an Oracle db. Anytime I open a query based off it, I have to input username & password. Is there a way to do this automatically? I'd prefer to use linked tables, but I've read a pass-through query can pass the passwords automatically. The directions seem straightforward, but I can't get one to work. The select query from the ODBC linked table looks like: SELECT OSCAR_TBLSALESMAN.SALESMAN,...
7
11832
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For tables that involve a number field as the primary key, the data is returned successfully. For tables that involve a character field (e.g. CHAR(3) or VARCHAR(10)) as the primary key, I have the correct number of rows returned, but the data displayed...
3
4895
by: Zlatko Matić | last post by:
Hi! What happens with linked tables if they were linked using File DSN, when I copy the Access file on some other PC without File DSN ? What is the difference between DSN on linked tables and pass-through queries ? Why I can change DSN for pass-through query and can't change it for linked table, in Properties dialog box ? Zlatko
15
2566
by: Ian Bush | last post by:
Hi All, I'm a bit confused by the following which is causing one of our user's codes fail in compilation: typedef struct SctpDest_S; 1) Is this standard ? 2) If so ( or even if not so ! ) what is it supposed to do ?
24
2418
by: tanmay | last post by:
please tell me how to get the following output using loops... 1) * * * * * * * * * * * * * * * *
11
5573
by: jjkeeper | last post by:
Hi, I need to create an annual leave Database for the company. I admit I was a complete novice at this. But I got thrown the job and I have to do it. So here's what I get so far, but I got pretty much stuck for some days figuring out what to do. Sorry I can't think of a better title. I have a tblMainProfile table, which stores everything about employee's particulars. ID, name, NIRC, etc etc. Next, I have tblLeaveEntitlement table, which...
0
8882
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8749
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9398
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9250
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9165
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9098
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8080
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
3216
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2153
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.