Database Issues, Table Relationships
Sep 24, 2007
Hi All,
My name is Paul and I am new to this Forum, so I firstly wanted to say hello to all and glad to be here.
I need some assistance with a work related database. I am currently developing it as a project but have run into a wall with the design being my first time.
I have to create a customer service and invoicing database. I currently have the following tables:
Client (Customer No, User Profile, Password, Customer Name, Acc No, Policyholder, 100% Indemnity)
Contact (Acc No, Date Signed On, Company Name, Contact Name, Address 1, Address 2, Address 3, Address 4, Address 5, Post Code, Telephone No, Fax No, Email)
Product (Product ID, Product Name, Service Level, Product Code, Price, Subscription Details)
Client / Price (Customer No, Product ID)
Usage (Order Number, Day, Month, Year, Subject Name, Client Reference, Product Code, Company Name, Customer No)
I have managed to link Client and contact no problems at all. I have also managed to link Client to Product using the table Client / Price.
I have tested this with queries and can get all of the data I need extracted out.
However the problem occurrs when I try and link the Usage table to something or other. The Usage table is data that I need to import on a monthly basis from Excel. Once I get it, I need to load it into Access and then generate usage statements for each of the clients to show what products they have ordered and how much each report has cost them.
To make matters worse, each client has 14 different products to choose from, and all clients have different prices for each of the different products.
Getting this to connect properly is an absolute nightmare.
Can anyone assist me in how I might link the Usage (imported table) to the rest of the database? I have attached a copy of the relationships on a word document and wonder if anyone can put a finger on what I might be doing wrong.
Hope someone can help.
View Replies
ADVERTISEMENT
Feb 14, 2012
I want to design a database for an educational instution environment. My problem is knowing what tables to create and how to link them. I know for example that an instructor can teach more than 1 course and a course can be taught by more than 1 Instructor. This makes it a many to many relationship which is not remommended. I know that there has to be another table to bridge these 2 tables. I want to know what is a suitable name for this 3rd table and what example fields it should contain besides the the Keys form table 1 and 2?
Now here is the over all problem:some instructors teaches more than 1 subjectsome subjects are taught by more than 1 Instructorsome Classes are taught by more than 1 instructorsome instructors teach more than1 classSome classes do more than 1 subjectSome subjects are done by more than 1 ClassSome classes are done at more than 1 locationThis is what is needed:
To be able to enter student grades and attendances by classThe marks sheet should be entered in a subform displaying the full student names list for a particular class at a time.
2. To be able to display the marks and attendance of students as a class list.
View 1 Replies
View Related
Sep 17, 2014
My db is split into front-end and back-end. In the f-e's Relationships schematic, I can see the relationships as they were defined at the time when the db was split, complete with the one-to-many symbology.
I can add a new table to the b-e and set its relationship as one-to-many, enforcing referential integrity and cascading as I wish - and the schematic (in the b-e) reflects that.
In the f-e, I can then use the <Get External Data - Access Database> function to link to the new table, and I can add the new table to the relationships diagram in the f-e. I can also drag and drop to link primary and foreign keys (within the f-e), but cannot select one-to-many. I'm OK with that, as I understand that the relationship is within the b-e, and this is just a diagrammatic representation.
But I can see the one-to-many relationships between the tables which existed when the db was split, and I would like to be able to see the new table's relationships in a consistent fashion. Updating the linked tables via the Linked Table Manager does not do the trick.
Surely I don't have to split the database again in order to achieve a consistent diagram - do I ?
View 3 Replies
View Related
Mar 31, 2006
Is this relationship correct (see attachment)? When i create an orders query with the fields:-Barcode number (source: orders/products table)Product name (source: products table)Quantity (source: orders/products table)Unit price (source: products table) Total (calculated - [Unit price]*[Quantity] )it does not allow data entry for barcode number and quantity (product name and unit price should appear automatically when barcode no. entered) why is that? and how could i resolve this? attached also is my database to see what i mean... if u have time can u please make the modifications NEEDED and send me the modified one, if not just give me a textual description of what i should do..... (btw it is actually a school canteen system databse, thats why i have customerID field as student administration number or staff initials)please reply soon.. this is urgentthank you
View 1 Replies
View Related
Sep 9, 2007
Hi,
I would like to create a small HR database that holds:
* Employee details (Name, Date of birth, Entry Date, Exit Date, Function,Department, Shift)
* Departments
* Departments and capabilities needed
* Employee, departments and capability achieved (1=yes, 0=no)
The idea would be to have a link between the employee the departments capabilities I can tick fields of the capabilities they have or have achieved.
When I have this I could run a report that shows a score per employee
For example:
Name: Department: Orderpicking: Packing: Cutting:
John Warehouse 1 1 0
As the tasks / capabilities required are different per department you would see something different if you would have user Jane that works in accounting
Is there anyone that can give me some hints so that I can get any further??
For the moment I have 3 tables:
1. Employee information - John, Jane
2. Departments - Warehouse, Accounting
3. Department tasks - Department, Cap1, Cap2, Cap3, Cap4
View 7 Replies
View Related
Jan 23, 2008
Hi all. I'm currently freelancing for a accident investigation company and they require a database. What the database needs to do is:
1.Hold client details
2.Hold Case details
3.Hold Invoice/costing details
4.hold expense details
The case table is the centre of the database and links to all the other information. I require: multiple clients per case, multiple invoices per case, multiple casecostings per invoice, and multiple expenses per case.
I have set up the tables to what i think is correct, but now find that when trying to link tblCase and tblExpenses, I am getting the error "Invalid field definition "ExpenseID" in definition of index or relationship". I think this may be something to do with my keys, but can't seem to figure out a way around.
I've attached my DB, if anybody could take a quick look at it and tell me where i'm going wrong i would be eternally grateful. Thanks guys.
View 12 Replies
View Related
Aug 22, 2006
Hi,
I would like to get your advice on my table setup and relationships for this payroll project. The company is an engineering company with Projects (or construction sites) around the world.
The 'Candidates' are current or potential employees and contractors. There are three main pay categories:
1.Shift-workers
All shift workers doing a particular job on a particular project are paid the same rates e.g. all welders on a particular project or site in England are paid the same as each other. For that reason I want to link the pay rates with the job description for these workers. This avoides creating 50 records for 50 welders on the site in England to say that they make £10 an hour normal time (or whatever it is) etc.
2. Contract
Contract workers usually get paid a flat rate per hour. As these are negociated on an individual basis I would need to have this information linked to each individuals job (M_CandidateJobDetails).
3. Salary
Again this information needs to be input for each individuals job.
For the contract and salary people the pay frequency can vary (weekly, bi-weekly or monthly). So can the currency they are paid in. I haven't got as far as the currency issue yet.
The reason for the one-to-many relationship between M_JobClassifaction and M_CandidateJobDetails is that many candidates can have the same type of job e.g. there can be many employees that in the job classifaction of 'Electrician'. For many of the jobs at managerial level e.g. 'site manager' there will only be one.
I will have a table with the hours worked by each person per week. I can use this for those on shift work or contract to calculate what they will be paid.
One of the main reasons for this database is so that the company can print reports to see what is paid out in payroll for each site and in total (in euros). These will be gross figures and I don't need to take expenses, vacations, bonuses or taxes into account. They other thing we will need to be able to do is assign candidates to vacant positions and change them from one position to another - possibly between different projects.
So basically does anyone have any comments on the relationships, normalisation or anything else. Is this the best way to do it?
I've attached a screenshot of the relationships.
View 13 Replies
View Related
Jan 16, 2008
Hi,
I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me.
Actually, I want to make a database regarding ultrsound scan examinations of patients.
I have five tables.
1. Patients. (patinetid*, patientname, age, sex, address, contact no)
2. Physicians. (physicianid*, physicianname, speciality, address, contact no)
3. Scans. (Scanid*, scanname, charges)
4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges)
5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)
I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table??
All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship.
So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.
I can post an image of relationships or blank database.
Kindly advise. Thanks in advance.
View 14 Replies
View Related
Feb 21, 2015
I need to redesign a database to include more complex relationships between 3 variables non of which are mutually exclusive.These are my 3 tables/fields. Each field is unique in its table.
T_Project.JobNo
T_Accession.AccessionNo
T_Reports.ReportNo
There is no clear relationship between the variables - ie they can all be 1 to 1 or 1 to many or do not have to exist at all. So I realise now (after 14 years of working with this data), that I need to have what I would think of as holding tables between the primary tables that hold combinations of the variables. Do I do this in one table that holds all 3 variables in non-repeating combinations (although this would need to allow nulls) or do I do it in 3 separate holding tables?
View 3 Replies
View Related
Oct 14, 2005
I am trying to put together a sales/purchase/stock control/accounting program using Access. I have put together a number of tables that I feel will be required and now I'm trying to set the relationships between them. The primary end document to be printed will be a Sales Order and a Purchase Order, likely generated from the reports module, so I have a table called Purchase Order and another called Sales Orders, related to Purchase Orders will be a table called Suppliers and another called Customers will be related to Sales Orders. Another table is called products.
For the Purchase Orders table, do I only add fields that are the primary key from the Suppliers table in order to list the full customer name and address stored only in Suppliers table. Would the supplier table be the parent and the Purchase Order the child ?
View 3 Replies
View Related
Feb 15, 2006
I have setup a number of tables for a booking system. The tables are as follow:
tblCustomers
tblBookings
tblRegularCustomers
tblRegularBookings
When a booking is made for just any time, it is in the tblBookings table. Regular bookings for example, every week, are put in the tblRegularBookings. In each of these tables, a booking cannot be made for the same date and time period. I am wanting to link the tables, so that when a normal booking is made in the one table, it cannot be the same date and time period as a regular booking as well.
The fields in each are as follow:
tblBookings:
Booking ID
Customer ID (linked to tblCustomers)
Date for Booking *
Time Period *
Weddings/Birthdays (yes/no)
Extension (yes/no)
Cost
tblRegularBookings
Regular Booking ID
RegCust ID
Date For *
Time Period *
Extension
Cost
What should i link to allow me to not create a normal booking on the same date as a regular?
Thanks
View 6 Replies
View Related
Nov 25, 2004
Here are my relationships for my 5 tables I created. My database is something that a turn key automotive performance shop would use. Tell me if my relationships are correct and what I need to do. I think I got a good start but I am not sure on a few things. Also what do I need to do for my validation and look up tables. Any help on that would be great. Thanks.
http://www.mustangmods.com/data/1030/relationships.jpg
View 14 Replies
View Related
Feb 9, 2005
This is a simple video library database, 1 member can have many loans, 1 video can have many loans and 1 loan can have many videos, so with that in mind woud anyone be able to make my relationships right? because at the moment you cant take out more than one video on each loan?
thanks in advance
http://www.the22nd.com/forum/uploads/post-12-1107976078.jpg
View 14 Replies
View Related
Apr 1, 2005
I have restructed my original tables into 3NF and added relationships. But I think I am missing a few more items to go on to next step. Help me, please! I am putting together an automated Time Card Entry Database based on a timesheet and other reports already existed in Excel.
I have the following fields on this form as follows:
1. Week Ending: automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date.
2. Employee Number: combo box that will populate next fields (1. Employee Last Name; 2. Employee First Name) after user select correct Employee Number.
3. Employee Last Name: automatically populates when Employee Number is selected.
4. Employee First Name: automatically populates when Employee Number is selected.
5. Acct Id: combo box containing a list of labor description that has associated fields as follows.
6. Description: Text Box will populate after selecting Acct Id.
7. Cost Center: Text Box will populate after selecting Acct Id.
8. Acct: Text Box will populate after selecting Acct Id.
9. Category: Text Box will populate after selecting Acct Id.
10 Pay Type Id: combo box that will populate Pay Type.
10. Pay Type: automatically populates when Pay Type Id is selected.
11. Allocation: Free form, to type comments.
12. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri
13. Total Wk Hrs: total hours for the week for per line of Acct Id.
Here are my tables:
1. tblEmployees:
1) pkeyEmployeeId = AutoNumber
2) strLastName = Text
3) intStaffNumber = Number
4) fkeyEmployeeTypeId = Number
2. tblEmployeeType:
1) pkeyEmployeeTypeId = AutoNumber
2) strEmployeeClass = Text
3) strEmployeeType = Text
3. tblAccounts:
1) pkeyAcctId = AutoNumber
2) intCostCenter = Number
3) intAcctNo = Number
4) intCategory = Text
5) strAcctName = Text
6) strAcctDescription = Text
3. tblPayType:
1) pkeyPayTypeId = AutoNumber
2) strPayType = Text
3) strDescription = Text
4. tblPayroll Schedule:
1) pkeyPayrollScheduleId = AutoNumber
2) intPayPeriodId = Number
3) dtmPayStartDate = Date/Time
4) dtmPayEndDate = Date/Time
5) dtmCheckDate = Date/Time
5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayPeriodId = Number
6. tblTimecardHours:
1) pkeyTimecardDetailId = AutoNumber
2) fkeyTimecardId = Number
3) fkeyAcctId = Number
4) fkeyPayTypeId = Number
5) strAllocation = Text
6) intSat = Number
7) intSun = Number
8) intMon = Number
9) intTue = Number
10) intWed = Number
11) intThu = Number
12) intFri = Number
Here is the layout of my form in this order:
1. Pay Period Id
2. Week Ending
3. Employee Number
4. Employee Last Name
5. Employee First Name
6. Acct Id
7. Description
8. Cost Center
9. Acct
10. Category
11. Pay Type Id
12. Pay Type
13. Allocation
14. SAT
15. SUN
16. MON
17. TUE
18. WED
19. THU
20. FRI
18. Wk Hrs
Question 1: Please review table relationships to see if I overlooked any tables that can be broken down or named its column differently?
Question 2: I have restructured these tables into 3NF, please see my previous posts under AccessRookie (although before 3/2005, someone used this ID).
Question 3: what code do I need to automatically populate "Week Ending" field with the correct "CheckDate" that will compare against today's date and insert into "Week Ending" field? Currently, user selects from Combo Box(Pay Period Id) then it populates Text Box(Week Ending).
Question 4: how come "tblTimecardHours.fkeyTimecardId" field is not populating? I think it is my table relationship: tblTimecard & tblTimecardHours.
Question 5: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI).
No need to store total since it is only need to display in data entry form (subform) and printing report.
I need your assistance since sometimes, it just takes another pair of eyes to review another peers' work. It's been ages since I've done any development from scratch. Help!!! Is there any way, I can attach my zipped database? It is 217KB zipped but this site only allow 100KB attachments.
Sincerely,
AccessRookie =)
View 5 Replies
View Related
Oct 5, 2005
I Have These Tables:
Case, Agents, Forwarders, Orders.
1 Case Has Each Time 1 Agent, Many Forwarders And Each Forwarder Many Orders
1 Agent Many Forwarders
Can Someone Suggest The Best Way To Link Them?
View 1 Replies
View Related
Jul 11, 2006
Can anyone look at this pdf and let me know if my relations are correct, i know i haven't marked on if its one to many etc but i just need to know if it'll work?
View 3 Replies
View Related
Jul 15, 2006
Ok im extremely new to this whole Access thing so I have several questions. First, let me give you an idea of what I am attempting: I am a member of a committee and we are involved in multiple projects. Each person is either inactive (working on no projects) or active (working on 1 or more projects). I want to set up a table that lists the member names, contact info, and which project(s) they are currently working on. I set up the basic table (not sure if its set up correctly or not though) but since some people are on more than one project, how do i make it so i can enter multiple values for the "Current Project" field? Also, I would like to make another table that lists Project Names, members (the members that are working on each specific project), and project description. How do I set it up so that under members (on the project table), it will gather the info from Member table (the one with contact info and such) and automatically list which members are working on each project? Also, There is one Leader per project and the rest working on it are just normal members. Can anyone suggest a good way to display who is leader on each project?
if this is confusing, just ask and i'll try to clarify for you
thanks, TRC
View 4 Replies
View Related
Sep 22, 2006
Hello all,
I have tables related by key fields. Is there a way to enter data into one table and have the key fields of the related table automatically enter into the key field of the related table?
Also, can you make custom drop down menus in a field? For instance, I have field that I would like to have a text in, there are only three values that are needed but spelling is important so I would just like to pull a list down to enter the data.
Thanks for any help!
View 8 Replies
View Related
Mar 6, 2008
im using access for the first time in years as iv not used it since college and im having problems with table relationships. i have 3 tables "cusomer details", "product details" and "purchases".
i have created a relationship between customer_id (primary key) in customer details and customer_id in purchases and product_id (primary key) in product details to product_id in purchases.
the idea is every time a customer makes a purchase i enter their customer_id into purchases and it will automaticaly bring up their name and address and enter the product_id and it will automaticaly bring up the product name in that table (purchases)
this does not appear to happen???????
i know its been a few years since i last used access but im sure iv done it right, what am i doing wrong?
TABLES BELOW:
Customer Details
customer_id
surname
forname
address
telephone
email
reg_date
Product details
product_id
product_name
product_category
price
Purchases
purchase_id
customer_id
surname
forname
address
product_id
product_name
quantity
View 8 Replies
View Related
Aug 9, 2006
I am new at Access. I am doing a database tracking the test results of eight different grades of students. The primary key is the name. When I try to add the 3rd test score table, I lose all information of all of the tables. I have tried changing the relationships, and cannot make this work. Any suggestions, please help.
View 1 Replies
View Related
Mar 29, 2007
I have 2 tables. One is JOB TRACKING and has all the information about a certain job and all the trades involved. The other is a table of email address for the managers of each of the trades. I have them connected (with relationships) so the trades match up.
There is a form that I fill out each time a new job comes in and I check off the trades involved on that form. Is there a way for that form to pick up the email addresses of the managers of the trades checked off? So then I can create a button some how to send an email to them containing that particular form...
thanks!!
View 1 Replies
View Related
Jul 12, 2005
I'm using Access 2003. When I open the relationship window, one table linked to my main data table appears six times - the name is the same but with the addition of a number 1 -5. I can delete the five spare ones and drag a relationship to the main table but it keeps dissapearing.When I try to change the primary key in this table, it tells me that there is a relationship established which I must break - unfortunately, I can't see any relationship between the two tables in the relationship window. Any ideas?
John
View 1 Replies
View Related
Nov 15, 2005
Hi. I've been trying to create a link between two different tables which i've put on the Relationships screen. Any type of link made between RepairScheduleID on the one table and RepairScheduleID on the other table disappear when I reopen the database after closing access. This happens if I save, if i don't save it requests me to save then doesn't store the changes anyway.
Ideas? What on earth is wrong?
View 1 Replies
View Related
Nov 28, 2006
Hey
I was wondering that is it possible that is in one field I select a certain name, say a type of product and that in another field will automatically display the price of that product based on another table.
Thanks
View 1 Replies
View Related
Jul 23, 2007
My database is to track customer incidents. Can anyone tell me if my relationships look ok? I'm a remedial access user to any input would be appreciated. Thanks in advance for the help.
View 6 Replies
View Related
Feb 27, 2008
I have a database which has been running well for four years. There are four tables that are linked from one to another in a one-to-many relationship, i.e., Table1 to Table2 to Table3 to Table4. We recently had a power failure and our network went down. Now the relationship between Table2 and Table3 is broken. When I try to rejoin them using the Relationship View, Access tells me that the relationship between the tables is "Indeterminate". I have run queries testing for unmatched records between all of the tables and the data appears to be fine.
My only guess at this point is that the structure of one of the tables is corrupted. The problem is that each of the tables has an autonumber field. If I build a new table structure, and append my old data, isn't that going to renumber all of the records?
I have run a compact and repair on the database and that didn't help. I have made copies of the tables and tried to use them in the relationships. The copies don't work either. Other than not being able to link the two tables, everything is working fine.
Does anyone have any ideas?
Thanks,
Cherry
View 3 Replies
View Related
