Calculating Fields within a Table - HEEELLPP!! I am new to this!

[Solaron]

This may be hard to explain, but here goes.

I am creating a simple database which contains a few tables and nothing more. They are all linked together, so in access xp there is a lovely function that when you load up the table (my main table in this one), there is a little cross to the left of a record where you can see all related records from another linked table! Beautiful! it's exactly what i want!!!

Now, here's my problem:

I either want to know how to make a FORM look just like that, so BOTH sets of records are in table form, or:

How on earth can you create a field within a table that calculates values based upon different fields within that table?!?! bit of a mouthful - sorry. So in the table i have one value, and then the same table there is a different field with a different value, i want to be able to make a third field in that same table that will calculate the difference between them.
I know how to do it in forms, but if i use a form, i cant get that groovy look of the tables that xp boasts.

Cheers in advance,

Chris

 

[Beerbrewer]

You can create a subform within your mainform to get alle the records as you want. "The wizard will lead you through the process".
Furthermore creating a field in a table based on other fields within that table is not a proper use of it. It is better to create a query that calculates the difference you want. This way you do not store irrelevant data (data that is derrived from and dependent on other existing data). You can show the result of that query in a subform.

Hope this helps

 

[Solaron]

Cheers dude, although doesn't 100% solve my problem.

I had a feeling that calculating values within tables is 'bad manners' for access, using a query seems simple enough although access help doesn't actually tell me how to perform calculations in queries!

But i was hoping to keep the layout of the table view in access. The database in question is for a vending company where they list their Sites, and linked to those are the machines within the site.

All this information needs to be on one page like a spreadsheet but with excel you cant lookup values (as far as i know) and ammend tables. So the view where all the sites are listed down the page, and being able to press a little cross to the left of it to bring up a subform with the machines is ideal.

So coming back to my original problem, on the machines 'sub-table' there is a 'vend open' and 'vend close', but i need it to generate a 'units sold' from those two values without manually using a calculator as it wastes time.

Hope this makes it clearer,

cheers for the speedy reply!

 

[Beerbrewer]

As always there are multiple solutions. I stich with my former conclusion that using a table is none. Stille I do not understand WHY you prefer a table look on screen. You should consider this!

Working with a single subform is a simple solution that is userfriendly. You can use the record navigation buttons to lookup records, etc.
Another more complicate solutions is to use two subforms in a mainform that is not based on a table and link those two as parent and child. Then you can create both tables in datasheet ("spreadsheet") view and when you move to a different site also the machines will change.

I hope I have understood your problem. It is not clear to me if you have considered and are familiar with the normal and usual features. If not you may mail me your .mdb and maybe I can show you what I mean.

Good Luck!

 

[Solaron]

Cheers, i can see your point.

the problem is is that this database isn't for me, it's for my parents and i've been told which layout they'd prefer - hence the trouble. Otherwise i would just create a normal standard database with little complication.

Feel free to check out my .mdb. E-mail me so i can e-mail you back with the database. (as i cant see your address without u e-mailing me first!)

I haven't been working on it for long, but to be honest, i haven't made many databases before, only an a-level project.

Cheers again for your help

My E-mail is lanky_bowl@yahoo.com

 

[Pat Hartman]

To implement this type of view, you need two forms. If the data is "narrow" so that the two forms could fit side-by-side or if the parental units won't mind the top half of the form being the parent table and the bottom half being the child table you can create a form with two subforms. In the current event of the parent form, you would need to set the recordsource of the child form and do a requery.

The other alternative is to "pop up" the child form. For this I would use the double click event of some field in the parent form. In this case you would use the where argument of the OpenForm Method.

I believe that both types of forms are shown in solutions.mdb and frmSamp97.mdb.

 

[Solaron]

Cheers guys, i've managed to get what i wanted now - all works good.