Database Relationships or whatever!

[BaldFox]

Hello,

I've built a database that contains 21 tables.

It has Reports that mostly show some text, plus financials that show totals of particular actions, like the costs involved in harvesting, crop spraying, paddock development, inventories, etc.

These "minor" reports are all in good shape, but I would like to have a Full Report that displays ALL the financial data that has been put into each and every table to give the farmer an overall idea of what his rural life is costing him.

There are 19 tables that contain financial data. I've given every thing I've got to get a Relationship working: creating common fields within each table, etc. I'm having a hard time understanding the "relationship" technique and Access's Help is of not much use!

The fields that I want to "join" are few and comprise the date of the action(s), the paddock in which the action(s) took place and the total expense of the action(s).

The Full Report appears to work, except that no data shows: just the previously manually-placed headings. Oh, and there is data in the appropriate tables, too.

I need some serious assistance, and any idea will be taken as a good idea.

Thanks and regards to all.

[jzwp11]

There are 19 tables that contain financial data

Generally speaking like data should be in stored in the same table. Can you be more specific about these 19 tables that contain financial data. Can you provide the table structure of a couple of these tables as well as some of the data as an example?

[ssanfu]

Hello,

I've built a database that contains 21 tables.
<snip>

<snip>
I need some serious assistance, and any idea will be taken as a good idea.

Thanks and regards to all.

I think it would help to read a up on normalization. Crystal has a good introductory at

http://www.accessmvp.com/Strive4Peace/


Scroll down the page to "Summary of Contents for Access Basics". Pay close attention to Chapter 3 "Normalizing Data".
I have read these chapters many times and every time I see something I've missed in the previous reads.

There are many other sites - Google Normalization.

[BaldFox]

Hi jzwp11,

Thanks for your reply.

I had thought of storing "like data" (i. e. 'total expense') in the one table, but MSA won't let you put more than one record source onto a form, I think.

If this is incorrect, how would I then be able to have, say the following fields from the table called "Harvest": harvdate; padnum; tractor and the other 79 table fields on a form and then include 'totalexp' from another table?

Almost all current tables have in common a field called 'totalexp', so is it this field that a relationship should be based upon? I don't know!

I also thought of having just one HUGE table that would comprise of over 2132 fields. This would be one cumbersome mother of a table to work with and manipulate, I'd reckon! Is there any way that I can automatically add all these tables together to make just one? To do it manually, will see me in my grave!

Hi ssanfu,

Thanks for your information: I read just about everything in Crystal's files, but unfortunately are none the wiser.

All the best.

[jzwp11]

I also thought of having just one HUGE table that would comprise of over 2132 fields.

Access can only handle 255 fields in a table if I remember correctly.

I suspect that your many of your fields should probably be records in a table. Is there any way you can post your relationship diagram or your database with any sensitive data removed, so that we can see what you have in detail?

but MSA won't let you put more than one record source onto a form, I think.

Access can have only 1 record source for a form but that record source can be a query that joins multiple tables.

[BaldFox]

Hi jzwp11 again,

This Report Wizard "feature" is still driving me crazy! Over the last few weeks, I've been reading everything I can lay my hands on.

Well, the Wizard has started to make a bit of sense, although now when I create a report with a just single table on it comprising 2 fields, the sums appear, but as soon as I add a field from any other and different table, that first table's sum figure fails. The tables all have a relationship, but I reckon it's going to come to a divorce, or at least a separation, very soon!

I read in MS Access's Help that sometimes when a report does not contain the data you reckon should be there is caused by "filtered fields", but the database doesn't use filtering anywhere within it.

I can't think of any way of showing either the "Relationships" here, other than just now uploading a pdf file showing the small example, and oddly enough I've got two of them. Is this abnormal? The second is huge and these is no way that I can figure out how to upload it, other than to have an e-mail address where I could send the entire database. It's only 17meg.

I'm open to any and all suggestions. I must get the hang of this problem ASAP.

Thanks to all.

[jzwp11]

Just from what you posted, you have structural issues with your database, so for now, the report should wait.

In your cattle, fert, diesel and I assume funcidige (what I can see) tables, you have similar fields. Additionally, under most circumstances, you would not store a total in a table (only those items used to calculate the total).

Also, I see that you are trying to link the tables via the ID fields in the respective tables. That is probably not correct either.

Concentrating on just these few tables, can you explain what type of data is contained in each and what you do with it in your business process.

[BaldFox]

Hi jzwp11,

The four simple tables in the previous post contain fields that do pretty much the same thing.

They contain fields named:

ID - (the Primary Key in all tables),
Date - (date of purchase),
Item - (name of product purchased),
Qty - (number of item [product] purchased),
Cost - (cost per item [product] purchased), and
Total - (Qty field x Cost field).

Some of the other tables have many more fields naturally, except for two that have no financial fields involved, but all contain the above fields.

The basic idea behind the database is to show the costs to the farmer of how much he/she spends on products (i. e. fumigants, fertilizers, fuels, etc.), and stock, crop spraying and harvesting, paddock development, etc.

The bigger picture of the database and tables is to allow the farmer to record just about everything that occurs during a particular event (i. e. crop spraying and harvesting), inasmuch as weather conditions, targeted pests, machinery and chemicals used and their associated costs, labour and fuel costs, etc.

To this point, I think that everything works extremely well.

It's when I try to create a single summary Report or Form, that I seem to "drop the ball".

Rather than have the farmer manually add up the totals of the 20 existing reports (one report for each table that contains financial data), I want a single Report or Form that displays the total of each table, AND then display the Grand Total (the bottom line as it were) of all this expenditure.

I would be more than happy to e-mail the entire unrestricted .mdb file to you, if you so desire.

Many thanks for your help.

Regards.

[jzwp11]

The four simple tables in the previous post contain fields that do pretty much the same thing.

ID - (the Primary Key in all tables),
Date - (date of purchase),
Item - (name of product purchased),
Qty - (number of item [product] purchased),
Cost - (cost per item [product] purchased), and
Total - (Qty field x Cost field).

If a series of tables have a similar structure, it is generally an indication that the data is similar enough to store all in 1 table.

I would probably try and generalize things.

For example, you say that these are products: fumigants, fertilizers, fuels, chemicals, stock etc.

So have a table that stores all products as records

tblProducts
-pkProductID primary key, autonumber
-txtProductName
-UnitPrice

tblCustomerPurchase
-pkCustPurchaseID primary key, autonumber
-fkCustomerID foreign key to relate back to the customer doing the purchasing
-dtePurchase (Date of purchase)

tblCustomerPurchaseDetail
-pkCustPurchaseDetailID primary key, autonumber
-fkCustPurchaseID foreign key to tblCustomerPurchase
-fkProductID foreign key to tblProducts
-unitprice (this value will be copied from product table in order to capture the price at the time of purchase, this will allow you the ability to change the prices in the product table as costs increase over time)
-Qty

(you would not store the total only the factors used to calculate the total)

You would probably have a series of table to capture services (i. e. crop spraying and harvesting) since you typically would not have a quantity but rather just a flat amount for the service.


If the products and services are related to an event then you would tie the tables to the event rather than directly to the customer.