Queries :: How To Normalize Some Graphical Data Along X Axis
Jun 20, 2013
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.
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?
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 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.
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.
In a graphical display, I would like know how to show the relationship between 4 new tables in an access 2007 database. I know that I setup this relationship since in the past but I can not get the 'arrows' to show up in the graph. i looked at the following link, but this does not show the graphically relationship between the table:
[URL] ....
I also clicked on each table to look their properties, and nothing is marked as 'hidden'
I created a list that grades students and I would like to be able to print out a report on a particular student that shows those grades in more of a graphical representation.
I originally used word to do this and all I did was create a heading that listed the marks along the top and the categories in the first column, for example:
Code: Outstanding - Very Good - Good - Satisfactory - NS - Poor Writing x Reading x Attitude x
etc..
I used this report function because it was easy for the parents to see. However, as you can imagine, using word was really not the right tool and I went with Access 2007 (it's what I have).
I'm really happy that I went to a database format, but I'm having problems showing the grades in a more graphical way. Right now, it just reports the grades but the parents preferred the other format.
Is there anyway to do this in Access? I'm sure some smart cookie out there knows how I can do this. I tried using the graph function, but I quickly got lost with that and it didn't seem to work the way I wanted.
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
Just encountered a new issue in my Access database whereby reports and forms often appear transparent and graphically glitch all over the place. Hard to describe but the following screenshot will demonstrate:
[URL]
If I zoom into a report then it renders properly and will print OK. If I refresh a form then that seems to fix it temporarily.
This has only started happening since we upgraded our server last week.
Background.
Access front end. MySQL back end. Users have a copy of the front end on all of their PCs. This issue is affecting everyone.
The database backend isn't stored on the server we recently replaced.
I have a problem when i create a pivot table . X axis and Y axis is show the number of ID (Primary key value) instead of owner name. How can i change this ? I high light with red rectangle . I want name there , not number ( this number come from table primary key).
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??
Hi all, I want to create a pivotchart from a query, embed it in a form and use a combobox in the main form to allow the user to select the x axis value. So in a query with fields city, state, country, etc the user would be able to chose one of those fields as the category(x axis across the bottom) to display data. Is this even possible? If it is, how do I do it? As it is now I can add multiple categories to my chart- but it just makes a big mess of the chart. I can embed a chart in a form and use a combobox- but it allows me to choose from within a field- but not from among fields. Also- how do I access the code to manipulate the chart? Thanx in advance
i have two charts in ms access and i want to combine these two charts in one chart. do you know how i can have two y-axis in ms access charts with two fields for data area.
I've cannibalized a chart from another database, hooked it up to my new data source and solved most of my formatting problems except for one, the Y axis of the chart won't display the scale properly. The minimum is set at - 3 and maximum is set at +3, but when the report opens the scale reads from -0.003 to +0.003. I've tried all the number formats I can think of. Currently it is set to General. If I set it to a number with anything less than 3 decimal places it formats as 0 or 0.00.
The Total Ongoing Calls The Ongoing Calls in Area 1 The Ongoing Calls in Area 2
Ideally the Total Ongoing Calls should equal the other two but this is never the case due to calls in wrongs areas etc. To display this I have the Total Ongoing Calls shown as an Area Chart on the Primary Axis with the other two figures shown as a Stacked Area Chart on the Secondary Axis.
This way if I manually fix the Primary and Secondary Axis Scale to the same then I can see at a glance the total in both areas and the discrepancy from the Total. Great.
The issue is that the Primary Axis will almost always be slightly higher than the Secondary Axis which means that whenever the Scale changes I would have to manually change the other Axis. Is there any way, programaticallly or otherwise, that I can link the two Axis together so that if one changes up or down the other changes with it.
Thanking everyone I am about to confuse in advance.
