Expression in query

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I have 1 Query that pulls a field with two possible
choices in it. Vendor and Source. Now if my contact Type
is source then there will be a vendor associated with it
but my query puts this into another line. So what I am
trying to accomplish is this:

If the contact type is = Source then I want to pull the
vendor into a field at the end of my row.

so my table would end up like this after running my query


Name LNum ContactType ContactName VendorName
JD 123.01 Vendor John Doe Doe it Right
JD 123.01 Source TheConsumer Doe it Right
OT 432.02 Vendor Jane Doe Jane's Quilt
GY 98.01 Source Al MostDone CR Hyper Drives
GY 98.01 Vendor IM Done CR Hyper Drives


Any help will be much appreciated. IF you have any
questions or I can provide any more information please let
me know. Thank you in advance for your time and
cooperation in helping me with this matter

Robert
 
Dear Robert:

For me, this is hard to see. Perhaps I could help if you posted full
details. That would mean:

1. The SQL text of the query you have
2. Sample data of all tables, with column names.
3. The results you're getting now (with that sample data) and how it
should change to be what you want.

I have 1 Query that pulls a field with two possible
choices in it. Vendor and Source. Now if my contact Type
is source then there will be a vendor associated with it
but my query puts this into another line. So what I am
trying to accomplish is this:

If the contact type is = Source then I want to pull the
vendor into a field at the end of my row.

so my table would end up like this after running my query


Name LNum ContactType ContactName VendorName
JD 123.01 Vendor John Doe Doe it Right
JD 123.01 Source TheConsumer Doe it Right
OT 432.02 Vendor Jane Doe Jane's Quilt
GY 98.01 Source Al MostDone CR Hyper Drives
GY 98.01 Vendor IM Done CR Hyper Drives


Any help will be much appreciated. IF you have any
questions or I can provide any more information please let
me know. Thank you in advance for your time and
cooperation in helping me with this matter

Robert
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Sorry for the incomplete info, here you go.

3 Tables -Customer-Contact Index-Schedule
when I run my query

SELECT Schedule.cdDate, Schedule.StatusCode, [Contact
Index].[Contact Name], [Contact Index].ContactsType,
Customers.City, Schedule.[Schedule Lease Number],
Customers.[Company Name], Schedule.[Equipment Amount],
Customers.State, Schedule.[Fund Month]
FROM ([Contact Index] INNER JOIN Customers ON [Contact
Index].[Customer ID] = Customers.ID) INNER JOIN Schedule
ON Customers.ID = Schedule.[Customer ID]
WHERE (((Schedule.cdDate)>=(Date()-180)) AND
((Schedule.StatusCode)<>"95-WITHDRAWN" And
(Schedule.StatusCode)<>"50-TERMNATED0 CUSTOMER" And
(Schedule.StatusCode)<>"15-PENDING" And
(Schedule.StatusCode)<>"30-ON HOLD" And
(Schedule.StatusCode)<>"40-INACTIVE" And
(Schedule.StatusCode)<>"91-EXPIRED") AND (([Contact
Index].ContactsType)="Vendor" Or ([Contact
Index].ContactsType)="Broker" Or ([Contact
Index].ContactsType)="Source"))
ORDER BY Schedule.[Schedule Lease Number];


It produces a table like this(table Wraps)
* denotes column names

----First row of data----
*cdDate *StatusCode *Contact Name *ContactsType
4/30/2003 90-DECLINED Target Dis. Source

*City *Schedule Lease # *Company Name *Equipment Amount
Hendon 40446.001 Moo & Assoc. $5,857.30

*State *Fund Month
VA 9/2003

----second row of data-----
*cdDate *StatusCode *Contact Name *ContactsType
4/30/2003 90-DECLINED ICS Services Vendor

*City *Schedule Lease # *Company Name *Equipment Amount
Hendon 40446.001 Moo & Assoc. $5,857.30

*State *Fund Month
VA 9/2003


What I am looking for is to add the column "Vendor Name"
at the end of my row so that I can pull the vendors name
on any row of data that has a source. In the end I want my
table to be like this

* denotes column names

----First row of data----
*cdDate *StatusCode *Contact Name *ContactsType
4/30/2003 90-DECLINED Target Dis. Source

*City *Schedule Lease # *Company Name *Equipment Amount
Hendon 40446.001 Moo & Assoc. $5,857.30

*State *Fund Month *Vendor Name
VA 9/2003 ICS Services


----second row of data-----
*cdDate *StatusCode *Contact Name *ContactsType
4/30/2003 90-DECLINED ICS Services Vendor

*City *Schedule Lease # *Company Name *Equipment Amount
Hendon 40446.001 Moo & Assoc. $5,857.30

*State *Fund Month |*Vendor Name|
VA 9/2003 |ICS Services|


If you need more information please let me know. Thank
you for your help on this matter

Robert
 
Back
Top