Daily Quantites - Need Sub -Subform?

[1Adam12]

Please help Access guru’s around the world!

I have created a form with a list of Subcontractors from a table, with a
subform tied to another table with the Subcontractors line items in it. The
tables use no primary keys as the line items in the table are doubled with
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

 

[Larry Daugherty]

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

 

[NetworkTrade]

well you initially lost me in emphasis on the primary key; and then switching
to "want to store daily quantities"

ignoring the primary key issue in presuming that is ok;
the classic DB norm is one does not store a calculated value

If your subform has numbers to be summed - then in the subform's footer you
would put an unbound text box and apply the correct code/formula to sum
values appearing in the subform. This would be the normal way to sum a value
in a subform.

If this value must be stored, and you describe it as "daily" then it implies
you would store a different value every day; apply a date stamp; and grow
this table daily for every record. In this case you have another table, and
another subform (not necesarily a sub sub). When you key thru the line items
at some point you can trigger math and date stamp to go into this new 3rd
table.

 

[KARL DEWEY]

Did I miss something or do you only have two tables - Subcontractors & line
items?
To get daily quanities you need a third table associated with the line items
table and Subcontractors table unless the line items are unique to each
Subcontractor.

 

[1Adam12]

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 I do this?

On the Primary key: initially I wanted to set up a primary key of the top
subcontractor code as the Primary key in the first table (Parent) and the
Line items for the Primary key in the Child table, but the line items start
over with each Subcontractor. 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 functionally. It looks like the candidate field may just work out best.
Do you have any pointers that might help?

Thanks,
Adam

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

 

[1Adam12]

Thanks for your reply!

I hadn't gotten that far in explaining where I was planning to go with the
summed quantity to date. I was hoping to solve the mystery third table and
how to effectively do this before I could try out what I was going to do. I
was going to have a field in the second table - the Line Item table - sync
to a query that sums the quantity to date from the third table. Then again,
this table was going to be under the Line item table I was thinking (hence -
do I need to create a sub subform, and how would I get it to work properly?)
and can you Query into a table that is really a Child to the table it's going
to populate? I like what you're saying, just not far enough a long to try it
out!

In the end though - I have learned that Access is not artifical intelligence,
and that too many questions bog it down. I've created queries that do
essentially what I need them to do in this case, and when I had in there the
Line Items and summed one line item, it's not picking it up as one sum, but
treating each line as individual records, even though it's just a repeated
line number. Any ideas? Believe me, I did my due diligience in trying to find
a solution through Access help adn other forums until IO decided to raise
thsi white flag. This is really another question entirely, but if your
willing to help, I would much appreciate it.

Thanks again,
Adam

well you initially lost me in emphasis on the primary key; and then switching
to "want to store daily quantities"

ignoring the primary key issue in presuming that is ok;
the classic DB norm is one does not store a calculated value

If your subform has numbers to be summed - then in the subform's footer you
would put an unbound text box and apply the correct code/formula to sum
values appearing in the subform. This would be the normal way to sum a value
in a subform.

If this value must be stored, and you describe it as "daily" then it implies
you would store a different value every day; apply a date stamp; and grow
this table daily for every record. In this case you have another table, and
another subform (not necesarily a sub sub). When you key thru the line items
at some point you can trigger math and date stamp to go into this new 3rd
table.

Please help Access guru’s around the world!

[quoted text clipped - 13 lines]

Thanks in advance,
Adam

 

[1Adam12]

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?
To get daily quanities you need a third table associated with the line items
table and Subcontractors table unless the line items are unique to each
Subcontractor.

Please help Access guru’s around the world!

[quoted text clipped - 13 lines]

Thanks in advance,
Adam

 

[Larry Daugherty]

You might google on "Normalize Data" to turn up a bunch of information
and probably links to tutorials.

For Access newbies I recommend lurking:

microsoft.public.access.gettingstarted and
microsoft.public.access..tablesdesign

also, a visit to www.mvps.org/access would be in order to browse
around and find other relevant info and links.

Welcome to Access development.

HTH
--
-Larry-
--

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 I do this?

On the Primary key: initially I wanted to set up a primary key of the top
subcontractor code as the Primary key in the first table (Parent) and the
Line items for the Primary key in the Child table, but the line items start
over with each Subcontractor. 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 functionally. It looks like the candidate field may just work out best.
Do you have any pointers that might help?

Thanks,
Adam

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

 

[1Adam12]

Thanks Larry

You might google on "Normalize Data" to turn up a bunch of information
and probably links to tutorials.

For Access newbies I recommend lurking:

microsoft.public.access.gettingstarted and
microsoft.public.access..tablesdesign

also, a visit to www.mvps.org/access would be in order to browse
around and find other relevant info and links.

Welcome to Access development.

HTH

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 I do this?

[quoted text clipped - 37 lines]

 

[Bob Quintal]

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
I do this?

On the Primary key: initially I wanted to set up a primary key of
the top subcontractor code as the Primary key in the first table
(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
functionally. It looks like the candidate field may just work out
best. Do you have any pointers that might help?

Thanks,
Adam

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

 

[1Adam12]

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

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]

 

[1Adam12]

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

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]

 

[Bob Quintal]

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.

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

 

[1Adam12 via AccessMonster.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

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

 

[1Adam12 via AccessMonster.com]

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

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