I have a 1000 records with 4 columns , now normalized.
I can use set to update and set some of the records. Perhaps there is a method I am missing. Is there some script or stored procedure I can run in sql to speed this up.
I have 181 units to match 1000 records as well as business leads any hints would be appreciated.
I am matching the DepartmentID in the department table back to the field on another table.
Thanks in advance
Regards
Angel
Greetings,
I have a friend who is using Excel to track a Greyhound Protective Association roster. These are folks that foster and adopt Greyhounds -- thus a worthy cause. It has over 600 rows of data.
I've attached 50 rows of sample data in Excel format.
Along with contact information, there are also fields to track up to 5 dogs. I've gone through the spreadsheet and tried to set it up for import to Access 2000. I have the dogs numbered 1 through 5.
I'd like to set this up in two (or more ??) tables, with Contacts in one, and the dog information in another. I'm guessing it will be one-to-many (Contacts -->Dogs).
However, I can't for the life of me figure out how to get this all together so that she can use a form-subform setup to input / access the data.
I'd like to pull up a name in the form, and have the subform display all of the dogs that this person has adopted, with the ability to add more dogs. Thus, they could have one dog, or 2 dozen dogs.
Any assistance would be greatly appreciated !! Thanks in advance.
Alrighty,
I am going to take another shot at making my inherited database like the others I see. It is an inventory questionnaire. It seems most of the information is stored in 1 table . I have read much on this forum about normalizing and I understand this is to stop duplicates. I would like to normalize this database and create it in Oracle. I have tried recreating it.
The database tracks assets and the people who own and manage them and there risk.
I have one table called
tblAsset - It holds everything about the asset including the risk rating and summary.
tblAssetformat - are the formats of the assets for dropdown query
tblImpactItems - is the risk impacts for dropdown query
tblMasterplan - are the decision teams dropdown query
tblRiskLevels - are risk levels for dropdown query
My form is usually a query and all the information on one screen.
When I tried to normalize it before it appears it should be the following:
tblAsset - Asset Name, Asset ID, Asset Description, Asset format, Asset Location
tblInventory - Conf Risk level, Integ Risk Level, Impact,
tblowner - Owner department, unit, masterplan.
Any assistance would be appreciated.
Hi all,
I am new to this forum and a rookie when it comes to Access. I inherited a pretty complex db and from my experience from other forums as well as trouble coming up with accurate queries I have come to the conclusion that normalizing the db is the only solution.
However, I really need help with this process.
I have attached a simplified version of my database with only the tables (all the #s and data are fake).
Basically what this db does is track lease hold improvements for various groups (ADT and RWA) and the tables are split up to reflect the 2 groups.
I will focus only on the ADT tables as the RWA is basically the same thing (Dont worry about GSA and the other stuff). The main table in the db is the ADT_Detail which includes all sorts of data regarding "Projects". I will provide information on each field:
Project- Project numbers numbers are unique, but often have multiple DCNs (and there's not always a project number so that can cause a null value for primary keys).
DCN - Each DCN can have multiple projects associated with it
TO - Task order can be the same for everything
SiteCode - A number that identifies various locations, there is another table that lists all the site codes and pertinent info on each.
CostBase - A currency figure on the original cost of each Project
Useful - length of useful life for each project
Complete-Expense - yes/no field
Complete-Cap - yes/no field
WIP- yes/no field
(any project can have 1 or multiple yes for these)
InServ Date - Date the project is placed in service
Start Amort Date - Date amortization starts on the project
date add LHI - the date the project is entered into the db
Fund- a number where the project is expensed
Post Fund - If a fundis not available for the project this is what is used by the DCN and is usually the more important one
Year- when the project begins
Post year - If a year is not available for the project this is what is used by the DCN and is usually the more important one
Deletion - yes/no box so that we can keep a track of what items we are deleting to compare with other periods
Deletion date - used to identify Quarter that the project was deleted
There is also a table labeled (ADT_Master) which contains other data for each project. This is pretty self explanatory when you look at the table.
Everything is also based on quarters when we ran queries to show additions, deletions, adjustments in certain quarters based on the quarters table.
This is probably more info than what is necessary but hopefully it will give you an idea of whats going on. Let me know if you need any further clarification and I will try my best to assist.
Thanks in advance.
Hi,
Just was asked to create a new db
Basically it's going to track provider/member information
Here is where I'm running into a issue.
I have a provider table, it has the name, rider#, and I need to list the 'clinic' the provider belongs too. I orignially thought since many providers can belong to the same clinic. I would created a clinic lookup table..
but then I started thinking, how in the world would I keep that updated? There are tons of clinics out there, and I have no way of knowing which ones need to be in the db.
So then I thought, should I just have clinic be a text field and the user will just type in the clinic name when they enter the new provider info? That would work, but it's also creates duplication in the db, and what happens if one person enters a new provider and spells the clinic name wrong? OR what happens if a clinic changes it's name all together?
I would have to run update queries to fix all that. Doesn't seem like the right way to go about this.
So how would you do it? Keep the database normalized, but also keep it easy for the users to update and maintain when creating records?
Hi folks.
I'm going over a laboratory application that I started a few years ago, trying to do a better job with some of the table structure that I set up, and improve the application. Right now, I'm working on the very first thing I did when I started building this application. This part has users pick a sieve stack setting for a size test, and record the grams of material weighed from each sieve in the test. This stack is based on the top and bottom size of the sample. In the original, I used a field for top and a field for bottom. In the new structure, it's together. There are over 100 different sieve stack combinations, and the number of sieves used in a combination for a test varies from 3 to 8. The way I was told to set it up is not in a normalized table structure, but it has worked fine for us to date.
I've gone in and broken the tables up into what I believe to be a normalized structure. The funny thing is, the size of the db is bigger with the tables put into a normalized structure than it is with the orignial, non-normal structure.
This seems weird to me. I've done this with other sets of non-normal tables in the application, and saw a subsequent shrink in db size because of the optimization of the table structure. Now I'm wondering if I've done this right, so I'm asking if some of you experienced hands can look at this and tell me if I've gone wrong in my attempt to normalize the table structure.
I appreciate any input in this issue, as it has me scratching my head a bit. I've posted db1, which is non-normal, and db2, which is normalized (I think). I put a few records in these, so you can get the gist of the application use. File size with these few records is negligible, but the application has over 18,000 records in it, and the file size difference is over 1MB, which was unexpected.
Thank you.
I’m working on a new database which will record furnace logs for a heat treat company. I did my best to normalize the tables. I’m trying to add new records through a select query which updates multiple tables. My problem is I don’t believe I did that great of a job normalizing the tables because I continue to get errors stating I have duplicate entries. It dose not make any sense because each table uses an auto number as the PK and one FK value from the tblProcessFurnace which is an auto number. Every new record should increment the PK. Thus making it impossible to have duplicate values.
View 4 Replies View Relatedhow i can normalized this data which has been imported from excel to ms access.
Channel
IslandsMalta UAE JordanSingapore
0%0%0%0%0%
0%4%5%5%5%
0%12%6%6%6%
0%12%10%10%10%
9%12%12%12%12%
12%12%12%12%12%
13%11%10%10%10%
[code]....
How does normalizing affect current queries and forms?I need to normalize a database here at work, the original creator made one BIG table.My concern is how will that normalization affect the current queries and forms I have created based on the current table?I tried the Wizard but that wont do the job I will have to do it manually.Thanks
View 2 Replies View RelatedHow to properly normalize dates in access. I have read many things and have actually normalized dates before in the past but feel that there is a specific correct way to accomplish this without having to jump through many hoops on coding and query design.
View 8 Replies View RelatedI need some normalizing my data properly, and then showing the values in a form.
Currently, my table relationships look like this;
However, there can be multiple Genres per Band and each Genre will be applied to multiple Bands, and I know this is a Many-To-Many relationship, but I'm uncertain on how to create this properly and then show it in a subform in a form.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
I have a form with a combo box. If the supplier(s) name is not in the combo box, it asks do you want to add that supplier address? If yes, a popup form displays, where the name and address are entered. THis updates the table. But the problem is the combobox doesn't refresh so the user isn't aware that it has updated.
I've tried updating the form, but that results in the question being asked again, do you want to add that supplier address.
Here is my code
Dim intNewCategory As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer
' Display message box asking if user wants to add a new category.
strTitle = "Shipper Database"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
intNewCategory = MsgBox("Do you want to add this Company and Address to the List?", intMsgDialog, strTitle)
If intNewCategory = vbYes Then
' Remove new name from CategoryID combo box so
' control can be requeried when user returns to form.
'DoCmd.RunCommand acCmdUndo
' Display message box and adjust length of value entered in
' CategoryID combo box.
strTitle = "Name Too Long"
intMsgDialog = vbOKOnly + vbExclamation
If Len(NewData) > 50 Then
intTruncateName = MsgBox("Name of the Supplier can be no longer than " _
& "50 characters. The name you entered will be truncated.", _
intMsgDialog, strTitle)
NewData = Left(NewData, 50)
End If
Hi all
I have a curly problem
A sent a mate a DB a while ago and I want to update it for him... so I need to send him something that will automatically update the forms, queries etc... progamatically i.e without him having to do anything to it.
Ideally, I'd like to front end/back end the DB. But server infrastructure doesn't allow for this. 2 networks are operating and they don't talk cleanly. Essentially, a FE/BE DB will work on one of the networks but not the other. Therefore, the DB is one whole DB so that BE tables don't need refreshing.
The way I see it... I have 2 options. Or maybe there are other options??
1. Export forms, queries etc... to the old DB or
2. Import the data tables into the new one.
At this stage I prefer option 2.
I have some idea how do this this but would appreciate some advice. Ideally, I'd like the user to click an update button to import tables, delete the old DB and transfer the new DB into the appropriate folder (and maybe rename the DB if it needs it??)
Any help appreciated.
I want to make an update code without referring to the form
My update query reads in is sql as follows
Dim SQL As String
StrSQL = "UPDATE products SET products.new = [products].[grossprice]-[products].[grossprice]*28/100"
I want to rewrite it in the following way :
Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = diff*28/100 "
However this effort fails since i have to refer to a form.Can i build a code without referring to a form?
Hi,
I have a database that has been in use for sometime but it needs some amendments.
I have burnt the db to cd and plan to make the updates and then take it back and import the tables from the one in use to the newly updated database.
I remember trying this before and it wouldn't let me import the tables as it said they had relationships.
Surely there is an easy way to do this? I don't have to delete the relationships, import the tables and then re-do the relationships?
Many thanks,
Housey
I have a database with details about employees in it.
There is a field with yearly salary in it and another with an ID number. Recently these slaaries have been increased by a non-standard amount. I have another table with the emplyee ID number and their new salary. I need to update the slaary figure in the first table with the salary figure in the second table. Is there anyway of doing this?
Any help would be most appreciated
Nick:)
I need to be able to update all the date records in a table using an update query. Any ideas of what i have to put in the 'update to' box to get all the dates to change from dd/mm/2005 to be dd/mm/2006
cheers for any help
Hi there people...i'm doing a databae project for A2 level coursework and have encountered a problem..please help me if you can.
I have a table of names. Also another table (actions) which contains several fields, but 2 of them are names, (1 being Proposer, and other being Actionee), are obtained from the names table.
However, when altering for instance the spelling of one of the names in the names table it does not update the spelling in the actions table. i have tried using the relationship-update option but this doesn't work because the names appear more than once in the actions table. if it were to appear just the once in the actions table then i wouldn't have any problem. but it is a requirement that the 2 names fields are in the actions table.
Any ideas of how to solve this?
can someone tell me how to make a form's data change when related data is changed in another form. E.g. If I change the price of a product in 'products' to £3.00, how do I make it automatically show that it is £3.00 in 'orders' just by typing in the product code?
View 1 Replies View Relatedhi everyone,
I have a "subformB" wich is inside a "MainForm". When "MainForm" loads up (and therefore subformB loads as well), "subformB" does not show the results it is supposed to show until I click on it. It doesn't update itself 'till I click on it. Can anyone give me a hand on this?
Thank you very much
I have used a make table query to create a table with text values. I need to convert these values to numerical data, for instance "Male" should become 1 and "Female" should become 0. I tried doing this in an update query, but had to use 2 separate queries. Don't know if a macro would work to update a table (there are no forms in this DB, it's all data fields). Same with code, I usually do this with forms. How can I update the raw data, which in some cases has multiple ifs, into the correct format? Thanks!
View 4 Replies View Relatedi have 4 tables in access and i need to be able to add information in one table and it to appear in the other 3 without me having to go in to each individual table and enter it.
Is this possible?
I have two tables. One table is linked to a text file that is always update by the another program. I have another table the is same exact one but it does not get updated.
I have access run these two tables to see the difference between. (Its basically and add/drop function) But after I run those two tables to see the difference, I want to update the nonlink file, so it is up to date with the "link" table. Thank you.
Good morning, I'm a newbie....and I inherited this db.
I have an existing table. I need to add two rows of information. When I add the rows, they are empty. I have tried running a query with all the fields from the table plus the two rows I need, but I loose 90+ records. What am I doing wrong???
Thank you for any help...
Here's my problem:
I have a table called WorkOrder. I have fields labeled:
JobID, which is an autonumber
WODate, which is formatted as =Date()
and WOID, long integer
Here's what I want to do and have done:
On my form I have WOID control source set to:
=Format([WODate],"mmddyy") & Format([JobID],"00")
I know that storing calculated values isn't the preferred method but it's what I've done.
The problem is I have a cmd button on the form, that when clicked asked for the WOID. This button is based on a query to print a specific work order.
The problems is that this calculated value (WOID) isn't appended to the table so nothing exists when the cmd button is click and the WOID is entered.
How can I update the table with this WOID for the specific job?