Normalisation Help Required
Feb 25, 2006
Hi all this is my first post so go easy
OK i have a project to do about a garage
these are the unormalised attributes
Customer_ID, First_Name, Surname, Address, Telephone_No, Postcode, Employee_ID, First_Name, Surname, Hours_worked, Service_ID, Name_of_service, Cost_of_service, Car_registraion, Engine_size, Colour_of_Car, Car_manufacturer, Invoice_No, Amount_due, Amount_paid, Outstanding_amount, VAT, Cost_of_service, Booking_code, Date_of_booking
these are the entities
CUSTOMER
SERVICE
CAR
PAYMENT
EMPLOYEE
BOOKING
ive never done normalisation before, and read online a bit about it but cant for the life of me understand it. anyone willing to give me a little help. on converting the above to 1NF, 2NF and 3NF?
Thanks in advance
alison
x
View Replies
ADVERTISEMENT
Nov 3, 2014
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies
View Related
May 19, 2005
This is a bit of a wierd question but as part of my assessment for university I have had to build a video rental database. My problem, however, is with the normalisation.
In my head I go straight from the first step to the last and no matter how much i have tried I cannot manage to comprehend the 1st, 2nd and 3rd steps thing and we have to show these in the writeup. Can anyone help explain this to me?
Thanks in advance for your help.
View 1 Replies
View Related
Sep 16, 2005
I have a hire vehicles.
Vehicles have a colour.
Because vehicle details - ie colour change over time , I want to record this against the hire ( it was the colour of the vehicle at the time of hire) - the fact the colour of the equipment has phsically changed is not important to my hire table.
Say I had a field on the vehicle table which was a code linked to the colour table. I want to copy the colourID to the hire. - Would my database be normalised if I did this.
Further more - colour is a legal requirement - and is more than just a decription - although I can change all items from red to pink in my colour table, I need the hire to tell me what the description of the colour was (the description of the colour- the actual colour has not changed) when I hired the item.
How would I arrange this?
View 3 Replies
View Related
Sep 12, 2006
I am seeking advice on a database with currently over 40,000 records, which was pulled off the internet from NOAA for weather related records.
The spreadsheet that they were using looks like this:
GridNumber | Year | IntervalNumber | Index Value
Each grid has history back to 1948 and each year has 6 intervals with an index value to each interval.
What I think would be better is have:
GridNumber | Year | Interval 1 | Interval 2 | Interval 3|.....| Interval 6|
And have the index value for each interval by year.
I am wanting to add some unbound fields on a form and create a "rate of payment" for each interval's index value and some other thing.
Does anyone one know of a way of moving this data over to over to a new format in an easy manner? And would it be the thing to do?
I have attached the db for looking at as anyone would be able to pull this info off the net. I removed several 10,000s records to allow for space.
I would sincerely appreciate any insight anyone could give on the subject.
View 6 Replies
View Related
Mar 16, 2008
I have a table that tracks a series of (mostly) date-based events and I'm trying to decide whether to further normalise the table.
The process works like this:
1. We are notfied of a problem (PBTCID PK, PropertyID FK, DatePBTC)
2. We send a recorded letter (DateRecLet)
3. We send a 1st class letter (DateFirstLet)
If no response to letters then:
4. Problem assigned to a department (DateHousing)
AND
5. Problem assigned to specific person (HO - numeric, to lookup)
If no response after their involvement then:
6. Request possession order (DateReqNSP)
7. Possession order served (DateServNSP)
If no reponse after order is served then:
9. Go to court (DateCourt)
10. Record outcome of court proceedings (Outcome - numeric to lookup)
At present, all of the above is in one table. I would say that 70% of the time, the problem is resolved at step 3. A further 25% is resolved at step at 4/5. Another 4% at step step 7, with the remaining 1% going all the way to step 10.
Am I better off with one table, even though the majority of proplems won't go beyond step 3? If so, what would be the best way to break this out?
Any direction would be much appreciated.
View 2 Replies
View Related
Jul 14, 2006
I have a Table Structure that works fine, but I want to trim it down a little.
I know that a few tables aren't normalised properly.
Three of the tables (tblCustomer, tblSupplier, tblHaulier), could be trimmed down to two tables (tblCompany) with a link to (tlkpCompanyType).
This would then allow me to make just one "Contacts" table, etc etc.
My problem is, I do not know how to refer to both a Customer AND a Haulier in the tblShipments or the tblShipBookings, as both of these tables need to have the names of both the Customer AND haulier in them (or at least the foriegn key).
You can see a jpeg of the database relationships here (http://www.joyceandstevieb.com/dbasemap.htm)
Any advice would be most welcome.
Thanx
View 14 Replies
View Related
Apr 18, 2007
Hello. I am trying to understand normalisation at the moment. I'm still a bit lost but I have tried to normalise this example. Is it right?0NFORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode, ItemID1, ItemName1, ItemPrice1, QuantityOrdered1, ItemID2, ItemName2, ItemPrice2, QuantityOrdered2, ItemID3, ItemName3, ItemPrice3, QuantityOrdered3)==================================================First Answer1NFORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)ITEM(OrderID, ItemID*, ItemName, ItemPrice, QuantityOrdered)2NFCUSTOMER(CustomerName*, HouseNumber, Street, Town, Postcode)ORDER(OrderID*, OrderDate, CustomerName)ORDERLINE(OrderID*, ItemID*, QuantityOrdered)ITEM(ItemID*, ItemName, ItemPrice)3NFCUSTOMER(CustomerName*, HouseNumber, Postcode)ADDRESS(Street, Town, Postcode*)ORDER(OrderID*, OrderDate, CustomerName)ORDERLINE(OrderNumber, ItemID*, QuantityOrdered)ITEM(ItemID*, ItemName, ItemPrice)or is itSecond Answer1NFORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)ITEM(OrderID, ItemID*, ItemName, ItemPrice, QuantityOrdered)2NFORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)ORDERLINE(OrderID*, ItemID*, QuantityOrdered)ITEM(ItemID*, ItemName, ItemPrice)3NFCUSTOMER(CustomerName*, HouseNumber, Street, Town, Postcode)ORDER(OrderID*, OrderDate, CustomerName)ORDERLINE(OrderID*, ItemID*, QuantityOrdered)ITEM(ItemID*, ItemName, ItemPrice)Thank you for helping :D
View 2 Replies
View Related
Jul 15, 2006
I have a Table Structure that works fine, but I want to trim it down a little.
I know that a few tables aren't normalised properly.
Three of the tables (tblCustomer, tblSupplier, tblHaulier), could be trimmed down to two tables (tblCompany) with a link to (tlkpCompanyType).
This would then allow me to make just one "Contacts" table, etc etc.
My problem is, I do not know how to refer to both a Customer AND a Haulier in the tblShipments or the tblShipBookings, as both of these tables need to have the names of both the Customer AND haulier in them (or at least the foriegn key).
You can see a jpeg of the database relationships here
Any advice would be most welcome.
Thanx
View 2 Replies
View Related
Oct 13, 2005
Why is it common to store address fields
ie Address1, Address2, Address3, Address4, Town etc in a table, seems to me that is as un normalisaed as
having fields like description1, description2, description3 etc which we would never do and put in another table as seperate records rather than fields.
Whats the differnace that makes address more acceptable to be un normalised?
Confused as ever , Paul
View 7 Replies
View Related
Nov 17, 2005
Hi
I have a timesheet db.
An employee can have upto classification for hours -
1. RT - Normal time
2. OT - Over time
3. DT - Double time
I have a main form which the theme is on the task and per day and this can have many employees, which is the sub form. If someone works RT, OT in a day it means there names appear twice when the hours are entered. At the weekend it is always DT.
I have the RT, OT & DT as a look up for the field.
Does this meant that i could set up the datasheet subform better?
Thanks
View 6 Replies
View Related
Mar 20, 2008
Anyone who can help, i have encountered a problem :confused: when trying to normalize tables. I have single paged word document attached for anyone capable to help. All advice and suggestion is welcomed.
View 5 Replies
View Related
Jun 8, 2005
Hi everyone,
I'm trying to get to grips with normalisation as it applies to a specific example database I'm working on (that I will ultimately be using to teach other people :eek: ). If anyone could help with the following issue I would be very grateful:
I have a table of football (or soccer) teams (TEAMS) with the fields
team_id
team_name
I also have a table of results (RESULTS) including the fields
result_id
result_hometeam
result_awayteam
result_homescore
result_awayscore
etc.....
The result_hometeam and result_awayteam fields both contain team_id as links to the TEAMS table.
Does the fact the team_id appears in two columns in the RESULTS table break any normalisation rules?
Should I have it split further so each row shows one team and their home/away status is included as another field?
I've confused myself with this stuff. :confused:
View 4 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
Jul 26, 2007
Commission: IIf([Amt]<=10000,30), IIf(Int([Amt]/1000 Between 10000 And 50001)*3.5+3.75)
What I required to do is that when amount is greater than or equal to 10000 than Amt multiply by 30 simply.
and when amount is between 10000to 50001 than Amt/1000 multiply by 3.5+3.75
means that when amount is 15000 than according to per 1000 it will be calculated.
please dear help me out in this I required it very very urgently.
Please Please Please Please. ....................................
View 1 Replies
View Related
Apr 4, 2005
Hi All,
I am new to Access and trying to create a database for employee's leave record.
I created table 1 for departments i.e., A, B, C and so on
There is table 2 for employees i.e., Z, X, Y and linked with table 1 for their departments.
There is table 3 where first I have to select department (from table 1) and then employee (table 2).
All I want (in table 3) is:
Once I select department, the next field for employee show its respective employees.
Thanks is advance.
View 1 Replies
View Related
Feb 24, 2008
I am fairly new to Access and may very well be trying to walk before I can run.
I have an Excel Spreadsheet that is currently used to produce a Nominal Roll that is listed by the following:
Surname
Enlisted Month
Enlisted Year
POP Month
POP Year
I know how to transfer it across to Access.
I then need to be able to produce lists (The listings order needs to be Enlisted Month/Year then POP Month/Year) that can then be merged into tables 4 columns wide (where at the top of each table is a General Comment) and the names run down in alphabetical order then move across to the next column.
(Basically for example I have 24 names that needs to be divided by 4)
hopefully that is as clear as mud, any help would be greatly appriciated
View 4 Replies
View Related
May 8, 2006
Hi. I want to have all my fields on my form to be required and so that the user cant close the form if they are empty (error message). In the table properties I have them set to Required an have tried putting in a validation rule of IS NOT NULL but it still dont work. any help appreciated. thanks
View 1 Replies
View Related
Nov 19, 2006
First of all sorry for the stupid question. I am opening a new business and will recieve a specializes CMS system using Access as the DB. Do I have to buy Access to run it?
View 2 Replies
View Related
Aug 1, 2005
How do I set the Required property for field that already exists via SQL. I'm thinking along the lines of: ALTER TABLE table1 ALTER COLUMN field1 text(50) NOT REQUIRED.
But this doesn't work, any clues?
View 5 Replies
View Related
May 11, 2006
I'm having problems with the required field option in my table. I have a form that writes to the table and has a validation of the fields built in the code. If the validation is true, then it will allow the record to be saved.
However, I've also set those same fields in the table to be a required field. Whenever I enter data in those fields in the form and click the save command button, I get a message back telling me one of the required fields cannot be null. The thing is, it is not null. I entered data into the field through the form. Whenever I change the required field option to no in the table the problem goes away, but shows up on the very next field in the list that is set to yes.
Validation occurs within the form that writes to the table, but I still would like to have the security of knowing the fields are required in the table as well, just in case someone tries to be sneaky and go directly into the table to enter/modify data.
Is there something I'm missing? Is this a bug? I've actually set up another database the same way and don't have any problems with it. I'm not sure what's going on. Has anyone else had this problem?
Thanks,
cbdcolga
View 1 Replies
View Related
Apr 9, 2008
I want an access database to manage my correspondence after my motorbike accident.
I am sort of unsure on tables and correct normalisation so here's my plan so far.
Table 1
Company address details
Table 2
Contact name details
(I have a lot of different people from different departments in each company) so a contactID to table 1
Table 3
Media Type - letter, email, phone call etc
Table 4
Category - Legal, Financial etc
Also I want to link pdf images to each entry.
Does this sound okay or can tables 1 and 2 be merged? dunno if it would be easier to add names to an existing company rather than possibly adding the whole thing again.
Thanks all for looking
View 1 Replies
View Related
Apr 23, 2007
After researching this issue I have not found a satisfactory solution to this issue.
I currently have 1 Access query that is the basis for my VBA code_ Sql statement. both Query statements work when debugged. However, I am getting this error on execution of the sql statement in my vba.
Dies here: '<<<<<<<<<<<<<<<<
Public Sub ConnectCMIS(spar As String)
Dim sConn As String
Dim oConn As ADODB.Connection
Dim lCnt As Long
Dim sSql, strSQL As String
Dim rstOra As ADODB.Recordset
Dim rsAccess As New ADODB.Recordset
Dim fld As ADODB.Field
On Error GoTo ErrorHandler
DoCmd.SetWarnings False
sConn = _
"Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL=TCP)" _
& _.;......
strSQL = "SELECT " & _
"MEASNO, FTEMNOMENCLATURE, NOMENCLATUREMODEL, " & _
"EquipID As EQUIPMENT_ID, MULTIPLE_ID, JOB_GROUP, " & _
"PROJECT, PRIORITY, IIf(Len(Trim(COMPLETE_BY_DATE)) > 0, Mid(COMPLETE_BY_DATE, 3, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 5, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 1, 2), Null) AS COMPLETEBYDATE, " & _
"RequestorId As REQUESTOR_ID, " & _
"CALIBRATION, REPAIR, MODIFICATION, ACCEPTANCE, EVALUATION, " & _
"MAINTENANCE, SUPPORT, CMIS_LAB, SERVICE_LAB, WORK_CODE, " & _
"CHARGE_NUMBER, DISPOSITION, ReqComments as REQUESTORCOMMENTS, INPUT_RANGE_MIN, " & _
"INPUT_RANGE_MAX, INPUT_UNITS, OUTPUT_RANGE_MIN, OUTPUT_RANGE_MAX, " & _
"OUTPUT_UNITS, GAIN, CUTOFF_FREQ, INPUT_FREQ, REF_FREQ, REF_VOLTAGE, " & _
"EXCIT_VOLTAGE, EXCIT_ENABLED, FTIR_ACCURACY, OFFSET, OFFSET_ENABLED, " & _
"REQ_EMO1, REQ_EMO2, REQ_EMO3, REQ_EMO4, REQ_EMO5, REQ_EMO6, " & _
"SPARECODE, CALIBRATION_ID " & _
"FROM QS_SRUpdatetoCMISdrt " & _
"WHERE job_group = " & Chr(39) & spar & Chr(39) & ""
Set rsAccess.ActiveConnection = CurrentProject.Connection
rsAccess.CursorType = adOpenStatic
Debug.Print strSQL
rsAccess.Open strSQL'<<<<<<<<<<<<<<<<
If rsAccess.EOF = False Then
Set oConn = New ADODB.Connection
oConn.Open sConn
Set rstOra = New ADODB.Recordset
rstOra.ActiveConnection = oConn
rstOra.CursorType = adOpenKeyset
rstOra.LockType = adLockOptimistic
rstOra.CursorLocation = adUseServer 'default
rstOra.Open "CMIS.UDV_RFS_SR"
Do While rsAccess.EOF = False
rstOra.AddNew 'Then where you add the Oracle record instead of individual assignments you have
On Error Resume Next
For Each fld In rsAccess.Fields
rstOra(fld.Name).Value = fld.Value
Next
rstOra.Update
rsAccess.MoveNext
Loop
End If
strSQL = "UPDATE CMIS.UDV_RFS_SR SET PROCESSED_IND = 'S' WHERE job_group = '" & spar & "'"
oConn.Execute strSQL, lCnt
DoCmd.RunSQL _
("UPDATE TA_SR SET PROCESSED_IND = 'S' WHERE Job_Group='" & spar & "'")
rstOra.Close
Set rstOra = Nothing
oConn.Close
Set oConn = Nothing
Call MsgBox("Submittal to CMIS has been processed.", vbInformation, "Process Submittal Complete")
SubExit:
On Error Resume Next
If Not oConn Is Nothing Then
Set oConn = Nothing
End If
rsAccess.Close
Set rsAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error Number = " & Err.Number & "-> " & Err.Description, vbExclamation, "CMISStatus"
Resume SubExit
End Sub
1st query:"QS_SRUpdatetoCMISdrt"
SELECT QS_TT_GeneralInfo.BEMS AS RequestorId, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null) AS EquipID, IIf([Additional]=True,[Equipment_ID],Null) AS Multiple_ID, TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, First(TA_SR.RequestorComments) AS ReqComments, "S" AS PROCESSED_IND, Now() AS LAST_UPDATE_DATE
FROM (TA_SR LEFT JOIN tblEquipListingPerJobGroup ON TA_SR.Job_Group = tblEquipListingPerJobGroup.Job_Group) LEFT JOIN QS_TT_GeneralInfo ON TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId
WHERE (((TA_SR.Job_Group)=[Forms]![FE_SRForm]![JobGroup]) AND ((TA_SR.SubmittedSR)=0))
GROUP BY QS_TT_GeneralInfo.BEMS, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null), IIf([Additional]=True,[Equipment_ID],Null), TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, "S", Now();
View 2 Replies
View Related
Jul 26, 2007
Commission: IIf([Amt]<=10000,30), IIf(Int([Amt]/1000 Between 10000 And 50001)*3.5+3.75)
What I required to do is that when amount is greater than or equal to 10000 than Amt multiply by 30 simply.
and when amount is between 10000to 50001 than Amt/1000 multiply by 3.5+3.75
means that when amount is 15000 than according to per 1000 it will be calculated.
please dear help me out in this I required it very very urgently.
Please Please Please Please. ....................................
View 2 Replies
View Related
Sep 13, 2007
I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible?
Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.
View 2 Replies
View Related
Nov 14, 2007
Hi,
I found a couple of short threads on the forum discussing the above. None of them go sufficiently far enough into detal to help me solve my (relatively simple) problem.
My boss has asked me to produce both mean and mode calculations for the number of pallets we ship to our branches on the 30+ routes that we service.
So far I have managed to implement mean but mode seems to have me stumped.
I'm aware there actually isn't a mode function in Access as such but like mode, is there a way to calculate the most frequent occurrence of a number over a time period?
My query is already producing Min, Max, Sum (and now mean) results for the pallets we ship, can someone offer me help on this?
All the code that I have retrieved so far seems to be based on something similar to this:
select top 1 with ties your_column
from your_table
group by your_column
order by count(*) desc
I have very little to no idea about how to implement this into my query. Any help would be appreciated.
Thanks In Advance.
sausagefingers
View 11 Replies
View Related
