Treat table data as formula in a query?

[R.T.]

I ran into a uniuqe problem, that I'm hoping I can get a different
viewpoint on.

A year or so ago, I wrote a database application that pulls
Subcontractor informtion (address, etc) from a 'contacts' table and
combines it with information pulled from a 'project' table (Job
Number, contract amount, etc) and generates a 13-20 (depending on # of
line items) page subcontract. This works wonderful with a single
exception. Some of our subcontractors require specific wording to be
changed in the contract.

As a solution to this, I've created a table that houses the contract
broken down into sections numbers. A user can copy and change any
given section, and then choose the order and the sections to include
in the contract. My problem is that I can't grasp what I need to do
to add the specific vendor/project dynamic data into the contract
table. I've listed an example below.


Table - tblContractText

Key - 1
Text - THIS SUBCONTRACT, made this _______ day of ______________,
20____, by and between [Vendor info from tblContacs need to go here],
hereinafter called the Subcontractor, and ABC Company, hereinafter
called the Contractor.
Use - Yes
Order - 1

Ok, the above will print the text in the first position of the
contract. Any ideas as to how I can reference the vendor info from
the table?

A bit more info, the project and vendor used for the subcontract are
selected by selecting each from a listing of jobs, and a contact book,
respectively.

Of the roughly 15 pages of the contract, there are 6 or 7 sections
that require dynamic data. The majority of it would be at the
beginning, which brought to mind sub reports, but there is a few ares
right in the middle of the contract that require dynamic data.

Basically, what would be prefect, would be a method of treating the
data in the table as a formula.

Any takers?

 

[Michel Walsh]

Hi,

If a table [generic] is like:

Part, Formulation ' fields name
1, "This subcontract ... "
2, " and ABC Company ... " ' data sample



and if you have a table [specific] like:


Part, SubContractorID, Formulation
1, 1029, "The sub-contract ... "
1, 1043, "This sub-contract ... "
2, 1043, "and The ABC Company ... "


then


SELECT a.Part, Nz(b.Formulation, a.Formulation) As useThis
FROM generic As a LEFT JOIN specific As b
ON a.part = b.part
WHERE specificSubContractorID=
Nz(b.SubContractorID, specificSubContractorID)
ORDER BY a.Part


would retrieve the various appropriate parts.

For SubContractor 1043, the two specific parts would be picked up; for
1029, part1 would be the specific one, part2 would be the generic one. For a
SubContractorID having not entry in the specific table, all parts would be
from the generic table.




Hoping it may help,
Vanderghast, Access MVP

I ran into a uniuqe problem, that I'm hoping I can get a different
viewpoint on.

A year or so ago, I wrote a database application that pulls
Subcontractor informtion (address, etc) from a 'contacts' table and
combines it with information pulled from a 'project' table (Job
Number, contract amount, etc) and generates a 13-20 (depending on # of
line items) page subcontract. This works wonderful with a single
exception. Some of our subcontractors require specific wording to be
changed in the contract.

As a solution to this, I've created a table that houses the contract
broken down into sections numbers. A user can copy and change any
given section, and then choose the order and the sections to include
in the contract. My problem is that I can't grasp what I need to do
to add the specific vendor/project dynamic data into the contract
table. I've listed an example below.


Table - tblContractText

Key - 1
Text - THIS SUBCONTRACT, made this _______ day of ______________,
20____, by and between [Vendor info from tblContacs need to go here],
hereinafter called the Subcontractor, and ABC Company, hereinafter
called the Contractor.
Use - Yes
Order - 1

Ok, the above will print the text in the first position of the
contract. Any ideas as to how I can reference the vendor info from
the table?

A bit more info, the project and vendor used for the subcontract are
selected by selecting each from a listing of jobs, and a contact book,
respectively.

Of the roughly 15 pages of the contract, there are 6 or 7 sections
that require dynamic data. The majority of it would be at the
beginning, which brought to mind sub reports, but there is a few ares
right in the middle of the contract that require dynamic data.

Basically, what would be prefect, would be a method of treating the
data in the table as a formula.

Any takers?

 

[R.T.]

Thank you for the feed back, this method actually may work a little
better than what I have in place. But, it still doesn't address the
issue that I am currently stumped on.

Perhaps more information is in order:

Tables that we are interested in

--

tblCOntractItems
Fields: JobNumber, ContractorNumber, LineItems

Since a particular Contractor may have multiple line items for each
contract, my Key for this table is created by appending the
Vendor#-Job#-LineItem. Any give project may have any number of subs.

--

tblProjects
Fields: JobNumber, Description, Owner

This table contains one record for every job. It does not store line
items, or anything that relates to subcontractors.

--

tblSubcontractors
Fields: ContractorNumber, Name, Address, (other Stuff)

This table is basically just a contact book.

--

tblContractText
Fields: ReportPlacement,ContractText, Description,

The report placement is a numerical field that decides where the
placement of each paragraph is printed. A null value here will leave
the paragraph out of the report. The ContractText field is the
actually wording that will be printed. The Description field will
describe the paragraph (i.e. use this ONLY for capital electric).


Ok, to try and keep this simple, I will show you how I could put this
on a report with static text. For readablitly, I won't use full
paths. The data is select by a prompt (ddb) for job number and
contractornumber which provides a unique list of contract items.

="The contract between us and " & [Owner] & "is made today. The items
included on this contract are as follows: " & [lineitems]

This is how the old application worked. No problem. But now, I want
to add the dynamic text from the tblContractText and combine it with
the dynamic job/sub data. This is where I've became stuck.

Thanks for your time!

Hi,

If a table [generic] is like:

Part, Formulation ' fields name
1, "This subcontract ... "
2, " and ABC Company ... " ' data sample



and if you have a table [specific] like:


Part, SubContractorID, Formulation
1, 1029, "The sub-contract ... "
1, 1043, "This sub-contract ... "
2, 1043, "and The ABC Company ... "


then


SELECT a.Part, Nz(b.Formulation, a.Formulation) As useThis
FROM generic As a LEFT JOIN specific As b
ON a.part = b.part
WHERE specificSubContractorID=
Nz(b.SubContractorID, specificSubContractorID)
ORDER BY a.Part


would retrieve the various appropriate parts.

For SubContractor 1043, the two specific parts would be picked up; for
1029, part1 would be the specific one, part2 would be the generic one. For a
SubContractorID having not entry in the specific table, all parts would be
from the generic table.




Hoping it may help,
Vanderghast, Access MVP

I ran into a uniuqe problem, that I'm hoping I can get a different
viewpoint on.

A year or so ago, I wrote a database application that pulls
Subcontractor informtion (address, etc) from a 'contacts' table and
combines it with information pulled from a 'project' table (Job
Number, contract amount, etc) and generates a 13-20 (depending on # of
line items) page subcontract. This works wonderful with a single
exception. Some of our subcontractors require specific wording to be
changed in the contract.

As a solution to this, I've created a table that houses the contract
broken down into sections numbers. A user can copy and change any
given section, and then choose the order and the sections to include
in the contract. My problem is that I can't grasp what I need to do
to add the specific vendor/project dynamic data into the contract
table. I've listed an example below.


Table - tblContractText

Key - 1
Text - THIS SUBCONTRACT, made this _______ day of ______________,
20____, by and between [Vendor info from tblContacs need to go here],
hereinafter called the Subcontractor, and ABC Company, hereinafter
called the Contractor.
Use - Yes
Order - 1

Ok, the above will print the text in the first position of the
contract. Any ideas as to how I can reference the vendor info from
the table?

A bit more info, the project and vendor used for the subcontract are
selected by selecting each from a listing of jobs, and a contact book,
respectively.

Of the roughly 15 pages of the contract, there are 6 or 7 sections
that require dynamic data. The majority of it would be at the
beginning, which brought to mind sub reports, but there is a few ares
right in the middle of the contract that require dynamic data.

Basically, what would be prefect, would be a method of treating the
data in the table as a formula.

Any takers?

 

[Michel Walsh]

Hi,


The most versatile solution would be to replace the line with a call to a
VBA function you will define:


= printLine0001( list_of_arguments )



and your VBA function would look like:

------------------------------------------
Public Function printLine0001( list_of_arguments ) As String

Select Case ContractorID

Case 1025
' have something special for 1025
printLine0001=" .... " & ... & ....

Case 1045
' as well as for 1045
printLiIne0001="..."

Case Else
' otherwise, use the standard formulation
printLine0001 = "The contract between us and " & [Owner] & " is made
today. The items included on this contract are as follows: " & [lineitems]
End Select

End Function
----------------------------------------



Since part of the logic is wired in the code, that means you have to modify
the code to add a new case.



Hoping it may help,
Vanderghast, Access MVP

Thank you for the feed back, this method actually may work a little
better than what I have in place. But, it still doesn't address the
issue that I am currently stumped on.

Perhaps more information is in order:

Tables that we are interested in

--

tblCOntractItems
Fields: JobNumber, ContractorNumber, LineItems

Since a particular Contractor may have multiple line items for each
contract, my Key for this table is created by appending the
Vendor#-Job#-LineItem. Any give project may have any number of subs.

--

tblProjects
Fields: JobNumber, Description, Owner

This table contains one record for every job. It does not store line
items, or anything that relates to subcontractors.

--

tblSubcontractors
Fields: ContractorNumber, Name, Address, (other Stuff)

This table is basically just a contact book.

--

tblContractText
Fields: ReportPlacement,ContractText, Description,

The report placement is a numerical field that decides where the
placement of each paragraph is printed. A null value here will leave
the paragraph out of the report. The ContractText field is the
actually wording that will be printed. The Description field will
describe the paragraph (i.e. use this ONLY for capital electric).


Ok, to try and keep this simple, I will show you how I could put this
on a report with static text. For readablitly, I won't use full
paths. The data is select by a prompt (ddb) for job number and
contractornumber which provides a unique list of contract items.

="The contract between us and " & [Owner] & "is made today. The items
included on this contract are as follows: " & [lineitems]

This is how the old application worked. No problem. But now, I want
to add the dynamic text from the tblContractText and combine it with
the dynamic job/sub data. This is where I've became stuck.

Thanks for your time!

Hi,

If a table [generic] is like:

Part, Formulation ' fields name
1, "This subcontract ... "
2, " and ABC Company ... " ' data sample



and if you have a table [specific] like:


Part, SubContractorID, Formulation
1, 1029, "The sub-contract ... "
1, 1043, "This sub-contract ... "
2, 1043, "and The ABC Company ... "


then


SELECT a.Part, Nz(b.Formulation, a.Formulation) As useThis
FROM generic As a LEFT JOIN specific As b
ON a.part = b.part
WHERE specificSubContractorID=
Nz(b.SubContractorID, specificSubContractorID)
ORDER BY a.Part


would retrieve the various appropriate parts.

For SubContractor 1043, the two specific parts would be picked up; for
1029, part1 would be the specific one, part2 would be the generic one. For
a
SubContractorID having not entry in the specific table, all parts would be
from the generic table.




Hoping it may help,
Vanderghast, Access MVP

I ran into a uniuqe problem, that I'm hoping I can get a different
viewpoint on.

A year or so ago, I wrote a database application that pulls
Subcontractor informtion (address, etc) from a 'contacts' table and
combines it with information pulled from a 'project' table (Job
Number, contract amount, etc) and generates a 13-20 (depending on # of
line items) page subcontract. This works wonderful with a single
exception. Some of our subcontractors require specific wording to be
changed in the contract.

As a solution to this, I've created a table that houses the contract
broken down into sections numbers. A user can copy and change any
given section, and then choose the order and the sections to include
in the contract. My problem is that I can't grasp what I need to do
to add the specific vendor/project dynamic data into the contract
table. I've listed an example below.


Table - tblContractText

Key - 1
Text - THIS SUBCONTRACT, made this _______ day of ______________,
20____, by and between [Vendor info from tblContacs need to go here],
hereinafter called the Subcontractor, and ABC Company, hereinafter
called the Contractor.
Use - Yes
Order - 1

Ok, the above will print the text in the first position of the
contract. Any ideas as to how I can reference the vendor info from
the table?

A bit more info, the project and vendor used for the subcontract are
selected by selecting each from a listing of jobs, and a contact book,
respectively.

Of the roughly 15 pages of the contract, there are 6 or 7 sections
that require dynamic data. The majority of it would be at the
beginning, which brought to mind sub reports, but there is a few ares
right in the middle of the contract that require dynamic data.

Basically, what would be prefect, would be a method of treating the
data in the table as a formula.

Any takers?