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 RepliesThis 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.
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?
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.
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.
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
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 RelatedI 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
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
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
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
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:
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.
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
I have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.
How can I export three tables into a single excel workbook.
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have some experience doing 'Update Query' using two different tables but I'm having a hard time doing an 'Update Query' using 3 tables.
I have my source table TP05XY with the fields 'Mark' 'Date' 'UTM_Edig' and 'UTM_Ndig'. Mark and Date are my primary keys (they together uniquely ID each record). I have my Observations table with the fields 'Mark' 'Date' and 'Obs_ID'. The last table is Locations with 'Obs_ID' 'UTM_E' and 'UTM_N'.
I want to update my fields UTM_E and UTM_N from UTM_Edig and UTM_Ndig. However, to do so, I have to go from my TP05XY table, through Observations table to update Locations table. Table TP05XY is joined to Observations through 'Mark' and 'Date' and Observations table is linked to Locations through 'Obs_ID' field.
I have tried a few options without success ... anyone knows how to do it?
Thanks,
Josée
I have being playing with ms access but I really don't know much about it or databases in general.I have created a very simple database to gather twitter following/followers data for research purposes.One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends).Both fields are numeric and contain the users id's.In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).So the data would look like this:
boss - client - type
12345, 67890, 1
12345, 54321, 2
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321...In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.So the above row looks like this:
12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2
That works just fine.For several reasons I also needed data of the form source - target.So I also made another table (table02) of this form.
67890, 12345
12345, 54321
...
In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id..So table02 lookes like this
67890-12345, 67890, 12345
12345-54321, 12345, 54321
...
Both tables also have a date/time stamp for each line.As you can see, table01, having also a type field is bigger than table02.The problem is when I try to append data, exactly the same data in both tables.Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.Still I get the same result, table02 takes a very long time to finish while table01 finishes in no time.
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
View 14 Replies View RelatedI have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
I want to fill local tables in some application with disconnected recordset. The tables in the front end application having the same table structure as in the back end database. The front end application was linked with the back end password protected database tables. I want no connected linked tables in the front end application. How can I fill the local tables in the front end application with the back-end password protected tables?
View 8 Replies View Related