Base Form to post Currency Category total to Table or Query

[Lester]

I have a Base Form that posts the Total calculation to Query but not to Table. I cannot retain Currency Category in Query to print out for the Total. I could print my results from either Table or Query but I need it to print out the Total in Currency Category. How can I make it do both, or at least the Table or Query?

[MTP]

I'm sorry, I'm not able to understand your problem. Maybe you could post your file (or a simplified example file with any private information removed) to help us understand better?

[Lester]

My 'Form' shows calculation total in Currency Category ($10.00). My 'query' calculates the total but it shows (10.00), not Currency Category. I can get 'query' to show Currency Category by right clicking my Total column and clicking Column Format/Currency but I cannot get the format to remain. When I go out of 'query' my Currency format leaves. My 'Table' doesn't show calculation Total at all. I am so new at OOForum that I don't know how to post a file. I would be happy to send you the file e-mail. My email is: zzzlecjac<at>gmail.com

[UnklDonald418]

Here is a link to a Tutorial on how to upload to this Forum
viewtopic.php?f=74&t=8289

[Lester]

I am sending my Database file so you can understand my problem better. I send the Database information through 'Writer' email documents (Envelope and Receipt).

[charlie.it]

Ciao, I moved the Refresh button from MainForm to SubForm. It works for me.

[MTP]

I made some additional comments to the database structure: for some items with a 1-to-many relationship, I created a new table for the "many" end.

So some rows in the main DATABASE table had more than one ("many") contact names. I made a new table called "Contacts" where each row has:

ContactID (primary key)
AccountID (foreign key, meaning it matches an ID number from the DATABASE table)
ContactName

You may want to expand on this and add other columns unique to each contact like ContactPhone.

I changed the form to have a subform "Contacts" linked to the table of the same name. I used the "linked fields" property to tie the subform to the masterform through the AccountID. So now you can add an infinite number of contacts to any account instead of being limited to 4.

Similarly for the amounts - there is a new table "Amounts" and the form is changed to allow any number of amounts to be entered for each customer.

I made another subform for the total. The SQL command for the query was short so I just put it inside the form instead of making a separate query.

I hope this will work more smoothly for you.

[Lester]

MTP THANK YOU for all your work on my project. It is appreciated. However, I need to mail-merge the information to a Writer "Receipt" document which I send to a group every month. I don't know a way to do that with the multiple "Tables". I actually print from the "Table".

[MTP]

Have you looked into using a Base Report to create the Receipt documents? Especially if you download the Oracle Report Builder extension, I suspect this would provide the functionality you are looking for.

There would be a learning curve to setting this up; whether climbing the learning curve would be worthwhile depends on how useful it would be to have the flexibility of multiple tables (so you're not locked into 4 contacts or 15 payments per account).

[chrisb]

hello Lester,

just seen the post by MTP. great minds think alike!

i do use mail merge but don't have the knowledge which would enable me to offer help or advice concerning your issue.
an alternative solution could be to create a report & print from that.

i have included an attachment which contains a simple report created with the Oracle Report Builder.
the attachment also contains a small macro which enables you to launch the report from a button control contained in the form 'fOpen_Report(Annual_Donations)'.
just select the person & year then hit 'Open Report'.

Edit: 06-Aug-2016 12:04. PLEASE IGNORE THE FOLLOWING LINE. THE DATABASE DOES NOT NEED TO BE REGISTERED

in order for this to work you will need to register this database HERE>>>> menu:Tools>Options>OpenOffice Base>Databases>New

you will also see that i have restructured your database.
new tables have been created & forms have been added.
data input has been simplified.

the query 'q_INFO_People_Personal_Details_SQL' can be used to print envelopes when using mail merge.

one of the huge advantages in using a database is the ability to extract, manipulate & sort the data.