Specific Normalisation Question
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 Replies
ADVERTISEMENT
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
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 2 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
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 8, 2015
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
View 3 Replies
View Related
Oct 6, 2005
Hi,
I have recently been doing a lot of work on this area. Im able to export to where i want to and run macros through the VBA code inside of Access to edit the spreadsheets. This is ok if your making a new excel workbook/worksheet.
But what im stuck on is exporting to a so called template in excel. I can export to it at the moment but creating a new worksheet, in which i have to then cut and paste the data into the correct worksheets through code and then delete the worksheet that i had been working from (which is annoying because you have to confirm the deletion of this worksheet, which is why i couldnt really do the process this way).
What i want to know is there a specific way of telling the data you are exporting from a table/query/querydef to go into a certain worksheet and into a certain cell. For example; a list of names, i want all the Surnames to go into a worksheet called "Claim_Breakdown" and start from cell "A15" downwards until they have all been exported into the worksheet.
Anyone have any ideas on how i could achieve this? Thanks.
View 4 Replies
View Related
Jul 23, 2013
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies
View Related
Oct 8, 2015
I am trying to figure out how to make a button that sends an email to a specific email address, containing the information from 1 row.
we have rows where we put the the address, the quantity and the time interval we can collect the packages in. these information have to be send to a trucking company. I want access to send an email to the trucking company's email address, and not a whole report of all the rows, but be able to choose to send row 1,2,3 etc.
View 1 Replies
View Related
Jul 14, 2007
so i have an interesting question and im hoping that someone can help on this one. i need to pull date from a specific table, no problem, that's written and working fine, next i need to be able to join the data from another table by a primary key, again no problem. third, i need to be able to select the date (using WHERE) for a specific date range. (i.e. i enter the date range of 01/7/2007 to 15/7/2007) and the query comes back only showing the data from that specific time, not the data from before or after. this is where my problem lies, all the entered data is being shown after entering my date range. i am going to include my SQL statement, just so you can actually see what im really talking about.
SELECT srealest.Name0, srealest.Dist1, SREpayments.Face2Pd, SREpayments.Penalty2Pd, SREpayments.[2paid], SREpayments.Face3Pd, SREpayments.Penalty3Pd, SREpayments.[3paid], SREpayments.Face4Pd, SREpayments.Penalty4Pd, SREpayments.[4paid], srealest.Map, srealest.Parcel, srealest.LeaseHold, srealest.TaxRebate1, srealest.TaxFace1, srealest.TaxPenalty1, srealest.TaxYear, srealest.BillNo, srealest.PdRebate1, srealest.PdFace1, srealest.PdPenalty1, srealest.DatePd
FROM SREpayments INNER JOIN srealest ON SREpayments.BillNo=srealest.BillNo
WHERE (((SREpayments.[2paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[3paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[4paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) Or (((srealest.DatePd) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]))
ORDER BY srealest.Name0;
any thoughts or ideas on how to accomplish this would be greatly appreciated!
View 2 Replies
View Related
Oct 3, 2005
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
Can anyone tell me how to do this please?
View 11 Replies
View Related
Jul 9, 2015
I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.
Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.
View 12 Replies
View Related
Mar 21, 2006
I have a table & form which has a field called address. I need to create a query that lets me select all addresses which may contain say George Street, this includes lock ups and individual addresses for that address, i hope you will understand and be able to help
View 1 Replies
View Related
Mar 3, 2005
Hi,
Does anyone know some code that will take me to the record whose field "A" has value "B"?
It sounds very simple but I can't find anything the does it!
Basically I have a table or enquiry records, some of which are related. I just want to be able to click on a button to take the user straight to the related record. The current design does this using a filter but this is pretty rubbish as it meanhs that in order to search on all records I need to select all records again first.
View 1 Replies
View Related
Sep 29, 2005
The following is a function in my Form "Enter"
--------------------------------------------------------------------------
Private Sub PoNum()
Dim strForm As String
Dim strwhere As String
strForm = "GoodOne"
strwhere = "[Forms]![GoodOne]![Orders3].[Form]![PO_Num] ='" & txtPO & "'"
If [txtPO] <> "" Then
If DCount("[Po_Num]", "Orders", "[PO_Num] ='" & txtPO & "'") = 0 Then
MsgBox "Sorry, No record matched"
txtPO.SetFocus
Else
MsgBox "yes"
DoCmd.Close acForm, "frmPopUp"
DoCmd.OpenForm FormName:=strForm, wherecondition:=strwhere
End If
End If
End Sub
------------------------------------------------------------------------
"Orders3" is the subform of Form "GoodOne" .
When the user enter the PO Number in txtPo, and press "ok" , it will go to
that record.
BUT it just show a new blank reocrd instead.
What's wrong with the programme ??? (is it the matter of subform ?)
Thanks
View 3 Replies
View Related
Aug 3, 2005
Hey everyone,
Ive got a bit of a problem with my database at the moment. Here are the two tables im specificly having problems with:
Item Stock
Item size
Serial Number
Delivery Date
Date out
Stock level
Item Fitted
Item Size
Date fitted
Reg number
Date Removed
Serial Number
Position
Here I have Item stock as my base table. Item Size and Serial Number info is collected from the base table and put into a combo box.
What im trying to do is when a specific item has been fitted, for access to note the size, serial number and date fitted, and place it into the correct record in the base table. serial number and size to be put in its respective table, and date fitted to be put in the "date out" field.
This is rather annoying i know and a bit difficult to explain, if anyone could help over msn that would be great - david_4321@hotmail.com
Thanks
David
View 1 Replies
View Related
Aug 16, 2005
Hi all
Im wanting to open a sales report, but I want to be prompted to enter dates FROM and TO so that I can get specific sales.
Example
"Enter start date" here i'd enter 1/8/05
"Enter end date" here i'd enter 31/8/05
that way i'd get all sales throughtout august. Then when september comes round I could enter new dates and save me having to go in and change the filter.
Can anyone help?
Thanks
View 3 Replies
View Related
Oct 16, 2006
This is an Access101 question to be sure, but I am not a major user.
What I want to do is:
Pull up one name and:
See the name of project worked on. What if they work on multiple projects?
Performed function daytime
Nighttime
Backup night
In house night
Perfromed Specific task A
Performed Specific Task B
And enter notes or remarks.
What is the best way for me to create this?
Many thanks
View 1 Replies
View Related
