Complicated database

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I am attempting to create a form that will update a table
in a specific way..

I'm attempting to create a form for a job.. the job will
be assigned an ID number. We may work on one or several
items.

How do I create a Form where I can enter one or many items
so that it will update to the table correctly (so that
they all fall under the same Job ID number?) The ID
number would need to repeat for as many items as are
entered on the form, and then move on to the next number
when a new form is started.

Please help!
Thanks
 
Jeff said:
I am attempting to create a form that will update a table
in a specific way..

I'm attempting to create a form for a job.. the job will
be assigned an ID number. We may work on one or several
items.

How do I create a Form where I can enter one or many items
so that it will update to the table correctly (so that
they all fall under the same Job ID number?) The ID
number would need to repeat for as many items as are
entered on the form, and then move on to the next number
when a new form is started.

Please help!
Thanks
Click to expand...

If I understand you correctly, what you want is to enter the ID for each
new record based on the last record, but allow the user to edit the ID if
they need to work with a new ID. Is that correct?

It sounds like you are on the right path, but just to check. Will this
table commonly have duplicate data in fields other than the ID field? We
want to avoid situations where we may use a Social Security number to
identify someone and then record various events about different people with
different Social Security numbers and repeat things like their address and
phone number on each record.
 
What we are doing is we issue work orders that require
that either one or several skus will all be on. For
example.. work order #1 may have item #333, #444, & #555.
All are associated with work order #1.

I am attempting to create a Form that will transfer
several lines of data to one Table. I only know how to
create a Form that will transfer to one line on that
table, I need to be able to create one or several lines.
The work order # will need to remain constant for as many
skus as are on that work order and change the next time
someone creates one.

I hope this is clear.

Thank you all
 
Jeff said:
What we are doing is we issue work orders that require
that either one or several skus will all be on. For
example.. work order #1 may have item #333, #444, & #555.
All are associated with work order #1.

I am attempting to create a Form that will transfer
several lines of data to one Table. I only know how to
create a Form that will transfer to one line on that
table, I need to be able to create one or several lines.
The work order # will need to remain constant for as many
skus as are on that work order and change the next time
someone creates one.

I hope this is clear.

Thank you all
Click to expand...

Depending on the table design, I would use a form/subform or a datasheet
view form.

The form/subform would require two tables, one for the Work Order number
and any additional information about that work order, like date, who did it,
completed or not etc. and one table for the SKU numbers and any data about
them, such as what the product is, it's cost color etc. The form would list
the Work Order number and any additional related information you wish to
show or input. The subform would be the SKU numbers along with any
additional related to the number that you wish to input or display. The
subform would be a datasheet view so it will show as many SKUs as you like.
You might make it look a little like this:




Work Order # 1234564
____________________________________

SKU Number(s)
____________________________________
21321
5466654
57545
4545456
455656
5456


End of form.......

A data sheet view would look like:
Work Order SKU
1234564 1321
1234564 5466654
1234564 57545
1234564 4545456
1234564 455656
1234564 5456
 
Hi Jeff,

It's fairly simple, not complicated at all. |:-)

You need to organize your data in two tables: tblJob and tblJobItem.

tblJob would have information that relates to the job only, not the items.

tblJobItems would have its own primary key and a long foreign key that's the
primary key of tblJob. data about this instance of this item in this job
goes into this table.

In the relationships window draw a line to connect the Primary Key of tblJob
to the foreign key in tblJobItem. Enforce Referential Integrity and allow
cascading deletes.

The most intuitive way to represent the data is to base a form on tblJob and
insert a subform that's based on tblJobItem.

HTH
 
Back
Top