Can't Normalize Data And Trying To Do Workarounds.
Sep 1, 2006
This has been a nightmare all around. Because I can't get 40 licenses for Access I'm stuck doing transferspreadsheets every day, which has worked out mostly, but I'm reaching the absolute limits of my knowledge. I can't even think straight and need help with a solution.
Here is the table data I'm bringing in (in a simple way)
Editor Client ResearchHits HitsSent AltEditor Pruned Edited
J. Doe Coca Cola 1000 500
J. Doe Coca Cola J. Schmoe 200 100
This data is brought in from two imports of data (there is actually a date column too that I didn't include). Now, what I need to do is have it subtract the "Pruned" from the "ResearchHis" and the "Edited" from the "HitsSent" while only showing Coca Cola and J. Doe once... is this possible?
I'm trying to build a query that will normalize some graphical data along the x-axis. To do this I need all the peaks to be at the same point. I'll just talk in generic terms of FieldX and FieldY for the X and Y variables (resp)
In my mind the steps require me to 1) Determine FieldX at Max(FieldY) 2) Determine difference between FieldX and arbitrary normalization point (probably 100k) 3) Shift FieldX at all points to put Max(FieldY) over 100k (its a log scale so I'll be dividing)
Steps 2 and 3 are easy, but step 1 is giving me grief.
Ok, so I know how to find Max(FieldY), easy enough. Now how do I find FieldX? My first thought is a complex series of subqueries. At some point in the past, where I was smarter than I am today, I created a query that would do numerical integration. Looking back at the query I can't understand it, but I think the principle will be similar.
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset Dim rsHH As DAO.Recordset Dim rsPhone As DAO.Recordset Dim rsEmail As DAO.Recordset Dim rsAddress As DAO.Recordset Dim rsPerson As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb
I have been tasked with producing a Service Catalogue - essentially, a list of services offered by IT to our business customers.
The Catalogue is a repository of information related to the service - such as, Service Info, Business Info, Server names and location, DB info, Batch and Online times, Network Info (and the list goes on).
Obviously, these are high-level categories. Within each of these (for example. Business Info) there is more granular info such as, Business Owner, Supported Business Area (there can be more than one) and Business Criticality.
Given the data and the fact that some category items may have one or many values for any given field (DB names for example), is this something worthy of a DB?
I have tossed this around in my head and I can't get away from the notion that I will require multiple tables with untold fields (yikes - scary thought!)
I have attached a rough DB but before I put any more time and effort into it, I want to know if I am on the right track.
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
I have a performance evaluation db in MSA 2003 I am about done with. I had asked a question and got a helpful response. However, the response mentioned normalizing so I hit up google for more info. My db is not normalized, but after reading several sites, I don't see why I need to normalize it. The sites also said there are not a lot od reason not to normalize. I can't believe I just stumbled onto one. I have fields in my table that need to be scored on a 1-5 scale. I can see making a separate table for scores. However, the list of questions never grows or shrinks. It's a standard format that asks the same 20 or so question every time. Is there a reason to split the table into multiple tables? If so, would it be the scores vs. employee info tables? TIA for your assistance!
I have data that I would like to use within another system. In order for this information to work as desired I will have to "de-normalize" my tables into a single record set.
Here is the issue, I have 40k+ records that have the majority of the data I need in a single table. I have directory path links to the photos related back to the primary key.
I need to merge these into a single line of information.
Example:(this is simply an example and not the true layout)
Tbl1 PKEY De1 De2 De3 123 North 15 Blue 124 North 28 Green
Code: Case | Violation | InspectionType ----------------------------------- 1234 WEEDS Initial 1234 TRASH Initial 1234 CAR Initial 1234 WEEDS Reinspect 1234 CAR Reinspect 1235 TRASH Initial 1235 TRASH Reinspect
Right now I'm having to do it with ten different queries (each with its own query behind it to massage the original data), one for each Excel field, and checking for blank entries before appending. And there's going to be about 50 Excel tabs to gather up... anything I'm missing besides the basic slog?
i would like to have a table where i can track price changes, however i don't want this to based on an inventory order.
i have a table which has the amount of hours to be charged. i then have another table which has the price per hour charged. the price keeps on changing. both tables have the date. tableHours has the date of occurance and TablePrice has the date when the price was last updated.
i need to know how to structure the TablePrice. currently it is set as BillableHourType, Rate, EffectiveDate. If this is correct, i don't know how to run a query.
i need to multiply Hour*Price, criteria: Effective Date must be most recent date as of Date of Occurance.
All Help will be appreciated.
I posted this post on the Microsoft site and i got no good responses, i am new to this site, but if it is a problem of reposting, please let me know. i will not do it again.
I am using a table a user created which is like: Member ID (key field) Visit Date Dept 1 Expense Code1 (combo box E through I) Dept 2 Expense Code2 This goes on through Department 20.
Now they want to know how many E's for one month. I am stumped on how to normalize this or if it is even possible! I thought maybe there is a way to search the table as if it were a spreadsheet doing a "countif" function??
I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".
A visual of my tables:
tblLabels (Main table) - LabelID - BaseProduct (old field with text data) - LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)
tblBaseProduct (new table) - BaseProductID (PK and FK to tblLabels) - BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)
I tried to design a query using design view of the query design and this is what I have:
Code:
UPDATE tblLabels, tblBaseProduct SET tblLabels.LabelBaseProduct = [baseProductID] WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));
When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.
If my make queries in the data base and the source data base is another .mdb and the table names in the other .mdb which would be used for the queries are the same as those in the data base where the queries would be made......does anyone see any problems with that in the area of corruption or similar.
The queries made would be indentical to their counterparts in the data base where they are made and would serve the same purpose.
It would be a toggle type of thing whereby the recordsources for the forms in question would be changed.
For what I want to do it works perfectly but I am not sure if there would be problems that would only surface with longer term use and varied conditions as opposed to some short term testing.
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1 ID // Response // Cats // Dogs // Elephants //.... Column 10 1 // I like cats // I like cats //(null)//(null)// ... (null)// 2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..// 3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data 2. Section 2: Show all responses from the Dogs bucket where there is data 3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
i would like in a form for a combo box to be able to select an item from a table and input relating information automatically into other boxes in the form..
I have 3 tables: Table 1 has product code and product description. Table 2 has invoice number company details, address etc. Table 3 has product code and product description qty and invoice number.. Table 3 relates to table 2 by the invoice number and table 3 product code looks up the product codes available in table 1 and also table 3 looks up the list of products descriptions in table 1 using the combo wizard. This means the wrong code can be put with wrong description. What i would like to know is how i select a product description and the product code in the form fills out automatically?? i hope this makes sense please helppppp!!
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected Total Envelopes=sum(Envelopes) for date selected Total Documents=sum(Cases) for date selected Total Pages=sum(Pages) for date selected
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
