Help! I know this is long.

[Bandit]

I have been monitoring, reading, asking questions and learning a lot on this
site for several months now. I am still stuck on my “main problem†and I
just can’t see past the tip of my nose on this one. I know it is probably so
simple, but I am too frustrated to see the simple problems here. I know this
is long, but I need help over this hump. If you want to email off site for
mentoring/assistance I am open, just please someone help me out here.

I have the following tables:
Purchase Order Table:

ID (primary key), POID, PO#, SupplierID, Supplier Salesman, EmployeeID,
ShippingMethodID, Remarks, Order Date

Purchase Order Sub form:

ID(primary key), POID, PO#, Part Name, Part Number, Quantity, Unit Price,
Tail Number, Condition

Now I may be way off base here so please feel free to tell me what I am
doing wrong or what I should be doing.

I created a Purchas order form and a purchase order subform. Then used
those two forms to create a Purchase Order Form as a main form and the
Purchase Order sub form as the sub form. I dragged and dropped to create the
sub form. How should my relationships be established? And then, using the
Inventory Management Database Template on MS Office, how do I get the
Purchase Order Report to print as it does in the example under Inventory
Management database? I want it to look exactly the same as the template,
except with the info above.

Please someone help me on this one. Everything else I have done thus far is
working pretty well, for an amateur at any rate. But this one has me stumped
and this is the most important at this point.

If you want to email off line for one on one mentoring, just let me know.

Thanks for any and all help.

 

[Allen Browne]

There are several parts to your question: the table structure, the form and
subform, and then the report.

The tables don't look quite right. Your Purchase Order table has fields:
ID primary key Is this an autonumber?
POID Is this a unique required field? Why have this as well as ID?
PO# Is this a unique required field? Why have this as well as ID?

Your Purchase Order Detail table has fields:
ID primary key AutoNumber?
POID Which field does this match in the [Purchase Order] table?
PO# Which field does this match in the [Purchase Order] table?

What you would normally need in the PO table would be:
POID primary key
and in PODetail table:
PODetailID AutoNumber primary key
POID Number matches PO.POID
In the properites for the POID field in PODetail, set the Required property
to Yes (so you can have a line item that does not belong to any purchase
order.) Also, clear the Default Value property if Access assigned a zero
(since defaulting to POID zero is just plain wrong.)

Once you have the fields right, you need to create a relationship between
the 2 tables. Relationships is on the Tools menu in Access 2003 and earlier,
or on the Database Tools tab of the ribbon on A2007. In the Relationships
window, drag PODetail.POID and drop onto PO.POID. Check the box for
referential integrity.

Once you have the tables right, create the form and subform. Since the
relationship is set up, Access should automatically set the Link Master
Fields and Link Child Fields so the correct line items show up in the
subform for the purchase order in the main form.

I don't have the template you refer to, but essentially you create a query
using both tables (and any others you need), and use it as the Record Source
for your report.

Perhaps it would be appropriate to have a table of parts, each with a unique
PartID and PartName. Your PODetail table would then have a PartID field
instead of the [Part Name] and [Part Number]. This would ensure the parts
are valid, avoid data entry errors, and avoid the situation where you table
has a part name that does not match the part number.