|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 2nd, 2009, 07:33 AM
posted to microsoft.public.access.queries
|
|||
|
|||
query help
access 2003:
I need to get to tableC from tableA and TableB. I tried hard to get to TableC but had no luck. tableA: ID N1 N2 N3 1 12 A1 B1 2 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 10 G3 I2 TableB: ID N1 Y1 20 12 33 21 55 51 22 67 23 23 10 34 result: TableC: ID Y1 N1 N2 N3 1 33 12 A1 B1 2 33 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 34 10 G3 I2 so on. -- Message posted via http://www.accessmonster.com 
|
|
#2
December 2nd, 2009, 09:11 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
One thing i do notice is their is no relation between the two tables. You
cannot link the two tables to obtain the result you need. "igg via AccessMonster.com" u53571@uwe wrote in message news:9ff9bfbb4aca6@uwe... access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC but had no luck. tableA: ID N1 N2 N3 1 12 A1 B1 2 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 10 G3 I2 TableB: ID N1 Y1 20 12 33 21 55 51 22 67 23 23 10 34 result: TableC: ID Y1 N1 N2 N3 1 33 12 A1 B1 2 33 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 34 10 G3 I2 so on. -- Message posted via http://www.accessmonster.com
|
|
#3
December 2nd, 2009, 09:11 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
One thing i do notice is their is no relation between the two tables. You
cannot link the two tables to obtain the result you need. "igg via AccessMonster.com" u53571@uwe wrote in message news:9ff9bfbb4aca6@uwe... access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC but had no luck. tableA: ID N1 N2 N3 1 12 A1 B1 2 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 10 G3 I2 TableB: ID N1 Y1 20 12 33 21 55 51 22 67 23 23 10 34 result: TableC: ID Y1 N1 N2 N3 1 33 12 A1 B1 2 33 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 34 10 G3 I2 so on. -- Message posted via http://www.accessmonster.com
|
|
#4
December 2nd, 2009, 09:11 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
One thing i do notice is their is no relation between the two tables. You
cannot link the two tables to obtain the result you need. "igg via AccessMonster.com" u53571@uwe wrote in message news:9ff9bfbb4aca6@uwe... access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC but had no luck. tableA: ID N1 N2 N3 1 12 A1 B1 2 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 10 G3 I2 TableB: ID N1 Y1 20 12 33 21 55 51 22 67 23 23 10 34 result: TableC: ID Y1 N1 N2 N3 1 33 12 A1 B1 2 33 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 34 10 G3 I2 so on. -- Message posted via http://www.accessmonster.com
|
|
#5
December 2nd, 2009, 01:12 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
So does TableA.N1 match up against TableB.N1?
Assuming that is true SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3 FROM TableA LEFT JOIN TableB ON TableA.N1 = TableB.N1 If you are doing this in query design view == Add both tables == Drag from TableA.N1 to TableB.N1 == Double-click on the join line and select the option that gives you ALL records in TableA and matching records in TableB == Select the fields you want to display. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County igg via AccessMonster.com wrote: access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC but had no luck. tableA: ID N1 N2 N3 1 12 A1 B1 2 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 10 G3 I2 TableB: ID N1 Y1 20 12 33 21 55 51 22 67 23 23 10 34 result: TableC: ID Y1 N1 N2 N3 1 33 12 A1 B1 2 33 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 34 10 G3 I2 so on.
|
|
#6
December 2nd, 2009, 01:12 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
So does TableA.N1 match up against TableB.N1?
Assuming that is true SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3 FROM TableA LEFT JOIN TableB ON TableA.N1 = TableB.N1 If you are doing this in query design view == Add both tables == Drag from TableA.N1 to TableB.N1 == Double-click on the join line and select the option that gives you ALL records in TableA and matching records in TableB == Select the fields you want to display. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County igg via AccessMonster.com wrote: access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC but had no luck. tableA: ID N1 N2 N3 1 12 A1 B1 2 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 10 G3 I2 TableB: ID N1 Y1 20 12 33 21 55 51 22 67 23 23 10 34 result: TableC: ID Y1 N1 N2 N3 1 33 12 A1 B1 2 33 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 34 10 G3 I2 so on.
|
|
#7
December 2nd, 2009, 01:12 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
So does TableA.N1 match up against TableB.N1?
Assuming that is true SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3 FROM TableA LEFT JOIN TableB ON TableA.N1 = TableB.N1 If you are doing this in query design view == Add both tables == Drag from TableA.N1 to TableB.N1 == Double-click on the join line and select the option that gives you ALL records in TableA and matching records in TableB == Select the fields you want to display. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County igg via AccessMonster.com wrote: access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC but had no luck. tableA: ID N1 N2 N3 1 12 A1 B1 2 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 10 G3 I2 TableB: ID N1 Y1 20 12 33 21 55 51 22 67 23 23 10 34 result: TableC: ID Y1 N1 N2 N3 1 33 12 A1 B1 2 33 12 D4 Z1 3 45 V5 S3 4 45 F3 O2 5 34 10 G3 I2 so on.
|
|
#8
December 5th, 2009, 07:51 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
This query works but how to use where statement such as where not exists or
where exits instead of "Left join" to get the same result. John Spencer wrote: So does TableA.N1 match up against TableB.N1? Assuming that is true SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3 FROM TableA LEFT JOIN TableB ON TableA.N1 = TableB.N1 If you are doing this in query design view == Add both tables == Drag from TableA.N1 to TableB.N1 == Double-click on the join line and select the option that gives you ALL records in TableA and matching records in TableB == Select the fields you want to display. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC [quoted text clipped - 25 lines] so on. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1
|
|
#9
December 5th, 2009, 07:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
query help
I don't know of a way to return the results you stated you wanted using an
exists or not exists subquery. I guess you could use a subquery to return the one field in tableB SELECT TableA.ID , (Select Y1 FROM TableB WHERE TableB.N1 = TableA.N1) as Y1 , TableA.N1 , TableA.N2 , TableA.N3 FROM TableA That query will be less efficient than using the LEFT JOIN since you are using a correlated subquery to get the ONE field you want. That means that you will run a query against TableB for every row in tableA. If all you want to know is whether or not (true or False) a value exists in TableB for Y1 then SELECT TableA.ID , Exists (Select Y1 FROM TableB WHERE TableB.N1 = TableA.N1) as Y1Exists , TableA.N1 , TableA.N2 , TableA.N3 FROM TableA If you want to know if a matching ROW exists (whether or not Y1 is Null or not) SELECT TableA.ID , Exists (Select * FROM TableB WHERE TableB.N1 = TableA.N1) as Y1Exists , TableA.N1 , TableA.N2 , TableA.N3 FROM TableA John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County igg via AccessMonster.com wrote: This query works but how to use where statement such as where not exists or where exits instead of "Left join" to get the same result. John Spencer wrote: So does TableA.N1 match up against TableB.N1? Assuming that is true SELECT TableA.ID, TableB.Y1, TableA.N1, TableA.N2, TableA.N3 FROM TableA LEFT JOIN TableB ON TableA.N1 = TableB.N1 If you are doing this in query design view == Add both tables == Drag from TableA.N1 to TableB.N1 == Double-click on the join line and select the option that gives you ALL records in TableA and matching records in TableB == Select the fields you want to display. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County access 2003: I need to get to tableC from tableA and TableB. I tried hard to get to TableC [quoted text clipped - 25 lines] so on.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
