Exporting fields into Word document but some fields won't show up 1

[Iendyar]

I have created an access database that computes all the values for a contract to purchase a new home. There are 3 fields which add or subtract currency amounts to compute total price, 5% of total price for downpayment and one that computes the difference between purchase amount and mortgage amount. All the fields in the database export just fine into the Word document except those with calculations. I used the mail merge to get the fields to export. Could someone please tell me how to correct this problem? Thank you in advance!
Iendyar

 

[lespaul]

What is the SQL for the Query? Are you naming the calculations? Is your word document looking for names? What is the code for your merge?

Leslie

 

[Iendyar]

I used the mail merge in word to populate the fields, I didn't write any code to do it.
HTH,
Iendyar

 

[Iendyar]

Here are the calculations for those fields in the control source block

TotalPrice=[HomePrice]+[LotPrice]+[SitePrep]+[Contractextras]+[Contractextras1]+[Contractextras2]+[Contractextras3]

CashReq=[TotalPrice]-[ProposedMtg]

FivePer=[TotalPrice]*0.05

BalanceToClose=[TotalPrice]-[Deposit]-[FivePerDeposit]-[ProposedMtg]

These fields automatically compute those values. There must be something special I have to do to make them migrate into Word properly.
Iendyar

 

[Iendyar]

Not really sure how to use SQL. There is VB on these machines, but really haven't used too much of that either. I am an Access newbie

 

[lespaul]

In the query design grid, you can switch to the SQL view which will give you the exact SQL that the query is calling. (one way - little button on the left side of the tool bar, you can switch from design to SQL to datasheet; another way is through the menu choices, one of them will be view, but I'm not sure which one!)

So, the mail merge isn't automatic, ie. the user presses a button in Access and the document is created?

Does the user open the Word document and it's tied to the Access query?

Leslie

 

[Iendyar]

No, the document isn't called out from access yet. I was designing the template first, then I was going to try to call the template and fill in the fields using the current record. I just looked and there is not a query for the mail merge listed in my queries.
Thanks,
Iendyar

 

[Iendyar]

I just created a query with the data that I want to use. Here is the SQL:
SELECT [ContractNum], [Date], [ReferalName], [Bank], [FirstName], [LastName], [StreetAdd], [City], [State], [Zip], [HomePhone], [Fax], [Other Phone], [LotNum], [BlockNum], [SecNum], [SubDivision], [HomePrice], [LotPrice], [SitePrep], [Contractextras], [Contractextras1], [Contractextras2], [ProposedMtg], [Contractextras3], [TotalPrice], [Deposit], [CashReq], [FivePerDeposit], [BalanceToClose], [Impactfee]
FROM Contracttbl;

Thanks again,
Iendyar

 

[lespaul]

Is this a new query that already has the calculations? I don't see any calculations here?

Leslie

Leslie

 

[Iendyar]

The calculations are in the control source on the Text box in the form. Is that the wrong way to do it?
Iendyar

 

[lespaul]

The form in Access? In order to get that information into word the calculations need to be in the query.

Now above in another post you said:

BalanceToClose=[TotalPrice]-[Deposit]-[FivePerDeposit]-[ProposedMtg]


but you have a field in your query called BalanceToClose, are these the same? Do you have a caluculated field in your table?





Leslie

 

[Iendyar]

Nope, that calculation is in the properties (control source)in the form. Yes, those are the same field.
Iendyar

 

[lespaul]

In what form?

All I'm interested in is your query. All the fields for the mail merge have to be in the query. The query is the source for the mail merge document. If there are calculated fields then they need to be in the query.







Leslie

 

[Iendyar]

These fields are also currency. I don't see anyway to set the field type to calculated.
Iendyar

 

[lespaul]

Ok, you have a table:

tblContracts
ContractNumber
Date - (do you really have a field named Date? Should change that - Date is a reserved word)
(please add all the other relevant fields in here for me please)

Then you create a query:

SELECT FIELD1, FIELD2, SUM(FIELD3) As SumOfField3 FROM tblCOntracts
GROUP BY FIELD1, FIELD2

then you use the query as the source for the mail merge document.

There are no forms involved, no controls involved. There is a mail merge document and the query.

Do you have a query that has ALL the information you need for your mail merge document?

If yes, please post the SQL for the query.
If no, then please provide the table structure so that we can create your query.

Leslie

Leslie

 

[Iendyar]

Okay,
sorry the server was down. The structure is as follows:
ID which is autonum
ContractNum number which is a number assigned by the company.
Date but you told me I don't need that
Bank Text
FirstName Text
LastName Text
StreetAdd Text
City Text
State Text
Zip Number
HomePhone Number
Fax Number
OtherPhone Number
LotNum number assigned by the county
BlockNum Number assigned by the county
SecNum number assigned by the county
SubDivision assigned by the county
HomePrice currency
LotPrice currency
SitePrep currency
ContractExtras currency
ContractExtras1 currency
ContractExtras2 currency
ContractExtras3 currency
TotalPrice currency (Homeprice+LotPrice+SitePrep+ContractExtras ect.)
Deposit currency
PropMtg currency (amount of loan)
CashReq currency (TotalPrice - PropMtg)
FivePerDeposit currency (5% of TotalPrice)
BalanceToClose currency (TotalPrice - Deposit - FivePerDeposit - PropMtg)

Thanks again,
Iendyar

 

[lespaul]

First off, I didn't say you didn't need the date field, what I said was you should RENAME it!

Secondly, are these FIELDS in the TABLE? Or are these the calculated fields in the QUERY?

TotalPrice currency (Homeprice+LotPrice+SitePrep+ContractExtras ect.)
Deposit currency
PropMtg currency (amount of loan)
CashReq currency (TotalPrice - PropMtg)
FivePerDeposit currency (5% of TotalPrice)
BalanceToClose currency (TotalPrice - Deposit - FivePerDeposit - PropMtg)


If these fields are in the table, you should remove them; storing calculatd fields breaks 3NF (if you don't know what 3NF is, read 'The Fundamentals of Relational Database Design'). There are also other aspects of your table structure that break 3NF (for example, ContractExtras, ContractExtras1, etc.). Secondly, you have PERSON information stored in the CONTRACT table. Again, breaks 3NF.

I would first suggest that you review your table structure and get your database to 3NF before continuing with this project. You will find that if you do not it will become harder to extract the information you need.

Just from reviewing this table I would expect a 3NF design to be similar to:

tblPersonInfo
PersonID (PK)
LastName
FirstName
(any other information about the PERSON)

tblProperty
PropertyID (PK)
LotNum
BlockNum
SecNum
SubDivision
(any other information about the PROPERTY)

tblContract
ContractID (PK)
PersonID (FK to tblPersonInfo)
PropertyID (FK to tblProperty)
(any other information about the CONTRACT)

tblContractExtras (this table allows you to have UNLIMITED number of Extras for each contract instead of the 4 in your structure)
ContExtraID (PK)
ContractID (FK to tblContract)
ExtraAmount


I'm not sure where in this model some of your fields should go, but this will give you some idea of what your structure should look like. NONE OF THE CALCULATED FIELDS SHOULD BE STORED IN THE TABLE!!! All the calculations should be done in queries.





Leslie

 

[Iendyar]

Okay. Thank you so much for your help and your valuable time.

Iendyar

 

[Iendyar]

Oh, and those are fields in the table.
Iendyar

 

[lespaul]

OK, if those fields are in the table, then the calculation is taking place BEFORE you try to do the mail merge (you can see these values in your table)?

Have you tried recreating the mail merge process in word to see if you can get it to work from the query? Have you saved the Access query? What are the properties of the Form Field in Word where the calculations don't show up?

Leslie