Form Query

[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

 

[Sam]

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

 

[AKoodray]

-----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

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

 

[Marshall Barton]

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.

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.

 

[Sam]

Alan, perhaps you could post some example actual data. Then we can suggest a
more productive table structure.

HTH
Sam