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.
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.
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
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.
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.
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
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.
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.
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
I have an inventory table and a repair table for computer equipment. The inventory table is already set and working with all the data I need.
In the repair table, I would like to just enter the ID number and have some of the information already stored in the inventory table automatically entered into the repair table as needed.
For example, computer 138 has repairs done to it. I would like to then go into the repair table to add a new entry, type in 138 and have the some of the data from inventory table (model, serial number, etc) transfer into the repair table.
Is this possible with Access?
I have only been able to do something with the lookup wizard, but that doesnt seem to work well.
I have a database which i am constantly updating and improving. I have a few friends who use the database in a current form. If i made an update say to a form or to some of the coding to make the DB more efficent, how can i update the dBs my friends are using without damaging or changing the existing data, or migrating it to the new design.
I am trying to open an Access database which was created a few years ago... When I open it to edit one of the forms I fail to see the forms etc that are in there.
Is there a hot key you hold down when opening the database to be able to open Access in full edit mode showing all forms queries etc
I was hoping you guys would have a recommendation. Do you know of a book or web site that offers workbook examples to help practice learning how to understand existing databases?
My biggest problem with databases has never been designing them but learning how to follow someone else's work. Some place to practice on this deficiency of mine would be great. Any thoughts?
I have been assigned to "improve" an Access database that has been created by a user. However, i do not know where to start. One of the things I have to do is write a stored procedure so that when the user runs the queries, the user is prompted to enter the criteria (for instance, a data range) to narrow down the number of records retrieved from the SQL backend. I have been told to improve the database. However, I do not have any experience in this and don't know how to start. The database has been created by a user so there's obviously lots of room for improvement. For instance, there are a million queries and I don't think that many are really needed. The goal is to minimize the size of the database. Can someone please guide me as to how I should go about starting to improve this database?
I would like to know how can I insert a new row between an existing row in an Access Table
For example,
I want to insert a new row between line number 122 and 123. Do I need to get rid of the primary key or change it to Number. What I am doing now in order to have several topics group together on a report such as "Activities Topic" and a Date field for that topic. However, there are various topics under the Activities topic. Each new information that is being updated for this topic I have to reorder the number by the topic name in the Order field so that the date will be in chronoligal order in the report.
Can i save an SQL statement to an existing Query. what i would like to do is have a crosstab on fields that will be determined at runtime. so i would have the pivot on a field that is chosen at runtime. I can run a sub that will generate the correct SQL, i am just lost on how to save it to an existing query.
the full blown explanation of what i am try to do, is the following. i have a report which is going to use a cross tab. so i need to attach a query to the report. if i assign report.recordsource = SQL i get a message that a crosstab can't be attached to the report. but if i use a saved crosstab and i write report.recordsource = me.myCrosstabQry then it works. don't know why, but it does.
I currently have a query (see code below) showing me a total count of WorkUnits. I would like to exclude WorkUnits if the PossibleCause field is Out of Stock. When I add criteria to Where in the code I keep getting an invalid bracketing issue and I cannot solve it no matter what I have tried. Any advice on how to add the critieria above to this query correctly?
SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals] FROM [Select Distinct [WorkUnit] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;
I have a table which contains the following fields: WORKER,SUB SYS,JAN-08,FEB-08,MAR-08 I want to insert into this table each month the data from the second table for that month can someone tell me how to do this...each time I try the secnd table data TOTAL strarts a new row below the ones that are already there...hard to explain. I tried this and it didn't work:
INSERT INTO [ORACLE SORA ALERTS BY WORKER] ( [JAN-08] ) SELECT A.TOTAL FROM A INNER JOIN [ORACLE SORA ALERTS BY WORKER] ON (A.SYS = [ORACLE SORA ALERTS BY WORKER].[SUB SYS]) AND (A.WORKER = [ORACLE SORA ALERTS BY WORKER].WORKER);
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 0000A ENF 0000A FIN 0000A RAP 000AT CRT 000AT ENF 000AT FIN 000AT RAP 000AX CRT 000AX ENF 000AX FIN 000AX RAP I have a second table which contains the following fields: WORKER,SYS,TOTAL
WORKER SYS TOTAL 0000A CRT 1 0000A FIN 21 000AX CRT 1 000AX ENF 2 000AX FIN 11
What I want is to move the numbers in the TOTAL COLUMN INTO THE MONTH/YEAR COLUMS IN THE FIRST TABLE. Would look something like this:
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 1 0000A ENF 0000A FIN 21 0000A RAP 000AX CRT 1 000AX ENF 2 000AX FIN 11 000AX RAP
When I run this query I want the data inserted into an existing table in the correct place. It is inserting the data in the correct column but not the correct rows. Please see attached screenshot. Can you please show me what I'm doing wrong.
INSERT INTO UNDISTRIBUTED ( Jun2003 ) SELECT Sum(CCur(a.AMT_TO_DISTRIBUTE)) AS AMOUNT FROM NOLDBA_RECEIPT AS a, NOLDBA_INT_CASE_STATUS AS b WHERE (((Trim([a].[id_case])) Is Not Null) AND ((a.ID_CASE)=[b].[case_id]) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False)) OR (((Trim([a].[id_case])) Is Null) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False) AND ((b.CASE_ID)=(select min (y.case_id) from NOLDBA_INT_CASE_STATUS Y, NOLDBA_INT_CASE_MEMBER Z WHERE A.ID_PAYOR=Z.MEMBER_ID AND Z.RELATION_CODE IN ('A','P') AND Z.CASE_ID=Y.CASE_ID))) GROUP BY b.IV_D_DO_CODE ORDER BY b.IV_D_DO_CODE;
Hi am a forensics student taking a course in Database management and we use microsoft access 2003. I opened an assignment already started in 03 format, I am now running access 2007 and I need to make modifications to a previous query but I can't find the query table. how do I view my pre made queries on the 2007 version?
I have tblClasses, tblEmployees, and tblRegistrations. I have a registration form with EmpName and subformClasses, which allows me to register one employee for several classes at a time.
My registration form has an OK button. I need a message to appear (on btnOK click) before adding a new record to the registrations table that indicates if the employee has already had training for the one or more of the classes selected in the subform. Then the option to "Yes" add the record anyway, or "No" cancel the record.
I have everything working except, I can’t figure out how to do the comparison with the subform and generate my "Yes/No" option. I’m hoping that one of you wonderful access geniuses will have a relatively simple suggestion for me.
Hello everybody, Can anyone out there please help me? I’ve been wandering around the various forums trying to gain some advice concerning password protecting a form. I have tried in vain to tackle this one on my own but alas I cannot, and hence I am turning to some kind person out there to try to help me out of my problem.
Having searched the various forums extensively, and have seen numerous examples all of which work fine,however in a different context; I cannot for the life of me understand where I could incorporate such code along with the existing code that I already use, with great success to open a linked form. All I want to achieve is for a user to click on a command button and for it to prompt for a password and then to open up my form which is synchronised to the original form that contains the command button.
What I have so far is:
Private Sub Command146_Click() On Error GoTo Err_Command146_Click
Dim stDocName As String Dim stLinkCriteria As String
I read the following thread from Ghudson showing a good example of password protecting a form but how do I use such code in the example that I have above.
I have a TSQL procedure written which validates if a record already exists. It returns a 1 or a 0 depending on if it finds a valid record based off a supplied paramater (an application ID in this case, stored as Text(14)).
I want to accomplish the same thing but in a standalone Access DB where I don't have access to SQL stored procedures. Basically, here's what I have:
frmInputNew contains a text box txACAPS. When a user enters in a number in the txtACAPS field, and tabs out, I want an event to run which checks that string against a table. If the string exists within a field in the table, I want to break out and open a new form (which will be a dialog box). If it doesn't exist in the table, I want to end the code and let the user continue entering in data.
I'm not entirely sure how to check a table for a value via VB and return a flag that I can form an IF/ELSE statement from. Any help would be appreciated.
