Newbie

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I have little experience with Access but I have played around with it a
little. I am trying to build a DB that stores claims I file with vendors. I
set up my tables and created a form. What I want to do on my form is enter
the vendor #, claim#, etc.. only once, then when I am entering the part
details that are associated with that one claim number I do have to repeat
the vendor#, claim#, etc. each time I enter a new part detail. Can this be
done? Doed this have to be done using relationships?

T.I.A.

Ed
 
Yes, and Yes.

I think you woulr really need three tables here.

One with Vendor Information

One with Master Claim Information (linked to vendor as a one-to many
relationship, with the vendor table being on the 'one' side and the claim
table being on the 'many' side.)

And one with Claim Detail Information (linked to the Claim Master table as a
one-to-many relationship, with the Master table being on the 'one' side and
the detail table being on the 'many' side.)


Once you get that basic structure built, and create the relationships, it
should be pretty easy to go from there.

This should be very similar to the Northwinds database. I don't currently
have it loaded, but it has a vendor table, then orders, then order details
(if I remember correctly).


Your form would have the vendor data, then maybe a subform with all the
claims listed. You might have a button next to the claims that would open
the claim details in a separate table.

Of you might have a form with the vendor and claim number, then a subform
with the details.

Lots of different ways to go here.

Get your structure 'normalized' then post back as you run into questions.

Good Luck,

Rick B


You need a separate table called PArtDetails, or similar.
I have little experience with Access but I have played around with it a
little. I am trying to build a DB that stores claims I file with vendors. I
set up my tables and created a form. What I want to do on my form is enter
the vendor #, claim#, etc.. only once, then when I am entering the part
details that are associated with that one claim number I do have to repeat
the vendor#, claim#, etc. each time I enter a new part detail. Can this be
done? Doed this have to be done using relationships?

T.I.A.

Ed
 
Hi Ed,

Yes, relationships are what it's all about and proper use of them solves
your problem. The issue is more in your data design and the establishment
of relationships than it is in form design.

Some comments follow that might be helpful to you.

From your post it would seem that the entities involved in your application
are Vendors, Claims and Parts. That is also the hierarchy. Suggested table
names and fields are given below. In order not to disturb the work you have
already done, you might start a new database using my suggestions and see
how it all works out.

tblVendor
Field Name type length Notes
VendorID Autonumber na Primary Key
VendorName text 50
VendorAdd1 text 50
VendorAdd2 text 50
VendorState text 2
VendorZip text 12
VendorPhone text 20
VendorFax text 20
VendorNote text 255 Notes about just
this vendor

tblClaim
ClaimID Autonumber Primary Key
VendorID Long Integer Foreign key
ClaimNumber text 50
ClaimDescrip text 255
ClaimNote text 255
ClaimOther text ?? Other fields
you need viz the Claims

tblPart
PartID Autonumber Primary Key
ClaimID Long Integer Foreign
key
PartNumber text 50
PartName text 100
PartDescrip text 255
PartNote text 255 Notes
about this specific part
PartOther text ?? Other
fields you need viz the parts

Bring up the tables in the Relationships window and establish relationships
by dragging a connection from tblVendor|VendorID to tblClaim|VendorID.
Enforce Referential Integrity and allow cascading deletes. Enforcing
referential integrity will prevent your entering a Claim until you have a
Vendor selected. Cascading deletes means that if you delete a vendor then
you'll delete all Claims and Parts associated with that Vendor.

Drag a connection between tblClaim|ClaimID and tblPart|ClaimID. Again,
enforce Referential Integrity and allow cascading deletes with the same
effects as described above.

At this point you're ready to design your forms. Once your forms are
designed, make sure that you remove all sight of the Autonumber (ID) fields.
They contribute very little to your understanding of what's happening and,
if you see them you're likely to believe they belong to you. They don't.
Their only purpose in life is to provide uniqueness for primary keys. Any
other use will lead to difficulties.

I suggest that you have a form dedicated to Vendors that shows everything in
the vendor table so that you can make new entries or edit data. That will
be kind of a maintenance form for now. Later you may alter your application
so that you're doing all of your work from another form and only call this
form when you need it. Design that Vendor form now and enter the vendor
name and some data for a few vendors.

Next design a form based on tblClaim with the same considerations as above
for tblVendor but don't enter any claims.

Same for tblPart and don't enter any parts.

Design your main working form: Design it based on tblVendor but only show
VendorName at the top of the detail section of the form. Now show
header/footer and, with wizards enabled, click the combobox on the toolbox
and create a combobox in the Header section of your form. The wizard will
ask you what you want the combobox to do. You want to find/goto a record
based on your selection. Change the label to "Find Vendor" and change the
combobox name to "cboFindVendor". In the data section of its properties
set Limit to List to "Yes". Since you'll want the Vendor names in
alphabetic order, click the ellipsis (...) at the far right of the RowSource
property on the Data tab. That will bring up the QBE grid. Click Show
Tables and select tblVendor. Drag VendorName into the first box in the
lower section. Click Sort and choose Alpha. Save the query and answer Yes
to saving it. Answer no/cancel to saving it in the query window. In the
format properties you want to set Autoexpand on.

Notice that setting Limit to List to Yes will prevent your entering a vendor
name that doesn't already exist. That prevents typos causing problems.
You'll need research Help on Limit to List to see what it can do for you.
With Autoexpand on the combobox will try to find your target name as you
type. If you keep typing beyond the point where it tracks you'll get a
Limit to List error event. In your code for that Error event I would open
your Vendor Maintenance Form in Dialog mode. Dialog mode means that you
can't open any other form in the application until you dismiss the Dialog
window. Fill in the Vendor information as much or as little as you like and
dismiss the form. After doing all that you need to do in the Limit to List
error code, return to your form. If you did everything you needed to do the
Vendor Name will now appear in the VendorName text box in the detail
section.

I've been getting carried away here and stealing all your fun. You'll learn
a lot as you run into and solve each problem. I suggest that you stretch
the detail section of this form down and out and that you insert a subform
based on a stripped down version of the claim form, with its display mode
set to continuous forms. Somewhere in that subform you'll want to have yet
another subform that's a stripped down version of your Part form, also
continuous forms.

The most important aspect is the data design; the tables and their
relationships. Get that right and the application will be easy to develop
and enhance.

Post back if you have problems.

HTH
 
Hey guys I need a little help again here. I have successfully created the
tables and forms below. Here is my question. I added a text box that
calculates from two other fields =[Po Price]*[QTY]. This works fine. I want
to add a Grand Total of this field. How can I do this?

T.I.A.

Ed
 
Hey guys I need a little help again here. I have successfully created the
tables and forms below. Here is my question. I added a text box that
calculates from two other fields =[Po Price]*[QTY]. This works fine. I want
to add a Grand Total of this field. How can I do this?
Click to expand...

Put a textbox on the Form's Footer, and set its Control Source to

=Sum([PO Price]*[QTY])
 
Back
Top