Building the required SQL statement

Hello,

I'm having difficulty writing a query and was hoping someone could help me 
out. 

I have a table 'orders' which contains numerous fields, of which 3 are of 
particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also 
have a table 'clients' which has a primary key 'client id'. What I am trying 
to do is return all the data from the 'clients' table for the associated 
clientid1 and clientid2 where the order_status="conf". 

I get lost because of the need to get the info for clientid1 and clientid2. 
If it were a simple Right Join ON I would be ok, but don't know how to handle 
this one. 

Thank you for the help, 

QB
0
Utf
3/14/2008 5:12:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
799 Views

Similar Articles

[PageSpeed] 5

Goes to show the problem of denormalization. Just think of the fun if you add 
a clientid3 field.

There's a couple ways of doing it. Here's one:

SELECT clients.*, orders.clientid1
FROM  clients, orders
WHERE clients.[client id] = orders.clientid1
AND orders.order_status="conf"
UNION 
SELECT clients.*, orders.clientid2
FROM  clients, orders
WHERE clients.[client id] = orders.clientid2
AND orders.order_status="conf" ;

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Question Boy" wrote:

> Hello,
> 
> I'm having difficulty writing a query and was hoping someone could help me 
> out. 
> 
> I have a table 'orders' which contains numerous fields, of which 3 are of 
> particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also 
> have a table 'clients' which has a primary key 'client id'. What I am trying 
> to do is return all the data from the 'clients' table for the associated 
> clientid1 and clientid2 where the order_status="conf". 
> 
> I get lost because of the need to get the info for clientid1 and clientid2. 
> If it were a simple Right Join ON I would be ok, but don't know how to handle 
> this one. 
> 
> Thank you for the help, 
> 
> QB
0
Utf
3/14/2008 6:58:02 PM
Bad design here.

With your current structure you need to include the clients table TWICE in 
your query

SELECT Clients.OrderStatus
, C1.*
, C2.*
FROM (ORDERS LEFT JOIN Clients as C1
ON Orders.ClientID1 = C1.ClientID)
LEFT JOIN Clients as C2
ON Orders.ClientID2 = C2.ClientID
WHERE Orders.Order_Status = "Conf"

OR you may need to use a UNION query if you want things to line up and you 
only want one set of information from the clients table


SELECT Clients.*
FROM Clients INNER JOIN Orders
ON Clients.ClientID = Orders.ClientID1
WHERE Orders.OrderStatus = "Conf"
UNION
SELECT Clients.*
FROM Clients INNER JOIN Orders
ON Clients.ClientID = Orders.ClientID2
WHERE Orders.OrderStatus = "Conf"

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Question Boy" <QuestionBoy@discussions.microsoft.com> wrote in message 
news:0E93A858-38C3-4E30-95EA-FBF5BA3699D2@microsoft.com...
> Hello,
>
> I'm having difficulty writing a query and was hoping someone could help me
> out.
>
> I have a table 'orders' which contains numerous fields, of which 3 are of
> particular interest to me 'order_status', 'clientid1' and 'clientid2'. I 
> also
> have a table 'clients' which has a primary key 'client id'. What I am 
> trying
> to do is return all the data from the 'clients' table for the associated
> clientid1 and clientid2 where the order_status="conf".
>
> I get lost because of the need to get the info for clientid1 and 
> clientid2.
> If it were a simple Right Join ON I would be ok, but don't know how to 
> handle
> this one.
>
> Thank you for the help,
>
> QB 


0
John
3/14/2008 6:58:48 PM
Question Boy wrote:
>I'm having difficulty writing a query and was hoping someone could help me 
>out. 
>
>I have a table 'orders' which contains numerous fields, of which 3 are of 
>particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also 
>have a table 'clients' which has a primary key 'client id'. What I am trying 
>to do is return all the data from the 'clients' table for the associated 
>clientid1 and clientid2 where the order_status="conf". 
>
>I get lost because of the need to get the info for clientid1 and clientid2. 
>If it were a simple Right Join ON I would be ok, but don't know how to handle 
>this one. 


You need to add the clients table to your query twice.  Then
Join clientid1 to Clients and join Clientid2 to Clients_1

-- 
Marsh
MVP [MS Access]
0
Marshall
3/14/2008 8:04:03 PM
To fix the normalization issue I'd need to create a table to house the 
clientid1 and clientid2 with a foreign key orderid?  But how to differentiate 
btw the 2, there is an importance to which is 1 and 2.



"Jerry Whittle" wrote:

> Goes to show the problem of denormalization. Just think of the fun if you add 
> a clientid3 field.
> 
> There's a couple ways of doing it. Here's one:
> 
> SELECT clients.*, orders.clientid1
> FROM  clients, orders
> WHERE clients.[client id] = orders.clientid1
> AND orders.order_status="conf"
> UNION 
> SELECT clients.*, orders.clientid2
> FROM  clients, orders
> WHERE clients.[client id] = orders.clientid2
> AND orders.order_status="conf" ;
> 
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> "Question Boy" wrote:
> 
> > Hello,
> > 
> > I'm having difficulty writing a query and was hoping someone could help me 
> > out. 
> > 
> > I have a table 'orders' which contains numerous fields, of which 3 are of 
> > particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also 
> > have a table 'clients' which has a primary key 'client id'. What I am trying 
> > to do is return all the data from the 'clients' table for the associated 
> > clientid1 and clientid2 where the order_status="conf". 
> > 
> > I get lost because of the need to get the info for clientid1 and clientid2. 
> > If it were a simple Right Join ON I would be ok, but don't know how to handle 
> > this one. 
> > 
> > Thank you for the help, 
> > 
> > QB
0
Utf
3/14/2008 8:38:00 PM
Reply:

Similar Artilces:

IF,Then statement?
Hi All I am trying to find a way to write and if/then statement. I have a column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a column (B) of gas MPG. I want to average each type of gas. So I was thinking of adding a column (C) for each type of gas. If A#=C(header), THEN place B# in C#. I can then take and average the column. I just don't know how to write it? OR is there a simplier way to write it? Thanks Bill -- bhowe ------------------------------------------------------------------------ bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&user...

Can I change the font color of text inside an IF statement?
Here is the equation I'm using in Excel 2003. =IF(AND(G3="NO",B1-E3<2),B1-E3,IF(AND(G3="YES",B1-E3<=96),B1-E3,"Error")) I would like the text color to change red if: IF NO is selected and B1-E3 >2 I would like the text to turn red. IF YES is selected and B1-E3 >4 I would like the text to turn red. If I can do this in conditional formatting, that’s fine, I'm just not familiar enough with excel to do this. Thanks for the assistance! Craig Hi a formula can't change a format. But have a look at 'Format - Conditional Format' fo...

Need help with SQL tables
After running a security reconcile sa was no longer a user in GP. I received the answer below about re-adding it, but I don't know how to add a userid in to a table. I can't find anything on knowledgebase about this. We don't have the SQL Enterprise Manager but use the SQL 2005 Management Studio Express. Can anyone help me? I just need to know the script that will add it to these tables. Thanks. Tracey D "Therefore it will be just a matter of adding 'sa' USERID into the following tables in DYNAMICS database as a minimum. Once added, use ano...

Installation Problems SQL 2005
I keep getting the following Error when installling CRM 3.0 Small Business Edition "Service mssearch was not found on computer 'SERVER1'. The specified service does not exist as an installed service" I have the Microsoft Full-Text Engine for SQL Server Service Installed and running. Can anyone help? Are you runndng your domain on a Windows Small Business Server? -- Rgds Michael MCDBA 2000 | MCITP DBA 2005 "jl815" wrote: > I keep getting the following Error when installling CRM 3.0 Small Business > Edition "Service mssearch was not found on com...

Using Float Type Variables for Primary on SQL 2005
We seem to have hit an oddity. We have a stored procedure that gets the next sequence number. It is protected by a transaction so no two processes could update the table of ids at the same time. Every so often, we get duplicates. How could that happen? Is the value based on mantissa? If so, is there a way to expanded it? Keep in mind, I can't change the type from float. Any help is greatly appreciated. Thanks in advance On 2010-07-19 16:19, RG wrote: > We seem to have hit an oddity. > Please don't multi-post. If you have to post to multiple newsgroup...

CPU spikes on sql server
We have noticed most of the same performance issues that are reported around here, but in trying to research a few of these I noticed the following strange behavior. Every 10 minutes a sql agent runs a process for "stored procedure priming". When this runs, the cpu on the server pegs at 100% for about 30 seconds. then back to 0% or so until the next firing This is a almost brand new install and only 1 or 2 users hit it. We have only begun to put data into it, so there isn't very much. Seems odd to me that a frequently scheduled process would hit things that hard that ...

Conditional Formating (If statements)
In the conditional formatting box how would I make B4 turn yellow if F12 is between 10 and 15? Right-click Sheet, click 'View Code', and paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim rnArea As Range Dim rnCell As Range With rnCell If Range("F12").Value >= 10 And Range("F12").Value <= 15 Then Range("B4").Interior.ColorIndex = 6 Else: Range("B4").Interior.ColorIndex = 0 End If End With End Sub -- Ryan--- If this in...

Building an Embedded Device/System? Tell VDC about your experience for a chance to WIN!
VDC is conducting its annual survey of mobile and embedded engineers so if you are involved in the engineering of mobile or embedded systems/software, this is your chance to influence key solution suppliers. The research covers embedded software, hardware, tools, and development practices. Your thoughts will improve our insights into the engineering community and help to influence next generation solutions. In addition, VDC will provide all respondents who complete the survey: * Instant access to a summary of VDC's 2009 survey findings; * Entry into a prize drawing for one...

Change IP address in Excel Sheet for SQL sever
We have change the IP address of our SQL Sever from 192.168.0.xxx to 192.168.0.4. This results in big problems as all our Excel Sheets with Pivots and SQL Queries in the have stopped working. Cause is that instead of using name we have used IP address in our ODBC set-up so the Excel file has the IP insted of the name of the SQL server. Now, one way to solve this is to save every sheet as XML and then change the adress, then import it and save it as an xls file again - losing graphs etc. Is there anyone that have an alternate solution to this problem and I will be eternaly greatful -...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

Broken statement import
Hi, Rather foolishly, it now turns out, I downloaded and installed Microsoft Office Accounting Express 2008 for evaluation purposes. Now, Money 2005 (14.0.120.1105) running on 64-bit Vista Business no longer downloads and imports bank statements automatically. Previously, I would do the following: 1) Load Money (obviously) and open my bank .mny file 2) Click Favourites, Favourite Web Sites, Your Banks, Barclays Bank plc, Web Statements Logon Page 3) Enter login credentials Then I would download the latest transactions, and Money would import them into my accounts directly. Now, it n...

Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual Studio 2008 using C#. It receives a SqlString as a parameter and return how many times a specific character was found. I deployed the procedure to a SQL Server database and can see it listed under the database. One caveat is that it has a small lock icon on it. I have a select query where I want to use the stored procedure. I am thinking this is doable in a select query but can't seem to get it to work. What I want to do is to have a select statement listing some fields and one of the fields being th...

Installing Problem SQL SP3a
Hello NG, If got a new W2K3-Server with installed AD and SQL2000 with SP3a. The machine works properly. On installing CRM1.2 the following error occurs: 'The specified SQL-Server must be running Microsoft SQL-Server version 2000 SP3 or later.' Please help me. Thanks, Andreas. ...

VERY URGENT HELP REQUIRED
Hi TO ALL Tech and MSVP: I use to assign item to a site. But today while trying to assign any item to a site, it does not work. I went through Cards>> Inventory >> Item >> Item Qty and Site go to button >> Selected the site >> Save. also i tryed Cards>> Inventory >> site > assign by site id and range of item silent no error when go back to the item realize that it didnt save/assign to the site. There is no error message , also these items does not added into IV00102 table also it exist only one with " " Location code where it sho...

Simple Format for Customer Statement
Hello All Is there available another format for printing customer statements? I'm mostly interested in a format that shows charges and payments without all the details and allocation to invoices. In other words I need a statement is the old fashion simple (Debit, Credit, Balance) format. Thank you Thanks Morris, I'll try it out. "Morris Paint" wrote: > This is our current statement. We are having another one written that shows > current period transactions in one area and all open invoices in another. > > "Mario" <Mario@discussions.microsoft...

SQL Command match Items to Matrix
SQL command that will put all items which contain a certain Supplier ID # to be added to a matrix that I have created. We have a company and we want to do a matrix for all of there items. But instead of manually adding each item to the matrix. If I could run an SQL command that will take all items with the supplier ID attached to them and have them added to the matrix. I am aware that I will still have to label the dimensions. It would just be fantastic if I could do a mass add. Hopefully someone has a creative mind. I tried some commands, but I am not an SQL master either. I know that ...

Upsize AutoNumber field to SQL Server
The Upsizing Wizard Add-In in Access 97 would automatically create an Insert Trigger when upsizing to SQL Server. But when I use the built-in Upsizing Wizard in Access XP (2002), it doesn't seem to create those triggers. Is there an option I'm missing somewhere or will I have to manually create those triggers for my upsized DB? TIA! ...

DPM Configuration with SQL Replicated Servers
Is there any documentation about backup and restoring SQL servers that are replicated? TIA Irvin ...

MS SQL 2005 Express Edition for Offline CRM for Outlook
We have a project that calls for MS SQL 2005 Express Edition on some of the same client machines as those that will eventually be CRM 3.0 clients. Do you know if there is any co-existence/compatibility issues involved in running MSDE and MS SQL 2005 Express Edition on the same machine or whether doing so would adversely affect CRM functionality? Or could we use MS SQL 2005 Express Edition for the DB with the offline version of CRM using Outlook? Thx... ...

multiple AND statements
I need to test a cell with about 20 different conditions like so: IF (A1 < 5)(A1 >= 5 and < 10) ....etc. What is the proper syntax for this? Would I be better off writing a function? I read that a VBA function is MUCH slower but that is not really a problem. Gale Gorman Houston , try =if(and(a1>5,a1<=10,1,2) "Gale Gorman" <ggorman@houston.rr.com> wrote in message news:BHXmb.10024$xv4.1395@twister.austin.rr.com... > I need to test a cell with about 20 different conditions > like so: > > IF (A1 < 5)(A1 >= 5 and < 10) ....etc. >...

SQL Ethnic Name Matching
I have a table of surnames and ethnicity. I want to choose one ethnicity and the number of characters going backwards from the end that should be matched to approximate ethnicity. This will be used to extract names of one ethnicity form another table. I'll be using MS Access initially, so I assume their character matching routines (those in the help file look like the ones in GW Basic) will do. (Dunno if they exist on other SQL.) Might someone suggest the SQL code for this op? Are LIKE and SIMILAR universal in SQL? I have a mismash of experiences with broad gaps. I used IBM DB2 in...

Is it possible to run an Oracle PL\Sql procedure within Excel?
Is it possible to run an Oracle PL\Sql procedure within Excel? Yes, but you would need to use ADO to setup the commandtext to issue to the database, and process the results. Do a Googl on 'ADO Oracle Stored Procedure'. Top result is http://support.microsoft.com/kb/176086 HTH Bob "mowee" <mowee@discussions.microsoft.com> wrote in message news:8238C7BE-5E65-429C-8BA6-E9588C6B7177@microsoft.com... > Is it possible to run an Oracle PL\Sql procedure within Excel? ...

Run macro from if statement
Is there a way to run a macro from the results of an if statement? i.e. if(cell a2=1,"run macro",dont run Any ideas? What I am trying to do ultimately is show a userform if the contents of a cell are changed Thanks I can't find a way to run a macro, but where is your userform that you want to show? It is possible to call another worksheet or area in the formula. If your userform isn't on an Excel worksheet you could paste it into one. >-----Original Message----- >Is there a way to run a macro from the results of an if statement? i.e. if(cell a2=1,"run macro&q...

Users requiring multiple mailboxes
We are currently running NT server 4.0 and Exchange 5.5. I have joined this orginization just recently and the method they have used to create Calendars for things like resources and rooms has been to create a new mailbox and then assign staff permission to the mailboxes This means that nearly everyone has at least 5 or 6 mailboxes associated to there Outlook profile. On top of this they do the same for each new "group" e-mail they want created i.e. info@abc.com, request@abc.com etc. I believe this is so they can send on behalf of I believe in using Public Folders which they barely...

problems with SQL.REQUEST
I am trying to use =SQL.REQUEST in an Excel spreadsheet http://office.microsoft.com/en-us/assistance/HP052092671033.aspx http://www.microsoft.com/downloads/details.aspx?FamilyID=57E79367-13A0-4895-9942-5B177846AB8A&displaylang=EN I have installed the xlodbc.xla add-in under Excel 2002 and 2003 and I still get #NAME? in the cell with the SQL.REQUEST This document talks about the xlodbc.xla not available for 2003 but does not say if an add-in is needed or not to get SQL.REQUEST http://support.microsoft.com/kb/288118/EN-US/ I was able to get the 2002 add-in to install and show up under E...