Need Expert Advise On Normalization
Jun 19, 2005
The question is whether or not to normalize the fields "Place of Birth/Place of Death" in my 100'000 records famous people data base. So far, 15'000 different birth places are deployed. Only 3'000 of them are used more then once, that is to say from 2 to a maximum of 1000 times (New York City). 12'000 birth places are therefor used only once up to now. So, a table of 15'000 places would need a 3-digit (all characters used) or even a 5-digit (numbers only used) identification. In the latter case the ident would often be longer than the returned value (Wien, Oslo, Rom, it's in german, you know). Furthermore, instead of just entering a birth place like "Novodny Chomarowsky" I had to search the table of places whether or not the entry has been used yet. Please supply strong pro-normalization-arguments.
View Replies
ADVERTISEMENT
Oct 14, 2005
I have created a switchboard and some additional forms. I would like the frontend of the database to display my forms in a maximized view when the users opens them. Can anyone advise me how to do this?
For some odd reason when I make changes to the forms (example) resizing them my clicking and dragging the edges, the changes will not save. Can anyone advise me what I may be doing incorrectly? :confused:
View 3 Replies
View Related
Oct 27, 2005
I am using Access 2000 and have the following issue I need assistance with please.
I initially had no primary key set on the table and the form and all the data from the tables showed fine in my form. However, I wanted a primary key to prevent a user from accidentally typing a duplicate Repair Order number in the form.
I went back into the table and set a primary key as our company’s Repair Order number. Since this number should never be duplicated I wanted this as the primary key. I had a ton of issues and kept receiving messages that indicated I had duplicates in the table. After running a query through the wizard to find the duplicates in the Repair Order column, which the query could locate, any, I finally exported the data in the table into a spreadsheet. Then I created a new table and set the primary key to Repair Order Number and set the index in the properties to “yes, no duplicates”.
However now when I go over to my form, the field for the Repair Order number, Model number, Serial number and Phone number now show this error message in the field #Name?, and will not allow a user to type any text or numbers into these four fields. The other fields do show the data from the table. I went through help for information on #Name? and verified that I do have Msowcf.dll and it is not missing from my computer.
All the other data was pulled from the table into the form except for these four fields. I did verify that there are no duplicates in the Repair Order column of the table but the Model and Serial numbers can have duplicates as well as the phone number.
If I change the index properties under the primary key of Repair Order Number and tell it to allow duplicates these issues go away but it seems I can't prevent duplicates from occuring in that particular field then.
Another note. I do have fields that work from a “Date of Service” that calculate out adding 10 months to the Date of Service as a contact date and another field that calculates out 12 months as an annual service date. Those seem not to be affected and the formulas are working fine in those fields on the forms.
I am not very familiar with Access and I am still in the learning stages. Can someone give me some insight on what I may have missed or need to correct? Please be specific. Any assistance would be greatly appreciated. This one has had me stumped for several days.
View 3 Replies
View Related
Dec 8, 2006
Hi,
I have the following forms setup:
frm_Main (Switchboard)
frm_Customers (Form)
frm_SalesDetailsSub (SubForm on frmCustomers showing basic details of items they have bought)
On frm_Main I can search for Customers by Surname using the following code behind a search button on the form:
On Error GoTo Err_Handler
Dim stQry
Dim stDocName As String
stQry = SearchSurname() 'Query containing tblCustomers
stDocName = "frm_Customers"
DoCmd.OpenForm stDocName, acNormal, stQry, "((([tbl_Customers].[Customer_LName])like[Customer Surname?]& ""*""))"
Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
Resume Next
End If
This works fine, however I also want to search by items on the subform frm_SalesDetailsSub by using another button on frm_Main. I have tried code like the one shown below.
On Error GoTo Err_Handler
Dim stQry
Dim stDocName As String
stQry = SearchSaleItem() 'Query containing tblCustomers and tblSales Items linked via ID
stDocName = "frm_Customers"
DoCmd.OpenForm stDocName, acNormal, stQry, "((([tbl_Sales_Details].[Sales_Details])like [Item Description?]& ""*""))"
Err_Handler:
If Err.Number = 2501 Then
Resume Next
Else
Resume Next
End If
I have tried lots of different variations of code, but can't seem to crack it.
Any advice is welcome.
Daz.....
View 8 Replies
View Related
Feb 7, 2008
Hi, I'd like to ask for help about Access, ASP - all this is new for me...
I'd like to make an Access based website for searching, comparing and adding some electrical appliances. These appliances are supposed to have a lable name, type, serial number and they also have some options. (Just like cars: volvo, s80, color red, airco).
My question is about the structure of the database: is it better to store for example the "colors" in a separate (related) table in Access or to have one table for everything and use the lookup option with dropdown menu? Can I connect such a dropdown list from the lookup in database to the website using ASP? And if some options have options themselves (like manual airco/automatical airco), what is the best structure for it?
The final product must be a site where new models can be added and available appliances can be searched and compared.
Thank you for your help,
Alex
View 13 Replies
View Related
May 12, 2005
i have a query that provides data like so:
IDNumber - Units - WeekNo
55643 - 180 - 1
55643 - (Null) - 2
55643 - (Null) - 3
55643 - 135 - 4
55643 - (Null) - 5
55643 - 0 - 6
55643 - (Null) - 7
is there a way of adding something to the SQL to make it fill in the Null values to the most previous value of Units? in other words, the top 2 Null's would become 180, the next one down would become 135 and the bottom one would become 0.
since data is not entered weekly, these Null's are inherently there for certain weeks and i am hoping that with the Nulls replaced, i can produce a bar chart from this query for a report. if the Null's remain, i am getting 'gaps' in my chart which look like zero's.
many thanks.
View 2 Replies
View Related
Nov 4, 2005
Rich helped Latex88 a little further down and the advise given was:
"=Count(*) in the control source of a textbox in the subform footer will do it, no vba is needed"
I tried this out out on a few of my subforms and it worked on all but one. The recordsource of the one that it does not work on is based on a query. When I changed the recordsource to a table then the recordcount works.
Curious as to why and is there a work around?
Thanks a head of time,
Shane
View 9 Replies
View Related
Dec 6, 2007
Hello,
Does anyone know of a listing or a publication/website where I can place an ad for an expert to develop an Access database? We need someone to do this work for our organization.
Thanks.
View 5 Replies
View Related
Jan 9, 2008
I work for a property management compnay and they have put me in charge of coming up with a way to go some what paperless. What they are wanting is a way to enter a work order for a particular client (i.e description of work to be done for maintenance). This would then need to be printed in a report to be faxed over to the workers. This info would then need to be pulled out and placed into an invoice where, where items could be entered such as items bought ets.
My company is willing to spend thousands for software, but I think it is insane. to do so.
I was thinking that surely this could be done in access. I have an understanding of how access works (forms, reports, tables, etc.) but I have never designed one like this.
It does not have to be complex and it does not have to have a lot of formulas, I was thinking that the total could be placed in field when the invoice info is entered. Only really calculation would be adding the total of the items.
Anyway is this something that can be done relatively easily by someone with my experience, or is this to complex.
Thank You for your time
View 1 Replies
View Related
Feb 17, 2008
I am an inexperienced user, part of my job is to up date data files daily, have been doing so for 7 months. Problem arose when I accidentally deleted a link and required IT assistance trying to locate where it had gone--
that's when we discovered that I was not updating the company-wide server but a separate shortcut that a previous (and briefly employed individual had created).
Now IT and I tried a few tricks to combine or join the data I had entered (this could be history of up to 2000 assets), but have not figured out just how.
I have backed up my data on a thumb drive to prevent loss in case of my hard drive accidentally failing or crashing due to weather, now I need advice on how to put that data into the main Access data base--w/o having to do it all manually again.
Does anyone know how to do this?
I would greatly appreciate your help!
View 2 Replies
View Related
Aug 17, 2006
Hi!
I've written a querry - and the results that come back dont look right. Some rows have data in and others dont.
Has anyone seen something similar?
Does this mean that the data are probably incorrect?
Any advise?
A.
View 1 Replies
View Related
Oct 19, 2005
I have a situation. In completing our Annual Report for our Accrediting body for my school, it requires us to complete a 'Completion and Placement Chart'. This chart was provided to us in Excel. The problem was that we needed a system to automaticaly update this chart as the year progressed so we can visually see our completion and placement rates. With this, I created an Excel sheet with the Completion and Placement Charts and made it so it can be updated as I entered a students information in.
Although it does calculate the data properly, the process is extremely ugly. It is terrible in appearance, function, and by all means, far from user friendly (I understand it, but it takes some explaining to do for someone else). This Workbook that I created houses almost all the information I need with the exception of it being entirely unsafe and far from being secure and data entry is a nightmare.
So, this brought me to Access. I created a database for this purpose (first time using access as well). It works like a dream in storing all the data that I need and keeping it safe. I needed to have a 'Placement Tracking Document' for each student outlining the students information for the accrediting body. This was done wonderfuly in Access and could not be accomplished through Excel (that I know of). However, I NEED to have Access tally all the information in the 'Completion and Placement Charts' like the Excel Workbook does. I need to keep the exact format of the 'Completion and Placement Chart' because it is a standard form in Excel and I can not steer away from it.
How can I get my DB in Access to comunicate with the 'Completion and Placement Charts' and have it automatically tally like my workbook???
I have attached some screen shots of my database and the entire Excel Workbook (Its edited because of confidential data) so you can understand a little of the chaos.
I rarely used Excel and never used Access until now. It took me nine months to create what I have because I was learning as I went. The books that I purchased to help me out do not touch this subject nearly enough (Access Cookbook, Access for Dummies and VB & VBA in a nutshell).
My point is, any help or direction in where to get thorough information on how to acomplish this is more then welcome. Any comments on what I have as well is more then welcome (negative or positive).
Thank you thank you thank you thank you in advance!
Oh, and use the buttons in the Excel Workbook to navigate.
John D :confused:
View 7 Replies
View Related
Jun 20, 2006
Good Morning,
I am new to Access and I welcome expert guidance on the relationships of my database. The database will be used to track employee training and although I've enjoyed limited success so far I'm hoping that eventually I'll get it right.
The database has to do just a few things so it doesn't have to be very complicated. It basically has to store employee, training event, and completed training event information. The attachment shows the tables and their relationships.
The are basically two types of training that the database needs to store. The first is training that applies to everyone and the other is training that only applies to certain job specialties. These types of training are identified by the ProjectMandatoryCode and the JobCode respectively.
The ProjectMandatoryCode is a default value that is automatically entered everytime a new employee is entered into the database (EmployeeInfo table) and it is also a default value when a training event is entered into the (TrainingEventInfo table) that applies to everyone. The employee JobCode is a selection when a new employee is entered into the EmployeeInfo table.
When the user wants to enter a training event that is job specific, they are able to select a JobCode from a combo box/option group and this code is written to the record. Selecting one button from the option group writes a default value (ProjectMandatoryCode) to the TrainingEventInfo table while selecting the other button enables the combo box so that a specific JobCode can be entered against the event.
Everything works fine except... so far I've been unable to produce a report that lists every training event that pertains to the employee whether it be ProjectMandatoryCode or JobCode related. I can query and produce completed events or a list of what events are required but nothing that is complete. Ideally, I should be able to produce a complete training plan for the employee which shows all necessary events and where the employee has completed the task I should be able to show the DateCompleted.
This failure has led me to doubt my table relationships since all my efforts at various query joins have failed. Is there anyone who could enlighten me so that I could learn from this, complete this task, and apply this new knowledge to future databases? To the person with the answer, thank you in advance for your assistance. Respectfully,
Dale Gagnon
View 5 Replies
View Related
Nov 1, 2004
please see post #4
View 3 Replies
View Related
Mar 14, 2005
I have a very simple SQL stmt
Select * from MyTbl order by MyID
The error: "Syntax error in ORDER BY clause"
If I remove the order by clause, it works. Now, I have been programming for 30 years and for the life of me, I cannot figure this one out. It is my first use of 'order by' on this ISP so I am assuming there is a server thing? Can anyone suggest to me what this might be? Thank you very much.
-Extremely Frustrated (Ken)
View 10 Replies
View Related
Jun 28, 2006
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name
FROM mtgwl.loan_payment lp
inner join mtgwl.deal_loan_relation dlr on
lp.loan_id=dlr.loan_id
and lp.time_out='9999-12-31-23.59.59.0'
and lp.as_of_date='2006-04-30'
and dlr.time_out='9999-12-31-23.59.59.0'
and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr
on dlr.deal_id=pdr.loan_id
and pdr.time_out='9999-12-31-23.59.59.0'
and pdr.type='PRODUCT'
inner join mtgwl.product_control pc
on pc.product_id=pdr.deal_id
and pc.time_out='9999-12-31-23.59.59.0'
and pc.product_name='GSAMP 2003 AHL'
View 2 Replies
View Related
Jun 28, 2006
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name
FROM mtgwl.loan_payment lp
inner join mtgwl.deal_loan_relation dlr on
lp.loan_id=dlr.loan_id
and lp.time_out='9999-12-31-23.59.59.0'
and lp.as_of_date='2006-04-30'
and dlr.time_out='9999-12-31-23.59.59.0'
and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr
on dlr.deal_id=pdr.loan_id
and pdr.time_out='9999-12-31-23.59.59.0'
and pdr.type='PRODUCT'
inner join mtgwl.product_control pc
on pc.product_id=pdr.deal_id
and pc.time_out='9999-12-31-23.59.59.0'
and pc.product_name='GSAMP 2003 AHL'
View 1 Replies
View Related
Jun 5, 2005
ok im working on a database that a company sends its employee to school and tracks there training.what classes they take what not.. my tables were fine until i realized that supervisors were also employees so i need to add a supervisorID i dont know how to do it without screwing up all my foreign keys i assume it goes into my employee table and department table but then where do i put my departmentID foreign key?
tblemployee(employeeID,firstname,lastname,phone,hi redate,payrate,DepartmentID)
tblDepartment(DepartmentID,name,supervisor)
tblClasses(ClassID,name,Description,credits,sectio n,TrainingID)
tblTraining(TrainingID,type)
tblClassEmployee(classID,EmployeeID,instructor,dat e)
View 2 Replies
View Related
Jan 11, 2006
I currently have a database which I believe is in 3NF. It's completed as such, but I have recently come accross normalization methods. Because of this I would like to know how my 3NF tables could look in 0NF, 1NF and 2NF. For both the purpose of ensuring the database is correct now, and for future reference...to do a more professional job next time!!Here's how the tables currently look:CUSTOMERS(CustID, CustName, CustAddress)ORDERS(OrderID, CustID, OrderDate)ORDERDETAILS(OrderID, BookID, BookPrice, Quantity)BOOKS(BookID, AuthorID, BookName, BookUnitPrice)AUTHORS(AuthorID, AuthName, AuthAddress)It's just basically a simple book royalty system. I have looked around various sources for examples, but my database has a few more tables in 3NF than most examples show.Thanks guys.
View 1 Replies
View Related
Oct 21, 2006
http://i74.photobucket.com/albums/i244/waloop/TempEmployment4.jpg
For my job history entity should I just store my placement number and remove the candidate number? Since placement number already determines the candidate number in the placement table.:confused:
View 3 Replies
View Related
Apr 27, 2007
Please could you experts assist me on normalizing my tbl structure below-
Employee_EmployeeID
CompanyName
Employee_FirstName
Employee_Surname
Employee_Sex
Employee_DOB
Employee_Coverage
Address
City
County
PostCode
StartDate
LeaveDate
Employee_Health
Employee_Dental
Employee_Travel
Spouse / Partner_FirstName
Spouse / Partner_Surname
Spouse / Partner_Sex
Spouse / Partner_DOB
Spouse / Partner_Coverage
Spouse / Partner_Health
Spouse / Partner_Dental
Spouse / Partner_Travel
Child 1_FirstName
Child 1_Surname
Child 1_Sex
Child 1_DOB
Child 1_Coverage
Child 1_Health
Child 1_Dental
Child 1_Travel
Child 2_FirstName
Child 2_Surname
Child 2_Sex
Child 2_DOB
Child 2_Coverage
Child 2_Health
Child 2_Dental
Child 2_Travel
Child 3_FirstName
Child 3_Surname
Child 3_Sex
Child 3_DOB
Child 3_Coverage
Child 3_Health
Child 3_Dental
Child 3_Travel
Child 4_FirstName
Child 4_Surname
Child 4_Sex
Child 4_DOB
Child4_Coverage
Child 4_Health
Child 4_Dental
Child 4_Travel
Child 5_FirstName
Child 5_Surname
Child 5_Sex
Child 5_DOB
Child 5_Coverage
Child 5_Health
Child 5_Dental
Child 5_Travel
Child 6_FirstName
Child 6_Surname
Child 6_Sex
Child 6_DOB
Child 6_Coverage
Child 6_Health
Child 6_Dental
Child 6_Travel
Notes
DateUpdated
Hope you can help me!
Ta
Kasey
View 3 Replies
View Related
Sep 10, 2007
I have decided to normalize further and now have brain freeze. I have a table 'tblComputers'. I have removed Computer Make + Computer Model to own tables and linked via ComputerMakeID & ComputerModelID, this is working OK.
I then decided to remove RAM to its own table. I created tblRAM. I added additional rows in tblComputers 'Memory Slot 1' Memory Slot 2' etc. tblRAM has 2 columns RamID & RAM so....RAMID 0 = 0 RAMID 1=128 RAMID2=256 etc..
My issues? What to link RAM to relationship wise then allow users to select multiple instances for different memory configurations to save values back to 'Memory Slot 1' 'Memory Slot 2' in tblComputers?
Hope that makes sense?
Regards,
Phil.
View 4 Replies
View Related
Oct 8, 2006
I currently have three tables.
The first table contains Employee ID, and Name of Employee
Second table contains Employee ID, and an autonumber
The third table contains an autonumber and a unique PIN
The 1st/2nd table are connected by Employee ID, the 2nd/3rd table connected by the autonumber. Now not every Employee gets the unique pin and the 3rd table gets re-done every year. This structure works great for all "current" employees. Employees that leave get deleted out of the 1st table, thus causing them to get deleted out of the second table due to relationship. This becomes a problem because I need to keep a record of who had what unique PIN, regardless of who is still assigned one or not. Anybody have any ideas on how to do this.
View 4 Replies
View Related
Nov 20, 2007
I'm not a programmer and know very little about setting up access databases other than the very basics...however...due to my politics background, I'm setting them up for the local government....just my little disclaimer about why I'm asking stupid questions.
I am creating a database for multiple users who are of two sorts. The users deal with either the setting up of a contract, or with the payment of a contract. Both of these groups need to be able to access different information about the same contracts. What seems most logical to me is to create two tables in the same database, one for the contract, and one for the payment information and have both update when one or the other is worked on.
My basic set up for now is this:
Table One: contract information
Table Two: Payment information
Link Key: Serial Number from contracts
These are my questions:
1) is there a way to set this up so that the only way someone from the payments group can create a linked record is if a record with the same serial number already exists in the contracts table?
2)Is there a way I can set it up so that when someone in the payments group starts to input their information, they can find the serial number they want, and have certain fields show up? For example: I want them to be able to search for a serial number and have the contract name show up with all of the previously entered payment information, then have a new field each time they go into it for their next payment (if that makes sense).
3) When linking, hopefully in such a way as to do the above, do I need to have the same field name in both tables, or...? I'm a bit confused as to how this works as initially i created a field in both tables that would have the same serial number and tried to link those..and it didn't work.
Sorry if this is so confused...I'm sort of learning as I go along.
View 6 Replies
View Related
Dec 8, 2007
I am having some questions on a order entry DB I'm working on, (modifying an existing one). I've attached a shot of the current relationships to demonstrate. Basically to start this was an order entry db to place orders to one vendor, so this was not a problem then... But.. Now I am modifying it to order from more then one vendor. So, I have the order form. You first pick the Vendor the order is going to, then the Customer and delivery site. Next you move to a subform to pick your items from a dropdown. So the problem... I have a new table for the Vendors. This table is related to the "Order Table" on a one to many. But what I also need is the VendorID to be in the table "tblItem" Thus when the Vendor is picked in the main form ,it can be filtered in the subform to only show items from this vendor. Now this would work by placing the Vendor ID in the "tblItems" table and relating the two. But would this be proper normalization to have the VenforID as a FK in two tables???
View 8 Replies
View Related
Mar 13, 2008
Newbie Access user here. Using Access 2003.
I am putting 80 rows (personnel) of data with about 20 columns (training courses), to track training for my people. Is this enough that I would need to make several tables, or am I correct in thinking I can put it all in one table? From what I have been reading, this is a pretty small table, but I want to make sure.
View 8 Replies
View Related
