User Table That I Can't Normalize. Trying To Query 20 Fields!

Jun 15, 2006

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??

Any replies much appreciated!

View Replies


ADVERTISEMENT

De-Normalize Table

Apr 26, 2007

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

Tbl2
PKEY Path PhotoName
123 M:Photo 123(a).jpg
123 M:Photo 123(b).jpg
123 M:Photo 123(c).jpg
124 M:Photo 124(a).jpg
124 M:Photo 124(b).jpg
124 M:Photo 124(c).jpg
124 M:Photo 124(d).jpg

The desired end result would look like

PKEY De1 De2 De3 Photo1 Photo2
123 North 15 Blue M:Photo123(a).jpg M:123(b).jpg

...and so on until all photos for the record have been listed out.

Any suggestions? If a search topic similar to my issue could be recommended I would appreciate it greatly.

View 1 Replies View Related

Tables :: Normalize Existing Table Containing Data And Copying PK Value To FK In New Table

Nov 21, 2014

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.

View 6 Replies View Related

Queries :: Unpivot / Normalize Table

Nov 15, 2013

Just wondering if I'm missing an SQL trick for normalizing this data I inherited from an Excel spreadsheet.

Current data:

Code:
Case | Insp1 | Insp2 | Insp3 | Insp4 | Insp5 | Rnsp1 | Rnsp2 | Rnsp3 | Rnsp4 | Rnsp5
---------------------------------------------------------------------------------
1234 WEEDS TRASH CAR <null> <null> WEEDS CAR <null> <null> <null>
1235 TRASH <null> <null> <null> <null> TRASH <null> <null> <null> <null>

Normalized table:

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?

View 2 Replies View Related

To Normalize Or Not To Normalize?.. That Is The Question

Oct 25, 2007

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.

View 3 Replies View Related

Normalize My Table - Track Price Levels

Aug 22, 2005

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.

thanks,

sam

View 7 Replies View Related

Queries :: Designing A Query To Normalize Existing Database

Dec 6, 2013

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.

View 4 Replies View Related

Forms :: Updating All Of User Controlled Fields In Customer Table

Sep 23, 2013

I need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.

The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.

View 1 Replies View Related

Queries :: User Selected Fields Added To Query Through Parameter

Oct 8, 2014

Is it possible to have the user select which field is added to the query through a parameter? In my query I have a table where each record has a part number and has sales number for each month. When the user runs the query I want them to be able to select which month is returned.

My table basically looks like this:

PartNumber,Jan,Feb,Mar,Apr,May etc.
01PartNumber,15,20,30,25,28 etc.
02PartNumber,12,14,12,16,20 etc

I want the user to enter "Mar" when prompted and return the parts numbers with values from "Mar".

Access 2007

View 10 Replies View Related

Modules & VBA :: Writing Code To Open Query After User Selected Fields To Include From ListBox

Jan 14, 2014

Access 2007-10
Listbox created: List62 (I know I need to rename it, but for now)
Multi-select: Extended
Row Source Type: Field List
Row Source: qryFieldList
Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).

Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:

Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If

[code]....

It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.

View 8 Replies View Related

Calc User Totals Table 1 And Enter In User Records In Table 2

Oct 28, 2004

I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".

My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.

Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.

Thanks in advance.

Regards Peter

View 5 Replies View Related

User Inputs Table When Query Runs.

Mar 14, 2008

I have a database with 96 tables.
8 tables per month
one query pulls the desired information.

My question is this, currently the query looks to all the January Tables.

Can I configure one query to request user input(month) and then the query looks to the tables based on the users input(month).

View 4 Replies View Related

Normalize Or Not To Normalize?

Oct 11, 2005

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!

View 6 Replies View Related

Getting User Input For Field And Table On Update Query

Jun 5, 2006

Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?

View 1 Replies View Related

Normalize An Exsiting DB?

Nov 21, 2006

Hello I have a question.
I work for a company that had someone set up an Access database that generates work orders.

That person created a single LARGE table. 107 fields in the table. over 3000 records created so far.

Should I bother to try and normalize it?
Can it be Normalized?
Is it a pain?

Or should I just start over and create a new database. I thought about doing this but don't know if I can copy the old information into the new one.

Thanks for any advice you can offer.

View 3 Replies View Related

Normalize Question

Apr 15, 2005

I have a table -

TableID
PartNum
PartName
InstallTime_Shop
InstallTime_Site
InstallTime_Cadd
InstallTime_Testing


InstallTime Is a set amount of estimated time per part. This Value would be used to estimate time needed to install a part.

Should InstallTime_ be in its own table?

If it is in it own table would it one to one?

View 10 Replies View Related

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?

Thanks.

View 3 Replies View Related

I Need To Normalize These Tables And I Will Pay $30 Bucks On Paypal For The Help!

Nov 6, 2007

If someone can help me with this I will send over $30 bucks for your time on paypal. I'm stuck bigtime and need some help.

Feel free to reply here, or e-mail me spork1138@gmail.com

Consider the sample data on patients, providers and visits given in the table below:

VisitNoVisitDatePatNoPatAgePatCityPatZipProvNoProvSpecialtyDiagnosis
V100201/13/2000P135DENVER80217D1INTERNISTEAR INFECTION
V100201/13/2000P135DENVER80217D2NURSE PRACTIONERINFLUENZA
V930301/20/2000P317ENGLEWOOD80113D2OBGYNPREGNANCY
V821101/18/2000P260BOULDER85932D3CARDIOLOGISTMURMUR
V342101/18/2000P465ENGLEWOOD80113D3CARDIOLOGISTIRREGULAR BEAT

Create a set of normalized tables, upto BCNF.

View 14 Replies View Related

Queries :: Join Multiple Fields From One Table To Same Table In A Query

Nov 21, 2014

I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.

Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).

View 2 Replies View Related

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.

View 1 Replies View Related

General :: Normalize Data From Spreadsheet Prior To Load To Access?

Dec 17, 2013

I have a large spreadsheet I need as the basis for an Access database.

The spreadsheet contains... Company Number, Name, Address, etc...

The Company number is consistant and always the same..

However the Name and address is different... eg LTD v Limited, ABC House v 34 High Street etc....

how I can prepare the data and get into access?

View 3 Replies View Related

Modules & VBA :: Normalize Data In Array / Remove Duplicates And Import

Dec 18, 2013

I have some data in an array that I need to normalize, remove duplicates, and import.

Original Table

Every record in the array has a person, all but a few have an address, most have a phone, and some have an email.

Person----- Address ----- Phone ----- Email
Tom ----- 10 A Ln ----- 789... ----- e@a
Sue ----- 20 B Ln ----- 256... ----- _____
Sam ----- 30 C Ln ----- _____ ----- _____
Dan ----- 40 D Ln ----- 478... ----- _____
Jan ----- 40 D Ln ----- 567... ----- e@d
Stu ----- 50 E Ln ----- _____ ----- _____
Syd ----- ______ ------ 224... ----- _____

New Data Structure

I want to group the data by HouseHold; which Address will serve to define for this import.

tblHouseHold
hhID
tblAddress
adrID, hhID, Address
tblPerson
prsID, hhID, Person
tblPhone
phnID, hhID, Phone
tblEmail
emlID, hhID, Email

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

[Code] .....

View 2 Replies View Related

Query – Group By 2 Like Fields In A Table

Jun 6, 2006

My table looks like this:
Sales(a)||Date(a)||Sales(b)||Date(b)||Sales(c)||Da te(c)
1) 50 05/06 75 06/15 100 08/15
So I want to show all sales with the criteria - Date >05/01 <07/01.
The final query totals will be
Sales||Date
50 05/06
7506/15
So the issue is that there are 38 rows with similar data in each column
I have the table in this format because I also have a form that feeds off this table and it needs to be in this format-but I would also like to run a query/report from this table that summaries sales. I also know that since there is more than 1 date value in the row, this is going to make the query difficult to run (each date is linked to a certain sales total).
I guess what I want to do is transfer the horizontal data to vertical data (with certain criteria)
Please help!!!

View 1 Replies View Related

Building A Query From Table Fields

Dec 2, 2006

Hi,
I am trying to make a query from fields out of 3 tables.
All tables must include following fields:

Table 1 fields:
WR04 (year 2004)
Date
Reporting Person

Table 2 fields:
WR05
Date
Reporting Person

Table 3 fields:
WR06
Date
Reporting Person

I am trying to pull together in the query any given individual (Reporting Person) who may be included in all above tables but believe I may have a relationship problem as I am only getting results that match all tables.
I hope this is clear.
Any suggestions please.

View 4 Replies View Related

Linking Fields Between A Query And Table?

Sep 12, 2014

In query design view, I have added a query with column (nameA) of names and a table with a column (nameB) of names. I am linking these two fields (nameA and nameB) which contain names. The names are spelled exactly the same so I should get results. Both fields are of the same data type.

But even when I add only the field with names from the query (the field called nameA), I don't get any results.

What is wrong here? Are there restrictions about linking a query to a table?

View 3 Replies View Related

Filtering Fields Of Table By Query

Sep 3, 2015

I am a student and I've just started to work with the MS Access and databases.

I am wondering if it is possible to filter the fields of table and display only specific information from that specific field. For example: The database (*.mdb file) is created automatically by the software (EPLAN Electric).

There are columns with fields like "de_DE@Verbindungsleitung;pl_PL@Kabel laczacy;en_EN@Connecting cable;en_US@Connecting cable;" But I would like to display in query/table filed only text starting from "en_EN@*" or "??_??@*" or display nothing...

I know that it is possible to display only rows which contain this text, but how can I get rid of fields from unwanted words...?

I cannot change original table of database because the parent-program (EPLAN Electric) will not recognize this database... And additionally data base is updated via EPLAN so every new field should be filtered in this way...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved