Daily Quantites - Need Sub -Subform?

This is great! It worked for the Line Item table, The Parent's Primary key is
just the Subcontractor, then the Child Primary key is the Subcontractor and
the Line item, but a third table, built to house future daily quantities is
being difficult. As attempt to do this, it violates referentail integrity if
I try to join the two to the two corresponding rows in the third table and
gives me a no unique code for the referenced field error if I join any one
off the Line item table to the third table. Any ideas?

Thanks,
Adam

Bob Quintal wrote:
This 3rd Normal Form is new lingo to me. I apologize for my lack
of knowledge in this, is this like saying a sub-subform? How would
[quoted text clipped - 4 lines]
(Parent) and the Line items for the Primary key in the Child
table, but the line items start over with each Subcontractor.

Then your primary key is made up of two fields, the subcontractor
code and the line items number.

In design view for your child table, click the record selector
column for one of the fields, then ctrl-click the delector(s)
additional field(s). Once all are selected, click the key icon in
the table design toolbar.

I've
thought about creating a seperate table for each Subcontractor to
overcome this, but then I can't set up a form to work
[quoted text clipped - 35 lines]
Thanks in advance,
Adam

I figured it out adn the fix was made. As far as normalizing the Database,
I've got the first tow levels good but will need to see if I did the third
level correctly. Thanks again

1Adam12 wrote:
I was wondering if this is the way it would work. Create a third table to
house the data but how do I get it all to talk to eachother? I find this
third step a bit perplexing and complicated - I can get two deep, but how do
you create a form that will populate daily quantities for each line item? I
appreciate your help in this all-mighty guru's...

Adam

Did I miss something or do you only have two tables - Subcontractors & line
items?
[quoted text clipped - 6 lines]
Thanks in advance,
Adam

"1Adam12" u44422@uwe wrote in news:861feed482c51@uwe:

This is great! It worked for the Line Item table, The Parent's
Primary key is just the Subcontractor, then the Child Primary key
is the Subcontractor and the Line item, but a third table, built
to house future daily quantities is being difficult. As attempt
to do this, it violates referentail integrity if I try to join the
two to the two corresponding rows in the third table and gives me
a no unique code for the referenced field error if I join any one
off the Line item table to the third table. Any ideas?

Thanks,
Adam

Your daily entries table would need a third field in the primary
key, probably the date of the entry.





Bob Quintal wrote:
This 3rd Normal Form is new lingo to me. I apologize for my lack
of knowledge in this, is this like saying a sub-subform? How
would
[quoted text clipped - 4 lines]
(Parent) and the Line items for the Primary key in the Child
table, but the line items start over with each Subcontractor.

Then your primary key is made up of two fields, the subcontractor
code and the line items number.

In design view for your child table, click the record selector
column for one of the fields, then ctrl-click the delector(s)
additional field(s). Once all are selected, click the key icon in
the table design toolbar.

I've
thought about creating a seperate table for each Subcontractor
to overcome this, but then I can't set up a form to work
[quoted text clipped - 35 lines]
Thanks in advance,
Adam






--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

I tried that but it's a null value. It's got to be the Date though, it's the
only logical thing, especially because I'm trying to normalize everything,
and this is the unique element for the table, besides the Daily quantity.
Thanks for your help Bob, I appreciate it.
Adam

Bob Quintal wrote:
This is great! It worked for the Line Item table, The Parent's
Primary key is just the Subcontractor, then the Child Primary key
[quoted text clipped - 7 lines]
Thanks,
Adam

Your daily entries table would need a third field in the primary
key, probably the date of the entry.

This 3rd Normal Form is new lingo to me. I apologize for my lack
of knowledge in this, is this like saying a sub-subform? How
[quoted text clipped - 17 lines]
Thanks in advance,
Adam


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200806/1

Larry,

I'm getting a lot of great help here on this post, overwhelmingly so. I've
been studying up on Normalization and have a couple questions about this,
Primary keys, and advice from others on this post:

1. In attmepts to give everything a Primary key, I'm accomplished this for
the first two Normal forms (1NF and 2NF) but wonder if advice given on how to
set a Primary key for the second form let's it remain as the 2NF. This is the
table where I store Line items, and because the Lines repeat with the
addition of every subcontractor, I made the Subcontractor code (SubWBS) and
the Line item (LineItem) fields Primary keys in order to allow this table to
have a (functional?) Primary key. Is this still 2NF if I'm using SubWBS as a
Primary key if it has a many to one relationship to the Parent table that
also uses the Subcontractor code (WBS) as a Primary key as well?

2. Now, for the 3NF, I need to set a Primary key here as well, but the only
logical place to set it would seem to be the Date. Now, I haven't been able
to steer around it's being a null value, or even if it's possible, but
wouldn't there be an issue once I duplicate the Date for several different
codes that occur on the same day? If I index this field, would it still give
me the same power? What risk am I taking here? I'm not going to a fourth
level with this form.

3. My overall objective with this form would be to search for the particular
Line item in the appropriate Subcontractor, and then add a new record to the
daily quantity table by first selecting a date, then entering it into the
Daily form; then either adding a new date, going back to the main form, or
undoing the operation. Am I on the right track here?

Thanks, you all are going straight to heaven on this one!
Adam

Larry Daugherty wrote:
BTW - Your data should be normalized to 3rd Normal Form and you should
assign a Primary Key to each Table. You can use Autonumber surrogate
keys or you can select from candidate fields.

You must use Primary Keys if you expect to build intelligent
applications on your data. Proper data design (AKA schema - the
Tables and Relationships) is the foundation of a good application.

HTH
Please help Access guru's around the world!

[quoted text clipped - 3 lines]
each new subcontractor (1, 2, 3, etc.). The subform looks great in that even
without primary keys, I'm able to scroll around the form and only
find those
line items from the sub form that are associated with each sub (50% there)
but now I want to store daily quantities as they occur to the line items.

I am unable to take the form to the third level - daily quantities -
and am
wondering if this needs a sub-subform to complete and how to do it? I'm gonna
need to see the project quantity to date on the subform. I'm hoping
one of
you is saying "this is easy, is he kidding?"

Thanks in advance,
Adam

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200806/1