Plater 7,872
Recognized Expert Expert
(I don't even know how to title this)
The bossman says I need to add a certain field into a query and I have yet to figure out out to do it.
I will try and make a trimmed down example.
Table1's columns: -
myUniqueIDField | (other columns)
-
Table2's columns: -
aUniqueID | LinkToTable1myUniqueIDField | StringColumn | (other columns)
-
So what I'm saying is: Any entry in Table1 can have 0-n number of entries in Table2. Nothing special there.
I need ALL columns from all entries in Table1, regardless of if they are in Table2 (left join, nothing big yet)
The problem comes with the extra column he wants.
The contents of that extra column need to be dependant on what is found in Table2 and based on Table2's [StringColumn].
For example some entries in Table2 code be: -
1 234 "Action Needed"
-
2 367 "Action Taken"
-
3 234 "Bad Data"
-
My computed column needs to know if for a given record of Table1, if there are any entries in Table2 that say "Action Needed" or "Action Taken".
The computed column (for all it matters) could have three possible values then:
It could be blank (meaning it found nothing of use in Table2)
It could say "Action Needed" (meaning it found an entry in Table2 that matches)
It could say "Action Taken" (meaning it found an entry in Table2 that matches)
If this were a one - to - one relationship it would be easy enough to be like Table2.StringCo lumn and just display it's contents, but there will be many entries in Table2.
So if anyone made any sense of that and has any suggestions I'm all ears.
(It would even be acceptable to me if the extra column contained a concatinated string of all the StringColumns in Table2 that matched that record, I could post process)
6 3196
Am confused with all those lines.. please make a display of your desired output.. :)
Plater 7,872
Recognized Expert Expert
Well maybe this sample set will help make things more understandable. It's hard to explain.
Sample Table1 Data: -
200 (some data)
-
201 (some data)
-
202 (some data)
-
203 (some data)
-
204 (some data)
-
205 (some data)
-
Sample Table2 Data: -
1 200 "SomeValue"
-
2 200 "SomeOtherValue"
-
3 200 "Action Required"
-
4 202 "Acion Taken"
-
5 202 "SomeValue"
-
6 203 "SomeValue"
-
7 204 "Action Required"
-
Sample Output of the query: -
200 (some data) "Action Required"
-
201 (some data) ""
-
202 (some data) "Action Taken"
-
203 (some data) ""
-
204 (some data) "Action Required"
-
204 (some data) ""
-
ck9663 2,878
Recognized Expert Specialist
Well maybe this sample set will help make things more understandable. It's hard to explain.
Sample Table1 Data: -
200 (some data)
-
201 (some data)
-
202 (some data)
-
203 (some data)
-
204 (some data)
-
205 (some data)
-
Sample Table2 Data: -
1 200 "SomeValue"
-
2 200 "SomeOtherValue"
-
3 200 "Action Required"
-
4 202 "Acion Taken"
-
5 202 "SomeValue"
-
6 203 "SomeValue"
-
7 204 "Action Required"
-
Sample Output of the query: -
200 (some data) "Action Required"
-
201 (some data) ""
-
202 (some data) "Action Taken"
-
203 (some data) ""
-
204 (some data) "Action Required"
-
204 (some data) ""
-
Llet's see. you need all the rows and columns in table1 regardless if the key exists in table 2. You also need the status (am assuming this column name) in table2 if the key is existing in table2 and the status has the word 'action' on it.
If am wrong, don't continue reading this.
Otherwise, try something like... - select table1.*, table2.someothercolumn,
-
from table1 left join table2 on table1.key = table2.key
-
left join (select key, somecolumn, statusoftask from table2 where statusoftask like '%action%') status on status.key = table1.key
-
If you have two or more status with the word 'action' on it, it will grab the first one. If there are no status with the word 'action', it'll be NULL. just use IsNull as necessary.
Good luck,
-- CK
Plater 7,872
Recognized Expert Expert
That looks like what I am after.
I was not aware I could use a select clause like that in the FROM section.
I will have to investigate it when back at work tomorrow and let you know if it works out. Thanks.
ck9663 2,878
Recognized Expert Specialist
Llet's see. you need all the rows and columns in table1 regardless if the key exists in table 2. You also need the status (am assuming this column name) in table2 if the key is existing in table2 and the status has the word 'action' on it.
If am wrong, don't continue reading this.
Otherwise, try something like... - select table1.*, table2.someothercolumn,
-
from table1 left join table2 on table1.key = table2.key
-
left join (select key, somecolumn, statusoftask from table2 where statusoftask like '%action%') status on status.key = table1.key
-
If you have two or more status with the word 'action' on it, it will grab the first one. If there are no status with the word 'action', it'll be NULL. just use IsNull as necessary.
Good luck,
-- CK
Also, try: - select table1.*, table2.someothercolumn, stat.statusoftask
-
from table1
-
left join table2 on table1.key = table2.key
-
left join table2 stat on table1.key = stat.key and statusoftask like '%action%'
-
If this works, try this one coz I think this is faster, because there's no subquery. You might also want to create index for faster performance.
I also think I might've missed a column. It should've been: - select table1.*, table2.someothercolumn, stat.statusoftask
-
from table1 left join table2 on table1.key = table2.key
-
left join (select key, somecolumn, statusoftask from table2 where statusoftask like '%action%') status on status.key = table1.key
-
Happy coding
-- CK
Plater 7,872
Recognized Expert Expert
Oh yeah, that 2nd one using LIKE works great.
Learning all kinds of new things. Was unaware the JOINs could have more then one "requiremen t" thing in there. Thought it could only have the key=key thing.
Thanks for the help on that. I had been trying like group by and distinct but wasn't getting anywhere.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: kj |
last post by:
When I run the attached query, I get duplicates when there is one to
many relationship between tableA and tableB. The query, tested schema
and the result is attached. Sorry for the long post.
Here is tested Schema and Data inserts.
----------------------
create table TestTblA
(ShipDate datetime,
CPEID varchar(30),
phonenum char(14))
|
by: Simon Withers |
last post by:
I have 3 data tables, A, B and C, with many to many relationship
tables between A-B and A-C.
The data in A and C changes rarely, and the A-C relationship relates
all possible combinations of A to a C
If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could
contain the records:
A.1, C.1
|
by: Keith |
last post by:
I am having a problem creating a many-to-many-to-many type relationship. It
works fine, but when I create a view to query it and test it, it does not
generate the results I expected.
Below if the DDL for the tables and the SQL for the view.
|
by: Megan |
last post by:
hello everybody,
i know this is a very long post, but i wanted to provide as much
detail as possible.
quick overview- i want to create a couple of many to many
relationships and am wondering how many relationships to create. i am
also trying to figure out what relationships to create.
|
by: Apple |
last post by:
I have to create a query with many to many relationship, but I can't
break it into 2 x 1 to many, should there anyone can teach me how to
solve this problem. Thanks in advance!
| |
by: Rico |
last post by:
Hello,
I have an application that I'm converting to Access 2003 and SQL Server 2005
Express. The application uses extensive use of DAO and the SEEK method on
indexes. I'm having an issue when the recordset opens a table. When I
write
Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly)
I get an error. I believe it's invalid operation or invalid parameter, I'm
|
by: pedro8ae |
last post by:
Hi friends, I am developing a project for an department in a school.
this school has some other sub-campus and each campus has different
rooms. ex.:
School XY:
sub-Campus 1:-Room1.1, Room 1.2, Room 1.3, Room 1.4, Room 1.4
sub-Campus 2:-Room2.1, Room2.2
sub-Campus 3:-Room3.1
I want to create a form that allow the user to select a sub-campus and
then automatically
|
by: Tableshavturned |
last post by:
Hi this is my first post on the forums. I haven't really developed before with Access 2003 so trouble shooting with this application is not my forte. The issue at hand is, created a star schema with all the IDs in one table for the eight tables I created in design view, the datatype for all the IDs is number in this Table.
ID
EventID
ObsID
RecID
ActItemID
LocID
AssgnID
ImpctID
ProjID
|
by: NicoleCartrette |
last post by:
Going back to school is easier said than done..
This was posted to an older thread earlier but I don't think it got any attention. Your help is appreciated
Professor requires we create a simple database and specified what are to be the primary keys and relationships etc.
I have created the four tables and established the neccessary one to many and many to one relationships between primary keys in the tables with the exception of one...
|
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...
|
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,...
| |
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |