Form Query

  • Thread starter Thread starter AKoodray
  • Start date Start date
A

AKoodray

I have reposted this from another group - if this is no
doable please let me know:

I have two tables with the following parameters:

Table 1
Acct
Jan
Feb
Mar (etc)

Table 2
Acct
Acct Type

The two tables are linked based on "Acct".
I would like to create a data input form that filters
based on "Acct Type" whereby I can have a master form that
has tabs for "Assets", Expenses, Revenues, Taxes... Each
tab would list all accounts of the prescribed "Acct Type".

I am having a tough time tieing the two tables together
ina workable input form. I tried using the filter method
but was unable to get the correct context of the
statement. I also tried creating a subform driven by a
query, however this allowed only a single record, rather
than a list, to be viewed.

Allan
 
-----Original Message-----
It's difficult to understand what you are trying to accomplish. However,
your Table 1 fields are defintely not correctly normalised, having a field
for each month of the year is most odd.

Maybe read about normalising as a start, there's heaps of links available on
the net...
eg. http://www.microsoft- accesssolutions.co.uk/1norm_form.htm

HTH
Sam



.
Actually, using months as fields would be quite common
Click to expand...
when working with financial data. As far as normalization
is comcerned, the tables are normalized, and in part, that
is what is createing the dilemna.

Allan
 
AKoodray said:
when working with financial data. As far as normalization
is comcerned, the tables are normalized, and in part, that
is what is createing the dilemna.
Click to expand...


Excuse me, Allen, but Sam has really pointed to the problem
here. The fundamental rules (normalization) of all
relational databases prohibit repetive fields (your month
fields) regardless of the use of the data. Another rule
states that each field must be atomic (have one and only one
value) and not a list of values or anything else that can be
decomposed into items with different meanings.

It looks like you're trying to apply spreadsheet concepts to
databases. Unfortunately, all that will do is cause you
problems such as the one you asked about above. With your
current structure, there just isn't a decent solution to
your problem. Once you apply the first three normal forms
to your data structure, this kind of problem just
dissappears and you can move on to making more productive
use of your data.
 
Alan, perhaps you could post some example actual data. Then we can suggest a
more productive table structure.

HTH
Sam
 
Thanks for the follow-up Sam, here is sample data from the
tables:

Table 1: (filed names followed by records)
CostCtr Acct Jan Feb Mar Apr
10 1234 100 150 25 75
10 1235 200 130 0 120
10 1236 300 220 250 215
11 1234 75 85 95 100
15 1235 25 65 85 50
15 1237 35 75 95 50

Table 2: (filed names followed by records)
CostCtr Desc CoNo
10 Acct'g 100
15 HR 200

Again, the concept would be to create a form that groups
by cost center number (single value only) for edit/input.

Allan
 
Back
Top