Normalization broken...please help fix

[DumbWithData]

I am trying to create an invoice table. This table would include a list of
each employee that worked and their hours, equipment used and its hours, and
materials and their quantities. I know that it would be bad design to have
fields like: employee1, employee2, employee3, employee4, equipment1,
equipment2, etc., but I do not know how to do this otherwise. It seems that
I would need to break this into numerous tables, but I do not know the
relationships these should have…Follow up question: If I can get this to
work, I would also like to be able to make revisions to these invoices
without erasing the original. For example, ‘invoice 1’ would have 10 hours
for a particular employee. Later I decide to change this value, but would
not want to overwrite the previous invoice but instead create a revision…so
now there would be ‘invoice 1’ and ‘invoice 1 rev 1’…is this possible?

 

[Jeff Boyce]

The questions you've posed are fairly general ... the folks who volunteer
their time here tend to focus on providing specific suggestions to specific
issues.

One observation ... a relational database (e.g., Microsoft Access) does NOT
work well if your data is structured like a spreadsheet (e.g., Employee1,
Employee2, ....). Plan on learning about normalization.

Developing an application using Microsoft Access (or any other tool) has a
number of necessary learning curves you'll need to work your way up.

1. how the data need to be organized ... in Access, you need to understand
normalization.
2. how the tool do specific things ... tricks and tips specific to the tool
selected
3. how people work best ... graphical user interface design
4. how to put it all together ... application development process

If you neglect any of these, both you and the tool you select will
"struggle".

If you don't have the time to devote to learning all of these, consider
finding someone who has the time or has already learned these lessons.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[DumbWithData]

Jeff:

Next time I will try to be more specific: If I have a subform inside
of a main form, when I update or edit the primary key in the main form does
this automatically update/edit the contents of its related field in the
subform? For example, I am creating a new invoice with its primary key,
invoice number, being 001. When I enter this, will it also enter this number
in its related field in the subform? If not, is there a way I can do this?

Thank you in advance.

 

[John W. Vinson]

Jeff:

Next time I will try to be more specific: If I have a subform inside
of a main form, when I update or edit the primary key in the main form does
this automatically update/edit the contents of its related field in the
subform? For example, I am creating a new invoice with its primary key,
invoice number, being 001. When I enter this, will it also enter this number
in its related field in the subform? If not, is there a way I can do this?

If the InvoiceNumber is the master/child link field of the subform, it will be
inherited the instant you enter any data into the subform (not before).

Have you... ummm... tried it to see?

 

[DumbWithData]

Thank you John. I have not yet created a subform and thereby have not tried
to this, but now I understand the idea of 'normalization' of tables and how
forms with subforms can make this process uncumbersome.