Daily Quantites - Need Sub -Subform?

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

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

"1Adam12" u44422@uwe wrote in message news:861dabce3acee@uwe...
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

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.

--
NTC


"1Adam12" wrote:

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

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.
--
KARL DEWEY
Build a little - Test a little


"1Adam12" wrote:

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

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

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

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

NetworkTrade wrote:
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

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

KARL DEWEY wrote:
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

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

"1Adam12" u44422@uwe wrote in message news:861ec3e94eb9b@uwe...
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

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

Thanks Larry

Larry Daugherty wrote:
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]
Thanks in advance,
Adam

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

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

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





--
Bob Quintal

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