How 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 RepliesHi 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 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.
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 have a parent table [Case] and a child table [Action], in a one-to-many [Access 2003].
The parent has an open date and a closed date.
The child has a received date and a completed date.
The child may contain more than 1 record that matches the parent.
Date fields for activities that have not yet happened are blank.
A typical example might be one parent and two child entries for a total of six dates fields.
I am after only a [single] most-recent action date of the six [there could be a tie for that most recent date, and then there would be two records returned in the result].
I am working for a table that includes a list of every parent record with the name and date of the most recent, or latest activity date.
After two days I decided to ask for help.
Thanks,
I want to use an expression to compare 2 dates and calculate the date of the last set of accounts for a company. The user will enter(DD/MM) of the company year end e.g. 31/12. I then want to compare this with todays date (in another field) to ascertain if the month has already passed in the current year and hence calculate the last year end. i.e. If (current month>company month end), year end date is DD/MM/YYYY (where YYYY is taken from today's date), year end date is DD/MM/(YYYY-1). I am not a programmer and although I understand database theory (from the teaching perspective) I am not expert in developing databases.
View 1 Replies View RelatedI'm having difficulty linking these two tables' date fields in the query qryDivVolShiftCount - what I'm doing wrong?
View 2 Replies View RelatedAny way of setting up a table containing the following date/time fields.
StartDate
StartTime
EndDate
EndTime
Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.
In either scenerio, how do I subtract the two in a query for a report?
Hi
I am currently creating a report form which only calculates
fields/columns in my main table. However I am currently struggling
counting text fields and checked fields as well as data fields in this
table.
I want it to count all the "yes" checked fields in my main table targeting
another column's data e.g I've got column with Category listed as 1, 2, 3, 4 and 5.
Along with this there is a check box to say that a job was completed under cat. 1,
2, 3, 4 or 5.
I have managed to get the formula to cound all checked boxes in the databases,
but what about targeting only say category 1's checked boxes or category 2's checked
boxes. I need to work out %'s from these individual totals as well.
Any suggestions on this would be great!
Elza
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
Do fields in Access Tables have a 'Number' property that you can use to reference them?
For example, if you have the following fields:
EmpID
FName
LName
Department
Hire_Date
Is there a way to query an employee's Hire_Date by telling Access to return the value that is in the 5th field [Hire_Date]?
[URL] ....
I have at least 3 relates tables in my access database.the first(sessions) table stores session detailes like id,date,time
The second one (tblemployees) contains our employees details like name, idp ,the third (attreq) relates the first table to second it stores ids of sessions and ids of personals that which determine which personals have attended in special sessions.
Now,my problem is that I want when a personnel log in and opens "confirmed session"form ,the access check and open records that this person have attended. How can I do it?
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 RelatedHi,
My database has 4 tables. Table 1 is assembly line 1 data. Table 2 is the sizes for assembly line 1. Table 3 is assembly line 3 data. Table 4 is the sizes for assembly line 2.
The table data comes from another program - they are reimported regularly and so I cannot change, add or delete fields from these 4 tables.
I have to change tables 2 and 4 to crosstab queries and then create 2 queries that join 1+2, 3+4. That's easy enough, no problem.
But then I have to UNION queries 1+2. While 95% of the fields are the same, the most crucial are not - the sizes. Assembly line 1 has 5 less sizes than assembly line 2. I need ALL of the sizes to show in the combined query.
I need to add these 5 fields without adding them to the tables. I did manage to create a crosstab query that included the missing sizes, but now I have a 1 in those fields where I need a NULL or Zero value.
Does that make sense? Can anybody help me?
Thanks in advance.
Rhet
I am really new to Access 2010 as I normally use SQL so im sure this is a very basic question. I have a form where I have linked some sql tables to a table in access so the data gets stored in SQL. The form consists of many fields the basic fields are Policy ID, Name, Office, Month, Dept etc. At the moment users have to fill this in using comboboxes and text boxes but I want to change this so when the user puts in the Policy ID all the other fields are populated ie in the office field it would have a sql query of or something that looksup the office from the policy id that is entered:
SELECT [Office] FROM DBO.DQ
WHERE [Policy_ID]='what is entered into the Policy ID field' and so on.
Also not sure if this works but as I have now linked the tables to SQL does this mean that when a user fills in the information into the form it will then update the SQL table or is this another issue?
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 RelatedIs it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).
I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.
The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...
So, what would be the best auto-fill options in Access 2007?
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I have a table in my Access 2010 Database with few date/time fields. I have set different formats like Short Time, Medium Time etc. But when I link this table to Sharepoint list, all the date/time fields have been modified to their default format (General Date).
Is there a way we can keep the formats as such even after linked to Sharepoint list? If not, is there a workaround?
I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.
ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2.
[ABCTABLE]
ABCORDER
ABCPART
[code]...
Basically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?