I'm working on a simple database that has 2 tables one called ITEMS, one called USERS. I'm tracking items being transfered between 2 users. in the ITEMS table I reference the sending user and receiving user by a numeric field ITEMUSERID and SENDTO that both refer to the USERID in the USERS table. I want to pull the description of both of these users in my result. I am able to do this using a stored query and then querying that query. I want to be able to do it in one step.
Here's what works..
Stored Query: ITEMS_LOCATION
(This pulls all the items and the "where they're coming from" description...)
SELECT USERS.DESCRIPTION, USERS.USERID, ITEMS.INFO, ITEMS.ITEMUSERID, ITEMS.SENDTO
FROM ITEMS INNER JOIN USERS ON ITEMS.ITEMUSERID = USERS.USERID;
(Then I execute the following query to get the "where the item is going" description in to the table)
SELECT ITEMS_LOCATIONS.DESCRIPTION, ITEMS_LOCATIONS.USERID, ITEMS_LOCATIONS.INFO, ITEMS_LOCATIONS.ITEMUSERID, ITEMS_LOCATIONS.SENDTO, USERS.DESCRIPTION
FROM ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS.SENDTO = USERS.USERID;
How can I acomplish this as one query?
4 1307
1.
in the ITEMS table I reference the sending user and receiving user by a numeric field ITEMUSERID and SENDTO that both refer to the USERID in the USERS table
According to statement above table ITEMS has both columns ITEMUSERID and SENDTO.
2.
SELECT ITEMS_LOCATIONS.DESCRIPTION, ITEMS_LOCATIONS.USERID, ITEMS_LOCATIONS.INFO, ITEMS_LOCATIONS.ITEMUSERID, ITEMS_LOCATIONS.SENDTO, USERS.DESCRIPTION
FROM ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS.SENDTO = USERS.USERID;
According to query provided it looks like SENDTO is in a different table ITEMS_LOCATIONS.
Which one is correct?
ITEMS_LOCATIONS is the name of the stored query...
Try this:
[PHP]SELECT U1.DESCRIPTION, U1.USERID, ITEMS.INFO, ITEMS.ITEMUSERID, ITEMS.SENDTO,
U2.DESCRIPTION
FROM ITEMS INNER JOIN USERS U1 ON ITEMS.ITEMUSERID = USERS.USERID
INNER JOIN USERS U2 ON ITEMS.SENDTO = USERS.USERID[/PHP]
Good Luck
Got me pointed in the right direction.. This works..
SELECT ITEMS_LOCATIONS.DESCRIPTION, ITEMS_LOCATIONS.USERID, ITEMS_LOCATIONS.INFO, ITEMS_LOCATIONS.ITEMUSERID, ITEMS_LOCATIONS.SENDTO, USERS.DESCRIPTION
FROM [SELECT USERS.DESCRIPTION, USERS.USERID, ITEMS.INFO, ITEMS.ITEMUSERID, ITEMS.SENDTO
FROM ITEMS INNER JOIN USERS ON ITEMS.ITEMUSERID = USERS.USERID]. AS ITEMS_LOCATIONS LEFT JOIN USERS ON ITEMS_LOCATIONS.SENDTO = USERS.USERID;
Thanks!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Greg |
last post by:
I am working on a project that will have about 500,000 records in an XML
document. This document will need to be queried with XPath, and records
will need to be updated. I was thinking about...
|
by: Shane |
last post by:
I wonder if someone has any ideas about the following.
I am currently producing some reports for a manufacturing company who work
with metal.
A finished part can contain multiple sub-parts to...
|
by: roiavidan |
last post by:
Hi,
I'm having a bit of a problem with a small application I wrote in C#,
which uses an Access database (mdb file) for storing financial data.
After looking for a similiar topic and failing to...
|
by: MDB |
last post by:
I'd normally Google for a question like this, and hope to snag a few
examples along with the answer, but this time I can't see to get the
keywords specific enough.
Or I'd ask coworkers, but...
|
by: paulb |
last post by:
I was wondering if it is possible to continuously query a real-time
datastream using SQL Server. Does anyone have any experience of this? I
have found LINUX based systems such as Borealis and...
|
by: sql_er |
last post by:
Guys,
I have an XML file which is 233MB in size. It was created by loading 6
tables from an sql server database into a dataset object and then
writing out the contents from this dataset into an...
|
by: RJN |
last post by:
Hi
I've written a code that queries Windows LDAP server and works fine, but
the same doesn't work when querying Solaris LDAP server.
DirectoryEntry de = new...
|
by: RajSharma |
last post by:
Hi,
I am facing a problem regarding querying thru a large table having millions of rows.......
Its hanging in between while querying for all those rows
Can anybody suggest me a query regarding :...
|
by: iheartvba |
last post by:
Hi,
I usually use access query builder to create complex queries. Especially to create Sub Queries I.E. a querying another query. In my current situation I need to provide the criteria to the query...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |