Basic Access, I think

[leinad]

I am having trouble designing a seemingly simple database:

Maybe you need more details, but here is a typical relationship I need to set up and can't figure out:

A few home parties (like tupperware), with a table containing details of the party, the Primary Key being the unique name for the party . Table of customers, with all their contact information. Last name is good enough for the Primary Key, or maybe two fields combined (last name, first name). Some of them went to both parties. How do I create the relationships between the two tables when many people went to each party?

Better yet, when entering details for a new party, typing the last name will compare against the existing table of customers and populate or, if it isn't a match, I can fill it in manually.

I want to be able to come up with a report eventually of maybe who was present at which party, who bought a certain thing. The tables all have to be related and able to draw information from each other.

Is my question clear, and answerable?

Thanks

He is no fool who gives what he cannot keep to gain what he cannot lose.
- Jim Elliot

 

[willir]

Jim

Some things to review...

http://www.abcdataworks.com/developers.htm

or

http://r937.com/relational.html

Plus Microsoft's side...

http://support.microsoft.com/default.aspx?scid=kb;en-us;283878

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467

...Moving on

You want to track attendees and purchases. Here is a start - fairly simple...

tblCustomer
CusotmerID - primary key, autonumber
CustomerLN - last name, text 25
CustomerFN - first name, text 15
CustomerPhone - phone number, text 20
CustomerAddr - address, text 255
CustomerDOB - birthday
CustomerMemo - memo field, use for ticklers

tblParty
PartyID - primary key, autonumber
HostID - foreign key to Customer table
PartyDate
NumAttendees - numeric, small interger
RateSuccess - text

tblAttendees
PartyID - foreign key to Party table
HostID - foreign key to Customer table
Comments - memo field

This is a many-to-many relationship
- many people can attend a party
- a person can attend many parties

tblProduct
ProductCode - primary key, 12 to 18 character, perhaps catelog no.
ProductName - text, 25
CurrentPrice - currency
Comments - memo

tblOrder
OrderID - primary key, autonumber
PartyID - foreign key to Party table
HostID - foreign key to Customer table
ProductCode - foreign key to Product table
OrderQty - numeric, small interger
OrderPrice - currency


I used color to hopefully show how primary keys and foreign keys are linked.

After creating your tables, don't forget to create your relations using "Tool" -> "Relationships" - add your tables, click on a primary key and drag it to a foreign key; enable integrity.

Forms...
frmProduct - enter product info, single form

sbfrmOrder - enter order, contineous form

frmCustomer - enter customer info, single form, plus
- sbfrmOrder embedded, linked via CustomerID
- note: turn Product field from text field to combo box

sbfrmCustomer - last and first name, phone number, contineous form

frmParty - enter party info, single form, plus...
- Tab form object
- sbfrmCustomer embedded on one tab, link via PartyID
note: turn CustomerID field to combo box
- sbfrmOrder embedded on other tab, linked via PartyID


Anyway, this should give you some ideas. You will want to tweak it for your needs.

Richard

 

[lespaul]

This should actually read:

tblAttendees
PartyID - foreign key to Party table
AttendeeID - foreign key to Customer table (not HostID!)
Comments - memo field


Leslie

 

[willir]

Thanks Leslie - That cut and paste, especially after midnight get you every time.

 

[lespaul]

no problem!

Leslie

 

[leinad]

willi, lp,

First, thank you for your posts.

I have further questions:

I have started to build this according to your layout, but I have hit a snag:

What is meant by single as opposed to continuous forms?

Also, I successfully created sbFrmOrder, a sub form of frmProduct, but I am having trouble beyond that.

You say to link and embed sbFrmOrder to frmCustomer. I can't figutre out how to do that

Also, I managed to make two tabs for the frmParty form, but I can't figure out how to get data onto the tabs

I am trying to build this according to your specs before populating it with data, and I want to make sure it is built correctly

Thanks again,
-Daniel

He is no fool who gives what he cannot keep to gain what he cannot lose.
- Jim Elliot

 

[willir]

Daniel

What is meant by single as opposed to continuous forms?

First the basics.
A form has three views - form, datasheet and design view. You want to have the form open in design view.

A critical tool when designing a form is the "Properties" window - it gets in the way sometimes, but is essential when design and tweaking tables. ("View" -> "Properties")

Last, a form has properties for the entire form or record, properties for the specific fields and labels, and properties for any subforms. When in design view, you should see a horizontal and a vertical ruler. Click in the square box where the two rulers meet on the top left. The square should be black. The Properties window will display properties for the form / record set. Click on the "Format" tab of the properties window. The "Default View" can be set to "Single", "Contineous" and "Datasheet".

You say to link and embed sbFrmOrder to frmCustomer. I can't figutre out how to do that

Click on the database window, then from the menu, select "Tools" -> "Relationships". This tool is used to defining your relationships. Add your tables to the design. Click on the "primary key" in the parent table and drag it to the "foreign key" in the child table.

For example, from the tblCustomer, click on CustomerID and drag it to HostID on the tblParty. A window popups - click "Enforce referential integrity".

Now open a form in the design mode. If you only just created the relationship before adding the subform, you will have to set it manually. Select a field on the main part of the form and then click on the subform - you now can look at properties of the subform. Look at the "Data" tab in the subform. The two fields Link Child and Link Master Fields set the relationship. Click on one of the Link fields, and then click on the "..." command button to open the "Subform Field Linker" - point and click to set your two fields as appropriate.

Also, I managed to make two tabs for the frmParty form, but I can't figure out how to get data onto the tabs

Tab forms are tricky until you understand how they work. You can place an object on the main form, all pages of the tab control or on a specific page of the tab control.

Properties windows to the rescue...
Have the porperties window open and select the "Other" tab. Now select the tab control object, but not a tab - the name displayed in "Name" should be the name of the entire tab control. Now select a "tab" - the Name in the properties window will match the name of the tab - change the name in the properties window and this will change the name on the tab.

FIRST, select a tab as a target. Then select your subform object on the database window, and drag it to the tab object. The subform will be placed on the tab page object which is where you want it to be.

When would you want a control to be placed on the entire object (visible for all tabs) - a title lable perhaps.

Hope this answers your questions.

Richard

 

[leinad]

Hey, I appreciate the time you have put into this for my benefit. I will do this tomorrow -- it's past my bedtime. i just wanted to thank you for your trouble.
Daniel

He is no fool who gives what he cannot keep to gain what he cannot lose.
- Jim Elliot