Confused on form auto-poulate

[kavarin]

My database is fairly simple and consists of 3 forms for the entire process:
1. Client information
User inputs - file # (primary key), last name, first name
2. File Sumarry - conatains all other fields, with the exception of the 3rd forms fields
3. Concessions and amounts
What I thought was the file # to be the primary key for each table and to be used as the joining property
What I am looking to do is have the user input the file # on the first form and have that number auto-poulate the other tables/forms correctly. But I have been unsuccessful. When I bring up the 2nd and 3rd forms, the fields are empty but the underlying tables have the data in them. Help?

 

[kavarin]

I am also confused on the "normalization" concept.
I have several other table that hold lookup values that are/could be used in multiple records (State name, file status, etc.)
Is that the correct line of thought for normalization?

 

[David R]

There is an Access file that may help with your first question: "Synchronize records between two forms". However it sounds you could also possibly use a form/subform structure, so you can see many subrecords dealing with the same main record. The synchronizing forms is more a way to get several forms all working with the same record in series.

For your second question, normalization says that anything to be stored more than once should be in its own table. This should be balanced against the processing overhead of opening lots of little lookup tables - if a field has three possibilities and they will neverchange, then I'd use a Value List combo box.
Lookup tables that never change and are used only for referencing things like state names, file types, etc, can also be put on the front-end of a front-end/back-end split database. Then you have them locally and accessing them will be faster than pulling them in from the back end.

Hopefully that helps.
David R

 

[kavarin]

I am wondering if subfomrs are appropriate in the manner you suggested, as each record is unique and would involve only a SINGLE subrecord.
Each sub-record is simply an account of certain rebates that are attached to a file. These rebates are based on an individual, file-by-file basis.
Your thoughts?

 

[Pat Hartman]

File 3 should have Many rows for each row of File 2. Each concession and amount should have their own row. They should not be stored as a series of columns.

 

[kavarin]

So you're saying that the way I have it now (concessions as a separate table linked via file numbere) is incorrect and I should have it where every single instance of a concession should be its own record (a file number coulf have 6 or 7 different records associated wiith concessions)?

 

[David R]

I am unclear what Concessions are in your database/line of work, but yes, if one FileNo can have several Concessions, then they should each have their own line in the third table. Something like:

PK | FileNo | Concession | Amount
----------------------------------
64    1233   <something>   $45,000
65    1233   <something2>  $12,100
66    45677  <something3>  $ 7,000
67    45677  <something6>  $98,780
68    45677  <something7>  $   500

However if Concession is a 'type', or something you pull from a list, then store it separately and just store an ID # for the type in this table. I was approaching it from Concession being a text description of what the result was.

Your table1 and table2 structure leads me to believe that one client can have several files, possibly. If so is File # the proper PK for table1? Perhaps I'm misunderstanding.

HTH,
David R

 

[kavarin]

The basis of this system is an Escrow tracking system.
Each file (escrow#) is unique and the PK for my Details, Clients, and Concessions table.
Details is working OK, but "concessions" is our terminology for builder rebates, construction upgrades, etc.
It is possible for each file to have up to 6 different types of concessions, so I have created a table for it with the following fileds:
con1type (lookup from ConType table)
con1amt (currency field)
Con2type (lookup from same ConType table)
con2amt (currency field).........so on and so forth
The Concession table is only storing up to 6 different currency amounts (if I understand lookup properties correctly), but I am having difficuly with the form created for this table not retrieving the appropriate data based on the escrow# in the main form

When the nausea and headaches from trying to decipher that goes away...please lend me your thouoghts

 

[vangogh228]

See if this answers your question:
http://www.access-programmers.co.uk/ubb/Forum1/HTML/007967.html

If so, be sure to thank Jenn.