Expression in Query w/ more information

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

Robert

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
 
Well, I will give this a shot without knowing what the
tables look like.

shot #1
If there is a vendor table you have to add it to the query
and join its ID and add vendor field to the output.

shot #2
If there is no vendor table, and the field [vendor name]
exists in one of the 3 given tables, you must add the
[vendor name] to the output.

shot #3
If there is no vendor table, and no [vendor name] field in
the three given tables, then you have to find the table
the field is in, or if it does not exist you have to
create the table for the vendor or add a vendor field to
an existing table, then add it to your query and add the
field to the output.

royc

-----Original Message-----

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


.
Click to expand...
 
Where do you store the "Vendor Name"? If it is in a different Table, how
does this Table relate to the Tables you posted?
 
I will get all the info up here at some point. Here is
what the 3 tables look like. I can not minipulate these
tables so I have to work with what I have. A screen print
the relationship manager is here
www.heartlandbc.com/pic.bmp this is 2.3MB
www.heartlandbc.com/pic.zip this is 36KB



* Denotes Primary Key
Table 1 - Schedule
*ID
*Customer ID
Schedule Lease Number
Application Number
Equipment amount
Fund month
Acceptance Date
Execution Date

Table 2 - Customers
*ID
Lease Number
Application Number
Status Code
Type
Company Name
Contact
Address


Table 3 - Contact Index
*ID
*Customer ID
*Schedule ID
*Contacts ID
Doc Ref
Contacts Type
Contact Code
Contact Name





-----Original Message-----

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


.
Click to expand...
 
Still can't see the [Vendor Name] anywhere in the posted Tables???

--
HTH
Van T. Dinh
MVP (Access)



Robert said:
I will get all the info up here at some point. Here is
what the 3 tables look like. I can not minipulate these
tables so I have to work with what I have. A screen print
the relationship manager is here
www.heartlandbc.com/pic.bmp this is 2.3MB
www.heartlandbc.com/pic.zip this is 36KB



* Denotes Primary Key
Table 1 - Schedule
*ID
*Customer ID
Schedule Lease Number
Application Number
Equipment amount
Fund month
Acceptance Date
Execution Date

Table 2 - Customers
*ID
Lease Number
Application Number
Status Code
Type
Company Name
Contact
Address


Table 3 - Contact Index
*ID
*Customer ID
*Schedule ID
*Contacts ID
Doc Ref
Contacts Type
Contact Code
Contact Name





-----Original Message-----

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


.
Click to expand...
Click to expand...
 
Contact Index table under Contact type it could be Source -
Vendor - Broker


-----Original Message-----
Still can't see the [Vendor Name] anywhere in the posted Tables???

--
HTH
Van T. Dinh
MVP (Access)



Robert said:
I will get all the info up here at some point. Here is
what the 3 tables look like. I can not minipulate these
tables so I have to work with what I have. A screen print
the relationship manager is here
www.heartlandbc.com/pic.bmp this is 2.3MB
www.heartlandbc.com/pic.zip this is 36KB



* Denotes Primary Key
Table 1 - Schedule
*ID
*Customer ID
Schedule Lease Number
Application Number
Equipment amount
Fund month
Acceptance Date
Execution Date

Table 2 - Customers
*ID
Lease Number
Application Number
Status Code
Type
Company Name
Contact
Address


Table 3 - Contact Index
*ID
*Customer ID
*Schedule ID
*Contacts ID
Doc Ref
Contacts Type
Contact Code
Contact Name





-----Original Message-----

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


.
Click to expand...
Click to expand...


.
Click to expand...
 
Sorry, you have to explain a bit more.

From your original post, you wanted to include the Field [Vendor Name] in
the Query so the Field [Vendor Name] must be in one of your Tables (unless
by "Vendor Name", you meant some other Field name and you think we have ESP
and know what you meant!).

OK ContactsType can be Source or Vendor or Broker but then where is the
[Vendor Name] ???

Look at the bmp file you posted and see if you find the Field [Vendor
Name]???
 
Sorry it has been a long week.

OK, there isnt a field Vendor Name (sorry for giving that
impression). This is the field I wish to create via an
expression or other means.

When I run my query the output is 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
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 want to do is pull in the vendor contact associated
with the deal. So I need to create a column named Vendor
Name and bring in the "Contact Name" for contact
type "Vendor" for every record.


I really appreciate your help and patience with this.

Robert



-----Original Message-----
Sorry, you have to explain a bit more.

From your original post, you wanted to include the Field [Vendor Name] in
the Query so the Field [Vendor Name] must be in one of your Tables (unless
by "Vendor Name", you meant some other Field name and you think we have ESP
and know what you meant!).

OK ContactsType can be Source or Vendor or Broker but then where is the
[Vendor Name] ???

Look at the bmp file you posted and see if you find the Field [Vendor
Name]???

--
HTH
Van T. Dinh
MVP (Access)



Robert said:
Contact Index table under Contact type it could be Source -
Vendor - Broker
Click to expand...



.
Click to expand...
 
aren't you duplicating the returned data since you already have [Contact
Name] as a returned Field and you can simply see from this?

If you still want the [Vendor Name] then simply create a calculated Column
with the expression:

Vendor Name: IIf( [Contact Index].[Contacts Type] = "Vendor",
[Contact Name], "")

(as 1 line in the Query Grid Field row).
 
Back
Top