ACCESS TABLES FOR NEW SYSTEM 2

[696796]

I have been stuck on this for a few days now and its really starting to bother me!

I have the following tables,
Issue, Owner, Area, Priority, Part, Supplier. I need to relate these so that
One issue has one part - which can have many suppliers for one part.
Each issue has only one area, priority, part, one owner etc.
is it possible to have a function to clock repeating issues?
the main problem is how to join these tables, and what, if any new tables to add?(eg, joiner many-many tables)

Any help v.much apreciated

Alex Marchant

 

[PHV]

You may take a look here:

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

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Alex

the main problem is how to join these tables, and what, if any new tables to add?(eg, joiner many-many tables)

PHV has pointed you to an excellent document that covers the theory - a good read.

Here is something to get you started...

tblOwner
OwnerID - primary key
OwnerLN - last name
OwnerFN - first name

tblArea
AreaID - primary key (can also use AreaCode)
AreaDesc - decription of area

Priority - can also use a value list in a combo box
PriorityCode - primary key

tblPart
PartCode - primary key
PartDesc - descirption of part

tblSupplier
Supplierid
SupplierName

tblPartSupplier
PartCode - foreign key to Parts Table
SupplierID - foreign key to Supplier table
Price

Primary key = PartCode + SupplierID

Discussion: You indicated a many to many relationship for parts and supplier.
A supplier can provide many parts
A part can be provided by many suppliers

tblIssue
This is the one table I am unsure of its purpose. Although one issue may have one part, do issues repeat themesleves? First kick at the can...

is it possible to have a function to clock repeating issues?

tblIssue
IssueID - primary key
IssueName

tblIssueOccurance
IssueID - foreign key to Issue table
IssueDate
OwnerID - foreign key to Owner
PartID - foreign key to Parts
SupplierID - foreign key to Supplier
PriorityCode (should this be on the Issue table ?)

Primary key = IssueID + IssueDate

Discussion:
Are you tracking problems or issues that require a part, or is this a quality check database? Regardless, it sounds like you need two tables for Issues - One to define the issue, the other to capture when the Issue occurs.

Richard

 

[696796]

Hi richard, thanks for the detailed reply. I'm tracking issues that require a part - so basically what happens is a part goes wrong, and then the information about this part, the area, priority(canbuild/cant build), description, Date first occured, date owner assigned, line station, LINE STOP TIME and status of issue(open/closed).

the tables you suggested are very helpful.

I need to create a form to show details from many of these tables, i did an autoform just to see if it would work and on some fields i cant enter input due to :-

"join key of issue not in recordset"

not sure what to do here

 

[willir]

696796

"join key of issue not in recordset"

Suggests you have a data problem.

Let step back a bit.

The process I follow when designing a database is to...
a) Ask what reports / information do I need
b) Identify how and what data I am going to collect to produce the information. This part I work out on paper.
c) Design my tables to collect the data and normalize them.
d) Enter test data and using queries, make sure I can retrieve the information. This step looks at providing the objectives in (a) using the design in (c).

Then I delete my test data, and establish my relationships. I suspect others may establish their relationships in (c) which is fine too.

Then I build my forms.

Have you established your relationship using the Relationship tool provided by Access? From the database window, from the menu, select "Tools" -> "Relationships". Paul Litwin's article explains this.

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

or

http://r937.com/relational.html

...Moving on
With relational databases, the most common relationship is a One-to-Many (1:M). I like to refer to this as parent and child, although you may see master and detail and variations thereof used. The "gotcha" is that you need a parent before you can have a child. You need the record on the "one" side to exist with it's primary key. The primary key is now used on the "many" side as the foreign key. If you think about it, it is pretty hard to now what foreign key to use if you have not established the primary key.

In the design suggestion I provided, I used a 1:M between (one side) tblIssue and (many side) tblIssueOccurance because you indicated you wanted to see the number of times an issue arises.

Filling in the pieces, I have to ask if you created an "Issue" in tblIssue?

To check this, use the Query Builder to...
1) Look at data in tblIssue
2) Look at data in tblIssue + tblIssueOccurance

Since tblIssueOccurance is linked to four or five tables, before you can create a single record in this table, you need to create records in...
tblOwner
tblPart
tblSupplier
(if you use a table for priorities, tblPriority)
and of course
tblIssue

Create records for Owner, Part and Supplier (and priority) should be fairly straight forward). Creating an Issue record before the occurence may be a little more complex.


...Moving on
Preferences. There are some wizards I like to use in Access, for example, the Forms wizard. And there are some wizards I do not use, for example, the autoforms. I can understand their convenience factor, but I then have to go back and tweak the design.

(I am assuming you have established your relationships)

Here is something to try.

Use the "Create form by using wizard". Select tblIssueOccurance. Use "Tabular" or "Datasheet" template. I am going to refer to this table as sbfrmOccurence.

Now create another form using the wizard. Select your tblIssue. Use "Columnar" or "Justified" template. At the last page / window of the wizard, select "modify the form's design". Move the form which is open in design mode to the side until you can see database windo which lists all your forms (tables, etc). Click and drag the sbfrmOccurence to the bottom of the current form. This is a form + subform design. After creating your issue record in the main form, because you have established your relationships, records created in the subform will be linked to the main record via the foreign key.

This is just an example for demonstration purposes, and may not be applicable to your needs. You probably want to create a main form based on tblIssueOccurance and use combo boxes to link to your related tables.

Richard

 

[696796]

Hi Richard,

these are some very useful tips you have shared with me... you are making my job a lot easier and its good to get a second opinion on things.

I have constructed the tables again and all looks good, Just one question, on the tblIssueOccurance and tblPartSupplier, how will they be properly joined? I have the two primary keys(composite key?) of partSupplier joined to the two related foreign keys in tblIssueOccurance in a one-many fashion, will this work?

Also you mentioned i had to enter the details into the parent tables first, so i did that - should tblIssueOccurance automatically get filled with any details?


You mentioned combo boxes which will refer to other tables -

what i really want is for the user to be able to select the issue ID in a combo box, and values from each of the tables will automatically fill the text boxes related to that issue ID. Can i set this up with a form wizard, or how do i change the properties of the boxes to update according to the combo boxes value?

Am i able to do this in access or delve into VBA code? if so, what sort of code is required?

The answer to these q's would be amazing and would solve most of my troubles, again thankyou for your help

Regards,

Alex Marchant

 

[willir]

696796

One issue has one part - which can have many suppliers for one part.

I based the design on this comment. You have a part that can be supplied by one of several suppliers...

tblPartSupplier
[COLOR=blue yellow]PartCode[/color] - foreign key to Parts Table
[COLOR=blue white]SupplierID[/color] - foreign key to Supplier table
Price

tblPart
[COLOR=blue yellow]PartCode[/color] - primary key
PartDesc - descirption of part


tblSupplier
[COLOR=blue white]SupplierID[/color]
SupplierName

You can of course use PartID and / or SupplierCode depending on your need,

Combo boxes and list boxes are a powerful tool. (I will just use combo box to simplify) There are FAQ's Access Forms. In brief...

Assuming form is open in design mode, and properties window is open. (From menu, select "View" -> "Properties")

To convert a text box to a combo box. Select the text box, then from the menu, "Format" -> "Change to" - "Combo box".

A combo box has several important properties. Select the combo box, and then look at the windows properties.

Other tab:
Name: Name of combo box. You can change the name, but any code referenced by the old name will not be changed with the new name.

Data tab:
ControlSource: field name combo box is bound to. For example, CustomerID

RowSource: You can use a value list, field list, or best of all, a table or query SQL. Very power stuff. Click on the field, and click on the command button with "..." that appears to the right to open the Query Builder. You can use the query builder to reference another table. For example, from the Customer table, CustomerID and CustomerName (sort ascending). The will generate an SQL statement -- "Select CustomerID, CustomerName..."

BoundColumn: Column in the RowSource that is used. In this case, CustomerID from the Customer table is going to bound to CustomerID in the PartSuppleir table.

Format tab:
ColumnCount: Must match the RecordSource, in this case 2
ColumnWidths: Specifies widths of columns displayed in drop down list. In this case, we want 0";1.5" . The first column, column 0, for CustomerID will be hidden - the end user will only see the CustomerName.

ListWidth: Width of list - in this case we want 1.5"

Event Tab:
These are events that can be used to run code. A common event, especially if the combo box is unbound (nothing in the Data tab - ControlSource, and used to FIND a record is the AfterUpdate event.

Which answers your last question...

what i really want is for the user to be able to select the issue ID in a combo box

Use the combo box wizard to do this. It will create an AfterUpdate event.

As far as devling in the code, the aforementioned will generate code you can take a look at. Later, I am sure you will want to get your feet wet in another post.

Richard