Table Relationship Newbie

Sep 14, 2005

I am working on this database where each category contains certain thickness ranges that can be stored in a pack of x amount and finally paneltypes.
ie KS1000 only comes in 40mm 50mm 75mm and paneltype MR is the one associated with it.

there are 3 tables
tbl_Customer
tbl_catergory
tbl_product
--------------------
tbl_Customer:
-CustomerID (autonumber) Primary key
-CompName
-CompAddr
-ContactFName
-ContactSName
---------------------
tbl_category:
-CategoryID (autonumber) Primary key
-Category (combo box) this has set values entered i.e.

KS600
KS900
KS1000
KS1000LP
KS1000RW
------------------
tbl_Product:
-ProductID Primary key (autonumber)
-fkeyCategoryID (foreign primary key)
-Thickness (combo) this has set values of thickness ie. 40, 50, 55, 60, 70, 80, 100, 150, 180
-NoInPack (set number directly related to the Category and thickness chosen)
i.e. Category KS1000 with thicknesses of 55 can be stored in a pack of 17. KS1000 with thickness of 60 can be stored in a pack of 22.

-PanelType: (combo box) set values as follows:
MR (this selected item relates to products KS600 KS900 KS1000 )
EB (this selected item relates to product KS1000RW )
CX (this selected item relates to product KS1000 )
MM (this selected item relates to product KS1000 )
WV (this selected item relates to product KS1000 )
---------------
I would like to be able to setup a form that a user uses to provide a quote:
It would start with using the Category combo box which lets you select one of the choices (i.e. KS1000) but the combo box would also have multiple column headings ie. Category/Thickness/NoInPack. Once selected then the next several individual field boxes (ie. thickness, NoInPack) are automaticallly populated with the selection just made in the Category combo box.

NOTE: Many categories can be chosen just for one record.
I therefore do not want to start setting up fields like Product1 product 2 etc and their associated fields like thickness1, thickness2 etc.
I'm failing in the relationships section. Please see attached database.
I have tbl_category: CategoryID (1) --------> (many)fkeyCategoryID from tbl_Product
I have tbl_Product: ProductID (1) -------->(many) CustomerID from tbl_customer
I know that i need a junction table somewhere.... but i'm a newbie when it comes to this stuff.
Any help would be appreciated.

View Replies


ADVERTISEMENT

Newbie Table Question..

May 21, 2006

Hello, (pls scuse my english, it's not good) I'm a newbie to Access and I'm learning access I'm trying out one scenario to see if I can do it but I'm stuck..
main scenario is that a local company has decided to computerise their wage systems, but the wage system is very confusing.. data modeling has already been done in this example but as I understand the boxes(entities) in ERD becomes tables right? so I have like five tables.. Pay method, Employ type, Employ, Department, Division..

I know what to do with Employ, Department and Division but Pay method and Employ type is confusing for me I don't know how to work out attributes (fields) for these.. I have four different paying methods depending on 7 different types of categories of employees.. how would it work?

Also if you guys have any links that explains sort of similar scenario I would appreciate it very much.. thank you..

Oh and since I'm a newbie I'd appreciate it if you give me a simple answer not complex solutions,

View 3 Replies View Related

Newbie Help!!! Moving Record To Another Table....

Jan 10, 2005

Quick Scenrio:

I have a current employee's table and Retired/Resigned table, when an employee either retires, resigns or gets fired, i want to be able by the click of a switch or check box of some sort to move that record to the retired table. Your input is greatly apprectiated....

Eddie..

View 4 Replies View Related

Newbie- How To Attach Formula To Field In A Table.

Mar 9, 2008

I'm sure this is a simple issue, but so simple I can't find the answer.

I have a recipe DB and have calories and fat and fiber fields, I want to have another field which will display a point count for a formula (calories/50+Fat/2+fiber/5) but the catch is if the fiber number entered is over 4 I only want the calculation to use 4 max.

Not sure how or where to create this formula to populate a field in the DB.

As you can tell I'm new to all this and any help would be appreciated.

Thanks in advance
Mike

View 14 Replies View Related

Newbie Question Link Row To Entire Table

Jul 21, 2006

I'm a relative newbie and I'm trying to figure out if I can link individual rows in one table to entire whole tables.

Example:

I have a table with the following fields:

Ticker Quote Volume
MSFT $25.00 3000000
IBM $30.00 1093837
SNDK $20.00 5959483


Now each of these Tickers has a whole table of options associated with each particular ticker and the EASIEST way for me to proceed would be to simply link row MSFT with an ENTIRE table of MSFT_OPTIONS then link IBM with the table IBM_OPTIONS.

Keep in mind that I don't have a field called TICKER in the options table and MSFT does not appear anywhere in the options table.

I know I can go and add the TICKER field to the OPTIONS_TABLE and then assign a primary key and then link them both but this would involve a great deal more work since I have hundreds of stocks with options tables to link. The data I get is in a specific format and I don't want to have to massage the data too much.

I appreciate your help,

Mr. Opine.

View 1 Replies View Related

Make-Table Query With Relationship To New Table

May 31, 2005

I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.

Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.

Thank You

View 1 Replies View Related

Newbie Question, Basic Table Automating Entries

Jul 20, 2006

Hi all, new member with a newbie question. I have just started working with Access, so pardon if this question is unclear or pitifully basic. We have a basic database comprised of client names, amounts paid to-date and balance due. It also has a field named date last paid, so here is the question. When you enter or change the amount in paid to-date, can you automatically have the date of the change (ie. Todays Date) entered in the "Date last paid" field.

Thanks all for the help in advance.

Chris

View 5 Replies View Related

Newbie Question! Getting A Cmd Button To Create Data In Table?

Nov 29, 2004

Hey all,

I've never used Access much...i was able to use it OK at one point but ive forgotten all about it now.
I need to create something very simple for the reception at my work...

When a customer phones we want to be able to keep track of how they heard of us - so we want a very simple access/VB program.

The best way would be to have buttons of each of the magazines our company is listed in..then when someone phones and says "ahhh magazine 3" the receptionist can press a button and the button will add 1 to a field in a table?/report? next to that magazine.

Hope that makes sense :s

Any help would be very appreciated! - It seems very simple to do ?

Thanks
Acle

View 5 Replies View Related

Total Newbie Question - Form Updating Not Table

Dec 1, 2004

Okay I've looked around but haven't found an answer to this, if there is one in the forum please forgive me. I've created a database to help track employee passwords for differnt system I placed and Audit Trail on the form (the example from Microsoft) however it updates only the form and not the table and I need it to do both. Please Help!

Thanks

Rand

View 14 Replies View Related

Can A Table Have A Relationship With Itself?

Jun 6, 2005

How can I have table data refer to other data in the same table? For example: If I have a table of people I know, and I want to 'link' family members together, and these other family members are also listed in the table of people I know.
Table: People
Field#1: PersonID
Field#2: PersonName
Field#3: FamilyMember1 (using the PersonID)
Field#4: FamilyMember2 (using the PersonID)

I hope I've explained myself clearly.
Thanks for any help.
-E

View 10 Replies View Related

Table Relationship

Aug 29, 2006

Hi, I have 2 related. In the first table I have a primary key number and another ID number.

On the 2nd table which is related by the PrimKey I want the ID number to come up automatically. I can't remember how to do this. Can anyone help?

thanks

View 2 Replies View Related

Table Relationship

Feb 1, 2007

Hey guys

Access rookie here (and I mean ROOKIE).

I have two tables.

One with details of people's information (mothers).

We also need to enter information for their babies.

Basically I have set up a form with the info for the mother's details, and she has a Mother ID. The baby also has an ID which should be the same as the mothers (linking the two tables).

When I enter the info into the form for the mothers details, I hit the command button that opens the baby's details, however it has not created a baby ID that is the same as the mother's ID, therefore there is no relationship. If I enter in the baby id, and fill out the form - I goto the table and see that it has made the link and the information is complete. However, I don't want to have to enter the ID each time.....I just want the mother's ID to carry accross to the baby's ID field, hence linking the two.

Why isn't this working?

Thanks for your help!

Ryn

View 1 Replies View Related

Cannot Add Another Table And Relationship

Mar 7, 2007

Hi there,

Hope you can help...

I am having problems with a current database i have designed which was initally for Products/Stock. I now want to intergrate Services into the equation. I have attached a proposed design, but whenever i add the Services table and relationship, everything, in the sense of data disappears.

I can upload my Database if needs be.

Thanks,
Jon

View 5 Replies View Related

One To Many Table Relationship

Jul 19, 2007

I'm having trouble modeling a relationship in a database. I need conceptual help. Here is an abstract description: A Class has several common attributes, but there are some attributes that differentiate different instances of this Class. For example, this Class may have x fields. ClassB will have those same fields and several others, so Class B will have (x+y) fields. Class C will also have x fields and an additonal Z fields for a total of (x+z) fields. x and y may be the same number or not, but they will certainly not be the same type of fields. See attached for picture.

My thinking is that since all the classes have x fields in common, why not have super class in its own table (ClassA), and have two (or more) tables composed of y and z fields respectively. The primary key (A1, lets say autonumbered) in the ClassA table would also be the primary key in ClassB and ClassC tables.

My goal is is have a subform within a form, where the form has all the fields from ClassA and the subform will have all the fields from either ClassB or ClassC depending on another field in ClassA (A2, text type for example). In essence, the layout of the subform would change depending on the value of A2 from the ClassA table.

Can this even be done. I think I'm missing something here. Would anyone care to offer advice?

View 3 Replies View Related

What Table Relationship?

Feb 19, 2008

Hi, I have these two tables

horse_entries
ent_HorseName (PK)
ent_entries

horse_information
inf_HorseName
inf_Ground
inf_Notes

I have no records in horse_information yet. I need it so that when a record is added to the first table then whatever is added to ent_HorseName is added to inf_HorseName. So I want all the records from horse_entries in horse_information. I want to be able to add to horse_information without that record being added to horse_entries. What kind of relationship should I use. Thanks

View 3 Replies View Related

Table Relationship

Sep 30, 2004

I have a form to insert data into the table1 of the db.mdb file.

In db.mdb file I have two tables one is table1 and other is table2 now the fields in both table are as follow

Table1

ID (AutoNumber)
Name (Text)
Roll No. (Number)
Class (Text)
Address (Text)
Result (Text)


Table2
ID (AutoNumber)
Roll No. (Text)
Result (Text)

Now I want that whenever that data is posted into table1 then table2 should autoupdate the corresponding data.

Please guide me how this is possible?

With Regards.

View 5 Replies View Related

Relationship Table For Other Objects

Sep 18, 2005

I am looking for an easier way to confirm that all a queries that are in a database are being used by the database. I started going through each and every one and it is taking for ever!

I have tried the documenter and either I cannot understand it properly or it does not contain the information that I am looking for.

An example might be I have a query called qryOne. I want to see where it is being used so I am looking for a “list” that says

qryOne = rptOne
qryOne = rptSix
qryOne = rptEightySix

Obviously these are just made up names, but I hope you understand what I am looking for. In essence a relationship table for queries, forms and reports.

Thanks for your help

View 3 Replies View Related

Table Relationship Theory

Aug 2, 2006

I was hoping to get pointed in the direction of some relationship theory. I am new at this and was wondering when, where and how to implement relationships into a DB. I am sure this has a lot of good uses, but I am failing to understand it completely. Any and all help is appreciated.


thanks
Riley

View 2 Replies View Related

Table Relationship Question

Mar 27, 2005

I have two tables, table 1 with client contact data and table 2 with client needs, both are auto numbered. I have two fields that are titled LastName & FirstName that appear in both tables. When I enter a new client in table 1 I want table 2 to automatically be updated with the clients first and last name. For example: If both tables have 4 records and I enter a new client in table 1 I want table 2 to be updated with the new client first and last name information,so both tables would now have 5 records. How do I create this relationship? Any help would be appreciated. If I haven't explained this well please let me know. Thanks. :confused:

View 3 Replies View Related

Multiple To 1 Relationship In A Table?

Mar 27, 2006

Hi,

I'm making a database of spare parts for machinery.
But I came upon the following problem :
A part can sometimes be used is more that 1 machinery.
But I do not understand how I put the ID of all the machines in 1 field of that spare part

example : spare partes :
- ID
- name
- machines

How do I solve this problem the best way?


TIA!

View 7 Replies View Related

Table Relationship Theory

Aug 2, 2006

As I continue my pursuit of MS access knowledge, I am moving to table relationships and would love if someone could point me to a good source to explain in detail when, where and why to use table relationships as well as how to implement this. Thank you much!


Riley

View 3 Replies View Related

Table Relationship Insanity

May 16, 2007

:eek: < I really like that smiley.

I am very glad to have found this forum, as I really need some help. I work for a non-profit and was asked to develop a database that would allow our services people to input data on employers that have been visited and track followups. I've tried to sort of use the "Access 2003 Inside Out" book by Viescas and elements of his LawTrack contact database sample. Confusion reigns supreme at this point. I've done some topical Access work before, and have even set up some simple databases using InfoPath as a front-end, but nothing this complicated.

I have the tables, and have attempted in multitudes of ways to establish the proper relationships. The most common problem is when trying to enter information into a form, at the point where it's time to enter contact information (three associated tables), I get that funny bell sound, and it won't let me enter proper information. A couple of times I noticed after completing all other fields and proceeding to a blank record, then coming back to it, it will then let me enter info.

I've changed the relationships and keys multiple times to no avail...:confused:

Here is the graphic of the relationships:
http://www.jcindustries.com/employerdatabaserelationships.png

I need for tblCompanyContacts, tblEmployerContact, and tblContactEvents to talk to each other and to tblEmployerDemographics as there are many contacts to one employer and you might have many contacts for one employer but only one can be primary.

I'm thinking the contacts-related tables would be best nested as a subform in a main form....when I try to generate a form on those three tables using table wizard, I get a blank form :confused: I know the whole problem is how to use the keys and their relationships, but I'm key stupid at this point :rolleyes:

Any thoughts?

Cheers,
Don B.

View 7 Replies View Related

Table Relationship Questions.

Jul 7, 2007

Hi Everyone,

I'm just at the start of database project to see if I can reproduce some kind of Management Game.

I'm quite focussed on making sure I have an efficient design for the database at this stage.

I've created most of my tables, and have also created most of the relationships between them, however I've got a couple of issues which I'm ummming and ahhhing about, to which I thought I'd see if anyone in here could help me.



1. I have a player table which will contain numerical values from 1-20 (Not integers) for different player attributes. The value of those attributes will be looked up and a text entry returned for any reports which would be seen by a user.

I want to do this for more than one type of attribute (i.e. Strength, Speed), is it more efficient to store all the text entries for those attributes together in one Attributes Table, or to store them separately in different tables?

(The text entries may be different for some of the attributes; this data would often be reported on collectively).



2. I have a Teams Table; each team would belong to a Region and then a Country with each Country having more than 1 Region. (i.e. ‘Yorkshire’ and ‘Lancashire’ would both be part of The UK…. All be it quite possibly not altogether happy about being next to one another ;-) )

Should I relate the Teams Table to a Region Table and then relate that to a Country Table? Or should I just make one relationship to a Table containing both Regions and Countries? (I'm thinking for normalization to take the first option, but am not certain)



3. I have a few tables that would link to quite simple data called Gender, it contains two options. 'Male' and 'Female'

For each table that would link to this information; should I have one Gender Table containing this data and relate them all to it, or should I create a separate Gender Table for each instance?



4. In my Players Table I have the option for each player to be carrying something in Both a ‘Left’ and ‘Right’ hand. I've created a separate field for each piece of data in the Players Table. The object being carried in each hand could be the same or different, or require both hands.

For each hand should I create a separate Objects Table containing the same data and then link to it, or is it more efficient to link both fields to the same Objects Table field?



I hope this is clear, but if not I’m more than happy to elaborate.



Thanks in advance to anyone who can help.

Kind regards,
pdjacks

View 9 Replies View Related

Import Table Without Relationship

Apr 25, 2008

I want to import tables from one mdb file to another mdb file without relationship using code. Anyone have the code?

Thanks.

View 3 Replies View Related

How To Change Table Relationship?

Oct 26, 2004

I have a table that is related to another table one to one. I need this table to be a one to many relationship. When I right click on the link to edit the relationship, I can't figure out what I need to do to change it to one to many. I have clicked on everything and I can't get it to change. Please help me.

View 2 Replies View Related

Table Relationship Problem

Dec 20, 2004

Hello to all
I am in need of urgent help so any help would be highly appreciated. I will try and explain the problem as easily as I can. The problem is as follows:

1. I have a table of users and each user is mapped to corresponding roles. So in other words one user can have one or several roles. That is one relationship.

2. On the other hand I have each role is mapped to specific module. So we have one module with several roles assigned to that module which is the next relationship.

3. This is relationship that I am having problems with. Since I have a relationship between User to role and another relationship of module to role I want a table that is basically dynamically generated to tell me which users need which modules. So it is a user to module table which the database should figure out according to what roles the user has and which module contains these roles.


I have no idea if there is way to do this or weather the mapping between module to role has to be done manually by me. I would really appreciate any help. Thank You

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved