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.
Could someone please give me some help with designing a database in access. I know excel really well, and took a class on access, and have tried a few times to get started with access but always to no avail.
I have a company that installs real estate signs
1. we have about 200-300 agents (which represent about 10-15 offices) 2. 3 things can happen to 1 sign - -1. installed the first time (charge) -2. have a sold sign, for sale sign, or flyer box put on(charge) -3. Finally removed(free, no charge) -4. the date would have to be tracked on all of these occurences 3. We have about 10 products or things that can be put or hung on a sign.
I can set up the offices and the agents, and link them just fine. I can set up the products and do the work detail. The problem I have and cannot figure out is trying to bill or invoice this. Some agents are billed individually and some agents don't get billed, there office gets billed as a whole. Could someone please offer some insight on how maybe to go about this from the beginning.
I've been asked by someone at work to design a "no brainer" gui for an access database but I am a complete newbie with regards to this - sure I can do simple queries to filter the information I what in design view but this is something else.
How can I design a gui that can be clicked on or is executed when the mdb icon is clicked that will allow people to access from a drop down box the area they need information about and then have to option to either have the information exported to excell or in a summary report to be printed off. Should I use the form wizard or the report wizard - though none of them seem to do what I want.
I've set up the database from the excell spreadsheets they gave me containing activity figures per operational region. The regions are listed in the first column and I wanted a drop down box to list them.
We have 6,000 entries on the database. These cover 8 regions with some regions containing maybe 500+ entries. As mentioned the regions are listed in column one of the database.
When I select the regions column in a drop down box instead of getting just one entry per region, I get every single entry for example 500 for region 1, 200 for region 2 etc. In other words it is reading the rows not grouping them according to region which is what I want
How do I group them so Access only includes one entry per region on the drop down box and what is the best way to design the gui to give the results I am after?
Good day, I am in the process of creating a database in Access.
I need some advice on designing the table structure.
I have 10,000 products. There are 40 product types. Some fields are the same for all products. (I.E. description, price). Some fields are unique to the product type.
I am thinking this:
1. Have a table called products with all 10,000 products including description, price, producttypeID and productID.
2. Have a table with producttypeID and ProducttypeDescription.
3. Have 40 tables with ProductID and fields that are unique to that category of product.
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)
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.
Hi guys, i need help with a sample database, and wondered if anyone could tell me the entities and how to calculate the prices etc. If anyone could attempt starting the database off for me it would be appreciated too!!
Here is the spec!!!
You are required to produce and document a design that meets the requirements of the McDuffs Burgers scenario:
The corporate office of McDuffs Burgers has asked you to design a database to help track its restaurants and managers. The database is to help the management show the total annual sales of each restaurant and the performance of each manager, as measured by the totals annual sales of all restaurants for that particular manager. Each restaurant is supervised by a single manager, but a manager is also responsible for several restaurants. The company stores typical personnel data (name, salary, and so on.) for each manager as well as basic data for each restaurant such as the telephone and address of each restaurant, its size in square metres, and total annual sales for the last fiscal year. The company would also like objective ways to measure the performance of a manager such as the total revenue for which they are responsible, the average annual revenue per restaurant, the average annual revenue per square foot, etc.
The database should also track the orders that are placed by individual restaurants to the corporate office for various food supplies. Each order is associated with a specific restaurant, and of course, a single restaurant will place multiple orders during the course of a year. The company uses a standard set of product numbers, product descriptions, and associated prices that applies to all restaurants. Each order can specify several products, and a single product may appear in several orders. The database should be capable of computing the total cost for each order.
Deliverables 1. Entity definition for each entity. 2. Entity Relationship Diagram, which must show entities, relationships and membership. 3. Relationship definition for each relationship. 4. Relations (This must include for each relation the primary and foreign keys). 5. Data Dictionary.
Furthermore:
You are required to implement the design (produced in the first part of the assessment - McDuffs Burgers - Database Design), by designing and creating queries, forms, reports and any supporting code. Revisions may be made to the design in the implementation process.
You should note the management of McDuffs Burgers has little experience of database systems and wish to be advised on the information the system can produce.
They require example reports demonstrating the capabilities of the system to: - Aid the day to day operation of the business. - Provide appropriate management information.
You must also implement an appropriate user interface to the database easy to use.
Hoping some one could assist me? I am busy designing a database which I am going to put in access.
I have designed the tables and am now not sure how to link them. I was given an excell spreadsheet with headings and am now trying to make this into a database. Example : I was given Co-ordinator Name - Which I now split up into:
Now there other fields like this , for example Regional Manager Name - which I have split up as above into a seperate table.
These fields all relate back to one Code - Called District
There are also a fields which needs to show the a fee required the fee paid and fee outstanding/balance .
The rest of the information required I can create check boxes for , I hope! Anyway my question is how do a relate all these different tables together? This is my mental block. And the check box tha information - is it stored a another table? I hope I am explaining my self correctly and I hope some one can make sense of my question
As a basic user of Access I believe I have identified a use for the program for the purposes of a reporting task which I am charged with at the moment. We undertake the weekly tracking of proposals, which involves in brief, the action required for the bigger proposals, the teams involved in writing the proposals and follow up information as well as registration of wins and losses etc. At the moment we use Excel but I am finding it extremely labour intensive with entries having to be removed and captured by only one person on a weekly basis and when you are talking about 40 to 50 new proposals registered nationally in a week and about 100 updates on existing jobs it becomes a perpetual case of chasing your tail.
Am I right in thinking that if we were to design a simple, effective database with a form design that all our research directors could use with ease, and we could merely run reports off each week that this would not produce a far quicker reaction time when it comes to pooling international resources but also far more effective reporting system with which to produce our management summaries?
I am aware that this will probably require quite a bit of consultation on design but before I go shouting the merits of Access to any of my superiors I wanted to make sure I could be confident of Access's capabilities first?
Can anyone advise? point me in the right direction?
I want to make my Intro of Database. When I open it. Just like you open NorthWind(Sample) database.
How to Remove the Title WIndow of Form.With all sides just like in NorthWind. also removed the Record Navigation (|< < 1 > >| >*). I saw every option in Form Properties but didnt find. Please Let me help in this Regard.
My second Question If I only want to remove Record Navigation only.But Title window displays. May be from first question I can also get my answer.
I have to design 2 databases base on some data that was given by my instructor but I feel like I'm missing something on both specially the 2nd database. I have attached my work so far as well as the instructions and the existing data..
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 ?
I am student, in high school, and I have to make a stock database, for ITGS, so as to receive my IB diploma.
I have created forms so as to enter stock information, and customer information, but I have no idea, as to how to connect them, so as to create an order summery and invoice. I started by creating 2 tables, one for the books, and one for the customers. I then made 2 other tables, one being an order table, and the other the linking table between the order and the books (see relation.jpg). I created a form so as to input information, but I can’t seem to get it to work, (see form.jpg)
Ideally, I would like the end-user, to select the customer's name, from a drop down list, and then for the rest of the customer information to update automatically, then, for the end-user to select a date, and finally the end-user to select books from the stock. Once the end-user has finalized the selection, they would ideally press a button, which would automatically print out an order summary/invoice (Maybe a report?)And that would also automatically reduce the quantity of the books in stock by one.
I have no idea as to how to do this and I would really need someone’s help, seeing as I have less than a week to hand this in by (The deadline is Monday 16th November 2007)
Here are the images form.jpg - http://img30.picoodle.com/img/img30/5/11/18/f_formm_78d6ea7.jpg relation.jpg - http://img03.picoodle.com/img/img03/5/11/18/f_relationm_6280f62.jpg
I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".
A visual of my tables:
tblLabels (Main table) - LabelID - BaseProduct (old field with text data) - LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)
tblBaseProduct (new table) - BaseProductID (PK and FK to tblLabels) - BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)
I tried to design a query using design view of the query design and this is what I have:
Code:
UPDATE tblLabels, tblBaseProduct SET tblLabels.LabelBaseProduct = [baseProductID] WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));
When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.
im creating a form in design view but the icon to show the fields from the table that i want the fields to come from isnt being displayed; how do i get it to show?
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
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
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.
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?
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.
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.
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?
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 ?
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?
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.
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?
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
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 =)
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?
