querying similar databases plan

I'm working with hideously unnormalized databases, most of which have
similar structures.  (Of course, if they had the same structures, it
would be too easy.)

I work at a company that does cancer research, so they collect
information about their patients over a series of visits.  The
questions they ask (queries) for parts of the final report are
standard across all databases, and then some are specific to a the
type of study they're doing.

What is the best way to approach this, given that I have to summarize
like 20 databases in maybe two weeks, and the column names are not
necessarily even consistent.  Nor is the location of some fields.
(Imagine a database built by someone that does not really understand
normalization, so he does it on _some_ tables and not on others.)

that's basically what I'm faced with.

Oh, MAJOR CAVEAT: I am not allowed to normalize the databases. (that
was my first inclination, and I was told very explicitly NOT TO.

Right now my plan is to do something like this:
1. Create a generic front end for each database type. (there are like
5 different ones).
2. Include all the queries that are "stable"/consistent across all
3. turn that into a template and then
4. customize the template for each of the various study types.
(because they'll basically add columns and standard queries).

Yes, I know this is a lot of work for a screwed database structure,
but I am in no way for no reason allowed to change it.  So I'm getting
good at building utilities that create insane union queries for me,
because I'm querying essentially repeating fields, eg


Yes, I know not to store information in column names, but whoever
built these monstrosities did not know that.  and that's where the
data is, so I'm stuck with it.

is there an easier way to get to the finish line (besides taking the

11/20/2007 11:13:43 PM
access.queries 6343 articles. 1 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 2


Similar Artilces:

Lost Database 06-22-07
Hey, I hope someone can help me. I just closed out my database window within access and now I can not access it. I see it in the folder but when I select it, access opens with no database window so I can not get at my information. HELP! Try holding down the Shift key the entire time that the application is opening. I'm guessing that you unchecked the option to Display Database Window, under Tools > Startup. Alternatively, if you have not also disabled Special Keys (bottom of same screen), you should be able to press the F11 button to display the database window. Tom Wicker...

Arabic ODBC query
I am trying to read and write arabic data into Access2000 using MFC ODBC (CRecordset) , but I can only see '?' marks. after doing a research on the news groups , I understand that a tweak needs to be made on RFX_Text can anyone guide me what needs to be done. Appreciate a response. thnx P.Ekkoratha ...

Field name into two lines in a query?
This may be a silly question but... Is it possible to make the name of a field into two lines instead of one long line in a query? No, there is no way to do that. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "mayj" <mayj@discussions.microsoft.com> wrote in message news:9372FB14-BDDE-49E3-8A0E-7E5F3B598FAC@microsoft.com... > This may be a silly question but... > > Is it possible to make the name of a field into two lines instead of one > long line in a query? O...

database problem #3
I am using this code to store data in msaccess database but when i try to open the table it is not tthere ,and while opening the mdb file it will always ask for conversion if i click the convert database then also no table is present. 1)What should i chage in this code to open in msAccess2003 Can anybody help me to solve my problem? 2)For storing i have not done any odbc/oledb (DSN)connection is that needed.If yes how to do the connection? 3)Is this code is right? #ifdef _DEBUG afxDump<<"before opening tha datbase"<<"\n"; #endif db.Create("C:\\ODLDATA.md...

Multiple Item query
I've read a few posts on this query and it looks like creating a table where I input the item numbers in it and linking it to the query seems to be a good way to get a lot of items in a query. Now what If I have hundreds of numbers I want to look up? do I input all 100 numbers into the table? Or what if it's like 10 numbers but I want create seperate queries for different groups of numbers, should I have 1 table for one group of numbers? What would be the easiest way for a novice to do this? It is easier to fill the table with just the values you need for the query you want to run, t...

Conditional formatting query #2
Hi, I thought I knew how to do this, but it turns out I don't. If I have names in column A (eg Alan, Bob, Cathy), and values in column B (see below), is there any way I can use conditional formatting to change a cell in A, based on a value in B? A B 1 Alan 4 2 Bob 1 3 Cathy 3 Essentially what I'd like is to highlight the name, if the value is 3 or over. I know how to change the cells in B using conditional formatting, but can't figure out how to change A cells. Thanks, Fiona Select all the cells you want Conditionally formatted. Try this with Formul...

display ratios as written in web query
I am using a web query to display data about screen contrasts, all written with the formatting of "700:1" or "3000:1" It displays corectly in the edit web query window, but everything I try to make it display as delivered fails. I tried text, custom formats with @ etc, but it seems to insist on doing the calculation before turning it back to text, or as a ratio it does the division. (The text formatting for this works OK if I type directly into the spread sheet) Is there a way to make a web query display as written? Any help appreciated. Keith ...

how to set up a query to show only user based selections
I have created a table with data types yes/no. I am trying to create a query that ONLY shows when the user has selected yes. I am trying to create a report that only shows the options selected versus showing all of the options. The users select this from a form that has option buttons. Any help will be greatlt appreciated! Submitted via EggHeadCafe - Software Developer Portal of Choice WMI - VB 6.0 Windows Management Instrumentation http://www.eggheadcafe.com/tutorials/aspnet/5fb2b0ca-919a-406b-8566-a9d07f76c868/wmi--vb-60-windows-mana.aspx Yes is stored as a -1 (minus one) so ...

Append Query Not Working 05-03-10
My Append query is not working and i am not sure why... Here is my SQL INSERT INTO tblContractPOTracking ( TrackingID ) SELECT tblDocTracking.TrackingID FROM tblDocTracking WHERE (((tblDocTracking.DocumentNumber)="5" Or (tblDocTracking.DocumentNumber)="6" Or (tblDocTracking.DocumentNumber)="7")); Please help! "not working" is a bit vague ... If you want more specific suggestions, please provide more specific description... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and s...

Display a count of unique records in a query in Access 2007
Hi, I'm not sure if I should be in this group, or Reports, but I'll start here. I have a user who would like to do a count of unique records and display the information in a query, or a report. Here are the basics: She has a list of donors, some of the donors donate more than once to the same cause. What she would like is a list of the number of donors, per cause. But, if the donor ID repeats for the same cause, she does not want that ID counted again. Example: We can get a query to return a count of the records per cause. It is returning a value of "7" for a cause,...

Identifying Modified Database Fields
Situation is as follows: - We have an SQL Server database table that is being updated in a synchronisation process from another source database, via code that loops through the source records, finds the corresponding records in the destination database, and updates relevent fields (via RDO, SELECT corresponding record, Edit, change all fields, Update.) This is existing code. There is a field in the destination database (ModifiedDate) that should only be updated if any of the destination fields have changed as a result of the synchronisation. The question relates to how th...

Visual basic for application project in database is corrupt
I'm running Office 2003 on Windows XP. I have a database that does not open, and gives me a message stating that Microsoft Access has detected corruption in this file. The message goes on to suggest that I run Compact and Repair Database from the Tools menu. When I do, I get the error message above. Is there any way of recovering this database? Thank you for your response. I actually tried both ways, linking and inporting, but I'm unable to gain access to the corrupted database. The only thing I have been able to do is access it through MSQuery in Excel, but that only gets me to ...

Query Date Help
I need to create a query that will do the following: these are my fields: resign date, hire date I need to subtract the resign date from hire date but I want it to return the answer as years, months & days ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days is there a way to use the DateDiff function to return years, monts & days? Look at this link on "A More Complete DateDiff Function" http://www.accessmvp.com/djsteele/Diff2Dates.html -- Good Luck BS"D "aldunford" wrote: > I need to create a query that will do the following: > > th...

Run query from date input on form
Hello all! I have what I hope is a easy situation. I have a table that has information that is entered daily with a field "Completed Date". I want to add a text box to a form where a user can enter a date and hit a button that will run a query for that specific completion date so I can have the query results exported to Excel. Would I use an unbound text box on the form? How would I set up the query to take the date entered in the form? Thank you very much! - Joker -- Message posted via http://www.accessmonster.com Yes, an unbound textbox would be one w...

How to change the AutoNumbers inID field in my finished database?
This may be a simple question but... I have already entered in my data and want to change the autonumbers, to a different way I have set it up. Is this possible? On Mon, 4 Feb 2008 19:48:01 -0800, Jako <Jako@discussions.microsoft.com> wrote: Define "different". -Tom. >This may be a simple question but... >I have already entered in my data and want to change the autonumbers, to a >different way I have set it up. Is this possible? Jako <Jako@discussions.microsoft.com> wrote: >I have already entered in my data and want to change the autonumbers, to a ...

Reassigning tasks to resources not already part of project plan
I am creating projects and assigning the majority of the tasks to team leaders, who are then reassigning to various resources on their teams. Although these resources are members of the team and can assign the tasks to themselves, they don't appear on the drop-down menu for the Reassign Work feature, I"m assuming because I don't have them assigned specifically to a task. Is there a way around this, other than creating a dummy task with ALL the potential resources on it? This is a multi-part message in MIME format. ------=_NextPart_000_00F5_01CAAF4B.B2486F6...

How do I add the similar points together on the X axis
I manage a staff of 35 people and each has to be issued multiple purcheus orders during the courst of the year. In order to keep trak of whom on my staff has which po I have inputted each po number in to an Excel sperad sheet. As my staff checks them out I put their names next to the number along with the amount they have been issued. Right now I am trying to make a bar graph to see how much each of my staff members has checked out, but I only want to end up with 35 bars (to see each persons total) not 286 bars (the numbers of pos issued so far). So my question is: Is there any way ...

Records are not deleted in the database
Hi everybody, today we noticed when we are deleting records in the web client, they stay in the database with the value 2 in the DeletionStateCode Column. I guess normally this is the markup for some sort of job or service to clean these records, but apparently this never happens. We have deployed Rollup Package 2 which should include a hotfix for this problem, but no luck. Can anyone point us in the right direction or is this a (huge) bug? Thanks Christian On Feb 3, 5:51=A0pm, CGuntsche <CGunts...@discussions.microsoft.com> wrote: > Hi everybody, > > today we noticed whe...

question on tables and queries
I've imported 2 tables from excel and ran a query against those two tables to determined the difference. This is something I would like to do each month. I would like to keep the same settings in my database, is there a way I can easily import/update the data in the new table without creating a new one each month? The query would stay the same as I'm comparing the same two files, but the the data will be changing every month. I don't know much about access and would like to know if there is an easier way of doing this besides creating a new table and query each month? I&...

It it possible to create a database in Access for this:
Hi, Im trying to create a database, but not having much luck. Im new to MS Access. I would like to use a switchboard that people open - it will be used by at least 6 people - so when they open it they have 3 or 4 options to choose from - nice a simple - not confusing for anyone on where to go or what to do. On that i was going to add a form to fill in details on clients. Then from the information that is added in that goes to a table. Such as Name, Number, DOB, location will be filled out for each client. Under that - 10 drop down menus to click on different jobs. Each job goes into a sep...

Slide database
Is it possible to create a database of Powerpoint slides? I give talks on local history and have many slides that may be used in several talks. I would like to keep a record of what slides I have shown to which group, notes on the slide, details of the source of any picture or information on the slide, etc. Naturally I would also like to keep the slide in the database complete with any animation effects. When I put together a talk I would anticipate taking slides from the database to form a presentation of an appropriate length I did try simply putting all the slides into o...

Linq Query and Lambda
Hello, I am getting a list of regions as follows: return _context.Regions.Select(r => new RegionModel { Id = r.Id, Name = r.Name }).OrderBy(r => r.Name).AsQueryable(); However I need to get only the Regions which are related with Centers. So I need to check _context.Centers and get all used Regions Ids from each Center.Region.Id and then get all the Regions with those Ids ... Is this possible to do with a lambda expression ... I think it is possible but I am a little bit confused on this. Thank You, Miguel shapper wrote: > Hello...

Query in Excell
I would like to create bolean statements in one or more of the columns that result from a query. Basically, I want to test a column for a factor and return a simple 'true' or 'false'. Using this I, hopefully, will eliminate the need to pass the data to Excel and perform the function there. ...

3 queries into 1
Hello, I am going to try this again starting fresh from my other posts. I am trying to develop an all inclusive query on our inventory. Each fical term we start with new tables. I would like to take our Ending Inventory from our prior fiscal term and combine it with our current inventory balances. In years past I always took our ending inventory and posted them in our purchase order table to have our beginning balances. I thought it would be less time consuming to have a query to combine the 2. Below is a list of data and the query designs. PART # PRODUCT DESCRIPTION END B...

Lookup query value from Form
Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...