Linking Tables- Posibble BOM problem??

  • Thread starter Thread starter Nick M via AccessMonster.com
  • Start date Start date
N

Nick M via AccessMonster.com

I am trying to put together a user friendly work order form together. The
form will need some information such as date, work to be done, and some
other information. It will also have a list of the items required to
complete the job. I have one table (Work_Order) set up to hold all the
information about the order, including room for up to 10 different items
that will be purchased that will be keyed in by their 'item #'.

In a report I want that number to reference the second table
(Component_List) that holds all of the information about each individual
item (description, cost, etc..).

I have been advised that this could be a bill of materials problem, and
have read a lot of posts on the matter in the last several days, and still
can't produce results. I am trying to avoid code if possible because I
don't want to be the only one that is able to maintain the database.

I currently do these work orders in Excel by using a VLOOKUP to find the
description and cost in a separate sheet. I was hoping there may be some
kind of similar function I could use in Access.

At this point I'll take any help I can get, including redesigning the
tables from scratch if necessary.

Thanks,

Nick M
 
Base your report on a query that links the two tables
eg (not tested)
SELECT table1.[item #], table2.description, table2.cost
FROM table1 INNER JOIN table2 On table1.[item #] = table2.[item #]

in the WHERE clause specify what records you want to appear

HTH
 
Hi Nick,

I would recommend further normalizing the database such that you have one
table of work order numbers, one table of items (or components), and a third
that relates the two.

This will allow each work order to have an unlimited number of items, and at
the same time avoid storing a bunch of nulls for those Work Orders that only
have one or two items.

Perhaps the biggest benefit though will be that it will be much easier to do
counts and summaries on the items in your reports (which may be the billing
problem that you mentioned) because all of the cost data will be in one field
(column).

To set this up, you would set up your Work Order Table, with a Primary Key
on the Work Order Field, then you would create your item table, with a
primary key on Item Number, then finally create a third table containing a
field for Work Order Number and Item Number (primary key on the combination
of the two), with any other necessary fields such as quantity, date, etc.
Make sure to define the relationships between these tables in the
relationships window, and check the box to enforce referential integrity.

Setting up the user form is very easy, just base the main form on the Work
Order Table. Then, create a second form (which will be a subform) based on a
join between the Items table and your third (join) table. The data source
for the subform must include the Work Order field from the join table, but
the field does not need to be added to the form itself.

Finally, add the subform to the main form by clicking on the subform tool
and dragging a box on the form. Access will ask you what field to use to
join the two. Just select the two Work Order fields and it will be added.
You can then resize and reposition the subform as you like.

When you view the form, any item numbers entered in the subform will
automatically be added to the join table (be sure though that the item number
field that they are entering the data in is the one from the join table, not
the items table), along with the work order number. The work order number
will automatically be added based on the form relationship that you have
defined.

Setting up your report will be essentially the same, a report based on the
work order table, with a subreport based on the join table and any associated
lookup data from the items table.

Hopefully that will help. Post back if you have any questions or comments.

-Ted Allen
 
Ted,

Thanks so much for the help. I've been going to nuts over this because I
knew there had to be a reasonably easy way. The only question I have is
this: Is there an easy way to jump from the sub form back to the main for
with the keyboard, or do you have to click from one to the other?

Thanks again.

Nick M
 
Hi Nick,

The subform will be embedded in the main form, so to the user it will look
like one form. I've never really looked into the keyboard navigation between
form and subform, our users usually tend to just click wherever they want to
enter data (our office does project tracking, so we're generally doing little
updates here and there vs pure data entry where all data is entered at once).


In the design view of the forms, you can set the tab order of the fields,
and what you want access to do when the user tabs off of the last field, but
I'm not sure if you can tab from the main form straight into the subform or
not.

I'm sure that you could do it with a short bit of code if necessary, but I
know that you wanted to avoid that. You may want to post that question in
the forms group if it it's important to you and you aren't able to get it
using the tab order (or maybe someone else will read this that will have the
answer).

-Ted Allen
 
Try using Control+Tab to move out of the subform. Also, Control+Shift+Tab will
move back out of the subform
 
Thanks to all of you....I've finally got it all figured out, until next
time anyway. Very much appreciated.
 
Back
Top