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
In my database you can search on either number or name. I have therefor, two combo boxes. and it works well, except that...
When I pick by number first, and then try to pick another record using the name combobox, the number combobox still shows the first search I did. I would like the two comboboxes to interact with each other...
I have created an Access 2010 Database for tracking inventory. As parts move to each location, quantities automatically subtract from the previous location. I now realized that some parts are destroyed.
The problem is the if I simply enter a negative value to indicated that a part was destroyed at one location, it appears as a positive value in the previous location. Is there a way to make negative values not affect a specific query?
how 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]....
I want to know if I change the menu bar in an Access db (such as take out every thing, edit, view, format, tools, etc. except file and exit) would it remain that way in every other db that was opened on that pc?
I want to restrict access to every tool except file, exit on only one pc (a public pc) and I am afraid to mess around with this in the event I screw something up again like I almost did yesterday...Security on that one pc is all I need, because the program needs to be accessible to managers for reports. This pc is in an out of the way place that allows employees to "play around" in it and I am not sure enough about how to secure it any other way. So, I reasoned that if I could restrict ability to roam on that pc then that is all I would need.
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
I've encountered a simple problem. =) I have a report based on a query. Of course due to the relations the query table should affect the report. But it does only with the filter parameters I put in the Query Design View, the quick ones ("filter by selection", etc.) that are in the result table view work there but doesn't affect the report. Still the query asks me to save it each time I apply any of these quick filters but make no changes at the end.
Any ideas how to make them work in the report? Or these filters are temporal and shouldn't work in that way? If so, what could I do to simplify customer's life?
(Access 2003, Access2000 based database)
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?
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
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 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.
All using 2010. I have a query that the date is based on the Saturday after that current day and is entered in manually. Is it possible to automate this with code that says whatever the current date is; to adjust to the next Saturday date? I know its a long shot but I just wanted to see if I can eliminate user input.
View 7 Replies View Relatedim working on an update query (written in VB coding) for one of my tables, for now im just setting the value to 1 (as currently all the data is set to 0, so for now it works), however id like to improve upon the query and get it to add 1 every time instead of setting it to 1 (i can work with it the way it is, but changing it would give me more info)
currently its at
DoCmd.RunSQL "UPDATE tblBatches SET tblBatches.batchCarryover = 1;"
Curretnly I have a query setup with Area, EventDate, & Complete. I want to be able to count each occurance of complete in current week. So far I have the following criteria under EventDate.
DatePart("ww",[EventDate])=DatePart("ww",Date()) And Year([EventDate])=Year(Date()).
So, this does in fact get me the records of current week, the issue I'm having is when an area has occurences daily. It will not bulk that area into a weekly count. I just need to be able to see how many occurances happen in the week total not daily. So my results could be.
Office1 5
Office2 1
Office2 1
Office2 1
Office2 1
Office2 1
Office3 5
How to reference current row in SQL string? I have a qst while using RowSource. I have 3 tables:
[1] CITY
col: _value _name
[2] STREET
col: _city _value _name
[3] ADDRESS
cal: city street
The problem ocurred when i designed ADDRESS table:
The RowSource of [city] is "SELECT _name, _value FROM CITY".
And I set the binding-column as 2, so that _name can be displayed and the real value is _value(integer).
But here comes the trouble: how can i set the RowSource of [street]?
The _value of STREET table depends on _city, and _city is _value in CITY table.
E.g. _name of STREET is "Wall Street" when (_city,_value) = (1,1),
But "Lincoln Avenue" when (_city,_value) = (2,1)
I want to use "SELECT _name, _value FROM STREET WHERE _city=CurrentRow.city" as RowSource, but what can replace the "CurrentRow"?
I need to Criteria between currently Month and Next Month. (we are in April because of this im giving this example : 01.04.2015-31.05.2015) .I tried this code but its not working :
Dateadd("M") between DateAdd("M"+1)
I have two tables:
1. Client Info table which contains the following fields:
Clientid
LastName
FirstName
2.DatesDistribution table
Clientid
DateAttended
The relationship is one to many for these tables.I have been trying to create a query that will show the following:
Clientid
LastName
FirstName
DateAttended (where this field only shows the most current date that the client visited)
Example:
ClientInfo has 2 records:
Clientid=jsmith
LastName=Smith
FirstName=Jim
Clientid=tjones
LastName=Jones
FirstName=Tom
[code]...
I have tried grouping the records in Client Info table to get distinct names and using a Last function to get the most current date with little success.
I have a problem with my query to calculate a most current date. I have try everything to explain all current data at each TubeNumber, but there are no calculated answers at TubeNumber 7, IC2, IC3 and IC4. Why not these TubeNumbers? See below my formula to find a current date:
Code:
FindACurrentDate: IIf([Time]=DMax("Time";"qlkp_FindACurrentDate";"[Date]=#" & DMax("Date";"qlkp_FindACurrentDate";"[TubeNumber]='" & [TubeNumber] & "'") & "# And [TubeNumber]='" & [TubeNumber] & "'");"This is most current date!")
I have send my database as an attachment with my question.
I am attempting to build a small database for my firm to keep a track of equipment. The equipment can be in one of three places. In the warehouse, out on a job or at the repairers. I want to create a query that will let me know where a piece of equipment is at any one time. I'm sure my tables are set up correctly. I have use a union query to work out when equipment is on a job or in repairs but I need one to show me where all the equipment currently is.
View 1 Replies View RelatedI have a query that does a great job at fining duplicate master records
How to this query so that it fining duplicate master records like the query show and add one more record that will link up as shown in the example.
[sql]SELECT subqry2.maxdatereferral
,[PSB Accout Linkingqry].master
,[PSB Accout Linkingqry].RecordNumber
,[PSB Accout Linkingqry].FirstName
,[PSB Accout Linkingqry].LastName
[Code] .....
I am trying to query any date before current month. My data is employee start dates, thus my end aim is to pick up all employees at end of last month.
View 1 Replies View Related