Bevos
asked on
Proper syntax for union query in Access that joins 9 tables
Hello, I have want to display the results of all 9 tables as a single query, but there are too many fields for access to do this if I just do a single select query. I've never used a union query before so I thought I would try this and used the following code (all tables are related in a 1:1 relationship on [CallNumber]):
Please help me figure out the correct syntax for this query.
Thank you,
Bevo
SELECT *
FROM tblStudyCharacteristics
UNION ALL
SELECT *
FROM tblTreatmentCharacteristics
UNION ALL
SELECT *
From tblOutcomeAssessment
UNION ALL
SELECT *
FROM tblIntermediateOutcome
UNION ALL
SELECT *
FROM tblHealthOutcomeMorbidity
UNION ALL
SELECT *
FROM tblHealthOutcomeMortality
UNION ALL
SELECT *
FROM tblHealthOutcomeFunctionQOL
UNION ALL
Select *
FROM tblAdverseEvents
UNION ALL
Select *
From tblQuality;Please help me figure out the correct syntax for this query.
Thank you,
Bevo
What you have s/b working ... is it not ?
mx
mx
And as an aside, once again your running into issues because of a bad database design.
JimD.
ASKER
JDettman, dang all these databases seem to be designed pretty bad despite my reading Access for Dummies.
So, I suppose union isn't the type of query I'm looking for. Is there any way to get around the limit to the number of fields in a select query?
Bevo
So, I suppose union isn't the type of query I'm looking for. Is there any way to get around the limit to the number of fields in a select query?
Bevo
Bevo,
You cannot do this, nor do you want to do this
You have no conceivable need to trying to pull 1000+ fields into a single query.
You will not base a report off such a query.
You have subforms
You will have subreports.
WHY are you looking to create this query?
You cannot do this, nor do you want to do this
You have no conceivable need to trying to pull 1000+ fields into a single query.
You will not base a report off such a query.
You have subforms
You will have subreports.
WHY are you looking to create this query?
How many fields are you talking about and what are you doing with all those fields.
And why do you have 1:1 Relationships ?
And why do you have 1:1 Relationships ?
<<So, I suppose union isn't the type of query I'm looking for. Is there any way to get around the limit to the number of fields in a select query?>>
No, 255 fields is it. You'll need to use multiple queries to get the data.
JimD.
No, 255 fields is it. You'll need to use multiple queries to get the data.
JimD.
ASKER
Thanks for the comments everyone. I have 1:1 relationships because the contents of each table describe a specific academic literature article. These articles have many points of data and I have separated those points into 9 domains (tables) each related by the Call Number used by our librarian.
It works out okay, probably not the best database design, but these projects happen frequently and do not last a long time so maybe that is okay.
I wanted all fields in one query so I could use a VB script for exporting selected fields to word/VBA after filtering according to some combo boxes. I could write all the queries people wanted but I thought it'd be easier to just let them select the fields they're interested in. Oh well, maybe not. Maybe I'll just make a separate one for each of the 9 tables.
Bevo
It works out okay, probably not the best database design, but these projects happen frequently and do not last a long time so maybe that is okay.
I wanted all fields in one query so I could use a VB script for exporting selected fields to word/VBA after filtering according to some combo boxes. I could write all the queries people wanted but I thought it'd be easier to just let them select the fields they're interested in. Oh well, maybe not. Maybe I'll just make a separate one for each of the 9 tables.
Bevo
@mx,
He's attempting to build an entry app for medical research papers, to be able to do data mining afterward.
His users are medical professionals
Attached is a sample from an earlier question.
There is a TON of data to be entered, so this IS a case where 1-1 relationships may make some sense.
A table with 400+ fields would be unwieldy.
@Bevos
If you REALLY are seeking to be able to ask questions like "how many white men treated with drug B lived 5+ years after treatment" your design is wrong.
You are well past "Access for Dummies" my friend.
Given what I have seen of medical research papers, you are into territory that professional TEAMS of developers might hesitate to tackle.
Papers are designed to aggregate data and present conclusions.
If what I think your end use requirements are is correct, you are seeking to do the exact opposite: disaggregate conclusions back to the base data.
That is IMMENSELY difficult, if the desired result is to be able to focus on the study participants outcomes.
Your present data scheme is focused on the papers, not the participants.
We have, in the past, told you to go back to square one.
It may be time to go back to square zero.
In some detail, describe what the end purpose of your app is to be
Nick
TabForms1.mdb
He's attempting to build an entry app for medical research papers, to be able to do data mining afterward.
His users are medical professionals
Attached is a sample from an earlier question.
There is a TON of data to be entered, so this IS a case where 1-1 relationships may make some sense.
A table with 400+ fields would be unwieldy.
@Bevos
If you REALLY are seeking to be able to ask questions like "how many white men treated with drug B lived 5+ years after treatment" your design is wrong.
You are well past "Access for Dummies" my friend.
Given what I have seen of medical research papers, you are into territory that professional TEAMS of developers might hesitate to tackle.
Papers are designed to aggregate data and present conclusions.
If what I think your end use requirements are is correct, you are seeking to do the exact opposite: disaggregate conclusions back to the base data.
That is IMMENSELY difficult, if the desired result is to be able to focus on the study participants outcomes.
Your present data scheme is focused on the papers, not the participants.
We have, in the past, told you to go back to square one.
It may be time to go back to square zero.
In some detail, describe what the end purpose of your app is to be
Nick
TabForms1.mdb
If what you're are looking for is to create a filter, then some of your earlier questions make sense
ie Putting the field captions in a listbox (that was you, wasn't it)
You could create a filter form
9 multi-select listboxes, with captions from each field, and a button to start a cascade of events
<exporting selected fields to word/VBA > Ummm no.
Access has reports. That's what they are for --> getting your data out.
Let me know if my idea for a filter form suits.
I can probably craft one
One condition: You will go here
http://access.mvps.org/access/tencommandments.htm
read and abide by the commandments.
ie Putting the field captions in a listbox (that was you, wasn't it)
You could create a filter form
9 multi-select listboxes, with captions from each field, and a button to start a cascade of events
<exporting selected fields to word/VBA > Ummm no.
Access has reports. That's what they are for --> getting your data out.
Let me know if my idea for a filter form suits.
I can probably craft one
One condition: You will go here
http://access.mvps.org/access/tencommandments.htm
read and abide by the commandments.
ASKER
I appreciate everything people on this forum have done for me thus far (immensely!). I have learned a great deal from everyone here but I know that I'm still just scratching the surface of VBA/SQL/Access. I still consider myself worlds better than when I started several months ago.
I used to become discouraged when I saw statements about my bad DB design, but now it just reminds me how much more there is to learn.
Hi Nick, we need to get them into word however (maybe this can be done better by cutting pasting from report?) for writing some documents. I am a doctor by trade and not very well versed in these things. I know biostatistics however and I understand your comment about this being a difficult thing to tackle. I am not directly performing any statistical operations on these values (the unit of measure is the study, not the N number of patients in the studies) because each study population/treatment can't be aggregated with another (not easily anyway, this is another evolving branch of statistics called meta-analysis and is not the prupose of this tool). Instead, we seek to describe the studies that have been done qualitatively, and use the knowledge we have of the underlying science to synthesize the results. The query/report (excel or word) generation is so that we can look at the results from different angles (ie filter on a certain treatment, or setting [i.e. inpatient vs outpatient and look at different outcomes]).
So basically we're looking at all of the studies that exist for a specific topic and then describing them as best we cant (not aggregating into meta-analysis statistics). The doctors who use the form will read the articles and plug in the points of data that are in the forms. After that time we will generate a report of the data entered and then we will write up the findings. I've been trying to make this thing because these doctors are very pressed for time so they deserve to have a tool that makes entry easy. But we are a non-profit with nobody (other than my own limited knowledge) who can create these things. I had only a little experience working with the databases in the health department before this so all of your input has been invaluable.
Please let me know if I can be of further help in describing what we're doing (lord knows I'm not much help with the VBA or SQL yet).
Thank you again everyone,
Bevo
I used to become discouraged when I saw statements about my bad DB design, but now it just reminds me how much more there is to learn.
Hi Nick, we need to get them into word however (maybe this can be done better by cutting pasting from report?) for writing some documents. I am a doctor by trade and not very well versed in these things. I know biostatistics however and I understand your comment about this being a difficult thing to tackle. I am not directly performing any statistical operations on these values (the unit of measure is the study, not the N number of patients in the studies) because each study population/treatment can't be aggregated with another (not easily anyway, this is another evolving branch of statistics called meta-analysis and is not the prupose of this tool). Instead, we seek to describe the studies that have been done qualitatively, and use the knowledge we have of the underlying science to synthesize the results. The query/report (excel or word) generation is so that we can look at the results from different angles (ie filter on a certain treatment, or setting [i.e. inpatient vs outpatient and look at different outcomes]).
So basically we're looking at all of the studies that exist for a specific topic and then describing them as best we cant (not aggregating into meta-analysis statistics). The doctors who use the form will read the articles and plug in the points of data that are in the forms. After that time we will generate a report of the data entered and then we will write up the findings. I've been trying to make this thing because these doctors are very pressed for time so they deserve to have a tool that makes entry easy. But we are a non-profit with nobody (other than my own limited knowledge) who can create these things. I had only a little experience working with the databases in the health department before this so all of your input has been invaluable.
Please let me know if I can be of further help in describing what we're doing (lord knows I'm not much help with the VBA or SQL yet).
Thank you again everyone,
Bevo
Good enough,
If the purpose is really to describe the papers (which papers discuss treatment regimes using drug x that have y number of black men over 40 in the control group), then your design with work.
Now, does the filter form sound like something you want/need?
And have you had a look at the commandments?
Nick
If the purpose is really to describe the papers (which papers discuss treatment regimes using drug x that have y number of black men over 40 in the control group), then your design with work.
Now, does the filter form sound like something you want/need?
And have you had a look at the commandments?
Nick
Ok,
Look at frmFilter.
It's the beginning of the monster filter.
Filters are a bugger
Character data needs to be in quotes
Somefield = chr(34) & frmSomeform.somecontrol & chr(34)
Numeric data must not be in quotes
Somefield = frmSomeform.somecontrol
Date data has to be in hashes
Somefield = "#" & frmSomeform.somecontrol & "#"
This will be quite an undertaking. Start extending it if this is how you want to go
Populate each of the tabs with the three listboxes and apply a consistent naming scheme.
Then we have to work out data types and = and <> and like and like *something* and between something and somethingelse
I'm getting a brain cramp thinking about it 8(
No half measures :)
Let me know
TabForms1.mdb
Look at frmFilter.
It's the beginning of the monster filter.
Filters are a bugger
Character data needs to be in quotes
Somefield = chr(34) & frmSomeform.somecontrol & chr(34)
Numeric data must not be in quotes
Somefield = frmSomeform.somecontrol
Date data has to be in hashes
Somefield = "#" & frmSomeform.somecontrol & "#"
This will be quite an undertaking. Start extending it if this is how you want to go
Populate each of the tabs with the three listboxes and apply a consistent naming scheme.
Then we have to work out data types and = and <> and like and like *something* and between something and somethingelse
I'm getting a brain cramp thinking about it 8(
No half measures :)
Let me know
TabForms1.mdb
Please use field names instead "*"
Bevo,
Your tables are far better then they initially were, but let me ask a question:
In these tables that form a one to one relationship based on the call number, for each call will every field be filled in?
I would suspect strongly the answer to that is no, but I don't understand the terminology of what your doing enough to answer that.
But if not, then the normalization rule you’re breaking is that all non-key columns should depend solely on the primary key (call number).
Here’s a simplistic example of what I’m talking about. Let’s say we have cars, desks, and pens. These are all “objects”, so we decide to do a single table and I start listing out all the attributes I need to keep track of:
tblObjects – One record per object
Make
Model
Year
VIN
Height
Width
Length
SurfaceType
NumberofDrawers
BarrelDesign
InkColor
Now looking down that list, certainly all these objects have height, width, and length. Make, model, and year might apply to all as well.
But does a desk or pen have a VIN number? No. Does a car or desk have an ink color? No.
Those are red flags. We have an attribute “Ink Color” that does not apply to every instance of “object”. In other words, some records *need* this field filled in to be accurate and describe a pen, but when the record represents a car or a desk, the field must be left null because it does not apply to this instance of the entity “object”
The correct design:
tblCars
VIN – Pirmary key
Make
Model
Year
Height
Width
Length
tblDesks
SerialNumber – Primary Key
Height
Width
Length
SurfaceType
NumberofDrawers
tblPens
SerialNumber – Primary Key
Model
BarrelDesign
InkColor
This is why you’re having a problem with the queries; you are using a “flat file” or de-normalized design where you’re simply listing all the fields that could possibly apply.
Anytime you bump into the 255 field limit, it's always a sure sign that your design is not normalized. The reason for that is that it is very hard to come up with something that requires 255 different attributes to describe it. If you do, then almost always you are trying to decribe more then one "thing" or entity.
Unfortunately I don’t understand enough of what you’re trying to do to offer a design at this point, but I think we could come up with something better with a little back and forth.
I would seriously consider starting over as much as that probably will hurt, but in the long run, you'll be far better off.
HTH,
JimD
Your tables are far better then they initially were, but let me ask a question:
In these tables that form a one to one relationship based on the call number, for each call will every field be filled in?
I would suspect strongly the answer to that is no, but I don't understand the terminology of what your doing enough to answer that.
But if not, then the normalization rule you’re breaking is that all non-key columns should depend solely on the primary key (call number).
Here’s a simplistic example of what I’m talking about. Let’s say we have cars, desks, and pens. These are all “objects”, so we decide to do a single table and I start listing out all the attributes I need to keep track of:
tblObjects – One record per object
Make
Model
Year
VIN
Height
Width
Length
SurfaceType
NumberofDrawers
BarrelDesign
InkColor
Now looking down that list, certainly all these objects have height, width, and length. Make, model, and year might apply to all as well.
But does a desk or pen have a VIN number? No. Does a car or desk have an ink color? No.
Those are red flags. We have an attribute “Ink Color” that does not apply to every instance of “object”. In other words, some records *need* this field filled in to be accurate and describe a pen, but when the record represents a car or a desk, the field must be left null because it does not apply to this instance of the entity “object”
The correct design:
tblCars
VIN – Pirmary key
Make
Model
Year
Height
Width
Length
tblDesks
SerialNumber – Primary Key
Height
Width
Length
SurfaceType
NumberofDrawers
tblPens
SerialNumber – Primary Key
Model
BarrelDesign
InkColor
This is why you’re having a problem with the queries; you are using a “flat file” or de-normalized design where you’re simply listing all the fields that could possibly apply.
Anytime you bump into the 255 field limit, it's always a sure sign that your design is not normalized. The reason for that is that it is very hard to come up with something that requires 255 different attributes to describe it. If you do, then almost always you are trying to decribe more then one "thing" or entity.
Unfortunately I don’t understand enough of what you’re trying to do to offer a design at this point, but I think we could come up with something better with a little back and forth.
I would seriously consider starting over as much as that probably will hurt, but in the long run, you'll be far better off.
HTH,
JimD
ASKER
Hi Jim, I really worked hard on a relational design (seriously!) and I had it working. But the overall feel felt clunky for what we were doing (probably my fault) and on top of this a lot of the docs aren't in the same location so they're working on a separate version of the database so merging their results was hard (the relationships were based on autoID fields which would overlap between users). To counteract this I made some append queries to start autoIDing at 10000 in one persons' database and 20000 in another etc... but it was just becoming too much for me to to manage. I think your suggestion is obviously better ( it lets you conserve as much space as possible by having the fewest blank values) but it was something that I couldn't keep up with. If I get some smaller projects I want to start over and really think about how to work with normalized design, but it is a bit too late for this one. I just want a product that 'works' for the limited use of what we're doing.
Nick, I'm going to spend a lot of my time this weekend working on this form filter (it seems really cool and I've never seen anything like it). My question is, after making all of these filters how do we apply them to the task of filtering what is exported to word/excel (or potentially reports if that works)?
Also, thank you for sharing the MVPS resource it is very well documented!
Thanks again for everyone's thoughts and comments and I'll continue to work on this as best as I can with your guidance,
Bevo
Nick, I'm going to spend a lot of my time this weekend working on this form filter (it seems really cool and I've never seen anything like it). My question is, after making all of these filters how do we apply them to the task of filtering what is exported to word/excel (or potentially reports if that works)?
Also, thank you for sharing the MVPS resource it is very well documented!
Thanks again for everyone's thoughts and comments and I'll continue to work on this as best as I can with your guidance,
Bevo
@Bevos,
Wiser folks than I have chimed in, and they have a point.
I didn't question all your one-to-one relationships
I didn't question that NONE of the relationships in what you posted are one-to-many
My bad
I know you have been breaking the corallaries of Commandment #2
"Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One."
The other evils being multi-value fields, attachment fields and OLE object fields
I wonder, now, as JimD has pointed out, where is the 'relation' in your realtional database?
Here's a tutorial on normalization
http://www.phlonx.com/portfolio/nf/
Also attached is a spreadsheet with all your fields.
If your data really is normalized, each study will go down a SINGLE column.
(Too many fields to have it go the regular direction!)
If you have to enter multiple columns per call number, or many, many of the fields are empty, you have data design work to do!
Normalizing-check.xls
Wiser folks than I have chimed in, and they have a point.
I didn't question all your one-to-one relationships
I didn't question that NONE of the relationships in what you posted are one-to-many
My bad
I know you have been breaking the corallaries of Commandment #2
"Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One."
The other evils being multi-value fields, attachment fields and OLE object fields
I wonder, now, as JimD has pointed out, where is the 'relation' in your realtional database?
Here's a tutorial on normalization
http://www.phlonx.com/portfolio/nf/
Also attached is a spreadsheet with all your fields.
If your data really is normalized, each study will go down a SINGLE column.
(Too many fields to have it go the regular direction!)
If you have to enter multiple columns per call number, or many, many of the fields are empty, you have data design work to do!
Normalizing-check.xls
ASKER
Hi Nick, these are good comments. I can easily see how they apply to situations like in the tutorials. However, the data we are describing is 'unknown' in the sense that we don't know what information we will capture! That is to say, there is no 'standard' way for authors of academic journals to report things. Sure, sometimes it is common to have cancer mortality statistics, for example, as 1 - 5 year % survival, but there are many, many possible ways for the author to report these such as mean, median survival for a group, ranges of survival, patient level non-aggregated survival etc... This is the same for most of these outcome variables. It is a jungle out there. So, because we aren't certain what will be out there many fields will have to be created to accommodate these diverse outcomes. As you stated early no study will capture all of these fields, but the doctors should have a place to transcribe them on the off chance they do contain them. So, I tried to segment these into 'domains' that is to say intermediate outcomes (predictors of clinical outcomes), final outcomes (mortality), morbidity conditions etc....
These largely correspond each to a type of table that would be present in a report (i.e. for treatment X we have 11 studies with these mortality outcomes). And from these tables generated in Access we will try to assess the quality of evidence and make generalizations about the trends (most of this isn't amenable to meta-analysis because these are not all randomized clinical trials and thus we can only comment qualitatively based on biologic plausibility and observed trends).
I don't see how (again maybe because I'm new to access/databases/normaliza
One thing I do definitely see an opportunity for in terms of non-one-to-one relationships is based on 'groups' in the study. I had a database design for this, but elected for the 'flat' design because it could be sent out remotely to our collaborators (I don't wanna touch trying to do an online version of this database yet, haha). So basically when I am saying groups I mean different treatments applied to groups of patients (i.e. one group received placebo, one group received drug X, one drug Y, one combination X*Y etc...)
Other than this, everything is a 'one to one' relationship as far as I can see.
I am walking through the tutorial you sent me and it is pretty well designed and helpful. But I just don't see how it might apply in this specific context yet.
Thanks again,
Bevo
These largely correspond each to a type of table that would be present in a report (i.e. for treatment X we have 11 studies with these mortality outcomes). And from these tables generated in Access we will try to assess the quality of evidence and make generalizations about the trends (most of this isn't amenable to meta-analysis because these are not all randomized clinical trials and thus we can only comment qualitatively based on biologic plausibility and observed trends).
I don't see how (again maybe because I'm new to access/databases/normaliza
One thing I do definitely see an opportunity for in terms of non-one-to-one relationships is based on 'groups' in the study. I had a database design for this, but elected for the 'flat' design because it could be sent out remotely to our collaborators (I don't wanna touch trying to do an online version of this database yet, haha). So basically when I am saying groups I mean different treatments applied to groups of patients (i.e. one group received placebo, one group received drug X, one drug Y, one combination X*Y etc...)
Other than this, everything is a 'one to one' relationship as far as I can see.
I am walking through the tutorial you sent me and it is pretty well designed and helpful. But I just don't see how it might apply in this specific context yet.
Thanks again,
Bevo
We don't know your data, and you do!
The spreadsheet I attached is a simple way to see if your data is normalized.
If each Call Number really does only consume a single column, even if many rows are empty, then you do have it fundamentally right
<But I just don't see how it might apply in this specific context yet.>
You have a gnarly context, that's for sure
<I'm getting a brain cramp thinking about it>
If you start knocking data into that spreadsheet, you may see patterns start to emerge.
Fields where certain entries always fall together.
I think some of that already shows in your table design--some of those tables will get no entries at all depending on the study, correct?
Keep plugging away at it.
Data design is the fundamental part of what we do, and you HAVE to get that right first.
Operational logic --> how to get data in and out, and keep end users from fubar'ing it, that comes after
The spreadsheet I attached is a simple way to see if your data is normalized.
If each Call Number really does only consume a single column, even if many rows are empty, then you do have it fundamentally right
<But I just don't see how it might apply in this specific context yet.>
You have a gnarly context, that's for sure
<I'm getting a brain cramp thinking about it>
If you start knocking data into that spreadsheet, you may see patterns start to emerge.
Fields where certain entries always fall together.
I think some of that already shows in your table design--some of those tables will get no entries at all depending on the study, correct?
Keep plugging away at it.
Data design is the fundamental part of what we do, and you HAVE to get that right first.
Operational logic --> how to get data in and out, and keep end users from fubar'ing it, that comes after
ASKER
Okay, so from what I've seen I think I can make some comments. In an ideal settings (where I could have users access the same DB and thus not have autoID issues with relationships) I think I would like to have a 'group' variable described in treatment characteristics (basically group would comprise the patient characteristics for all different treatments in the study from 1, 2, 3...n) this way it wouldn't lock the user into a simple 'intervention/control' schema. Next, I could see the intermediate outcomes being separated into things such as 'diagnostic accuracy' 'hospital duration of stay/treatment' and in study quality each of those 'greyed boxes' which has the eight or so check boxes could be their own table (user will have to fill out all of them if they apply) and lastly some of the morbidity outcomes could be broken up ('specific morbid conditions', 'severity of complications', 'clinical outcome').
Other than these changes I'm not sure how else I can start segmenting these into further categories to avoid duplication.
Also, I suppose I should get rid of the multivalue list fields, but I like them because this is primarily a data entry tool. What do you suggest in their place? (also the lookup fields I guess should be replaced by lookup values?)
Bevo
Other than these changes I'm not sure how else I can start segmenting these into further categories to avoid duplication.
Also, I suppose I should get rid of the multivalue list fields, but I like them because this is primarily a data entry tool. What do you suggest in their place? (also the lookup fields I guess should be replaced by lookup values?)
Bevo
< I should get rid of the multivalue list fields>
They are evil.
They are a crutch to help you avoid normalizing your data, creating tables and relationships
In the end, Access is doing all of that--but hiding it in system tables, bloating the file, wrecking backwards compatability and upscaling (SQL Server) possibilities.
The stuff you are tempted to put in a multivalue field, you put in a table with an autonumber Primary Key, and you put that key in the table, instead of the multivalue field. On forms, you build a combobox to do the entry.
They are evil.
They are a crutch to help you avoid normalizing your data, creating tables and relationships
In the end, Access is doing all of that--but hiding it in system tables, bloating the file, wrecking backwards compatability and upscaling (SQL Server) possibilities.
The stuff you are tempted to put in a multivalue field, you put in a table with an autonumber Primary Key, and you put that key in the table, instead of the multivalue field. On forms, you build a combobox to do the entry.
ASKER
Ok, I'm gonna make that change too over the weekend.
Thanks,
Bevo
Thanks,
Bevo
<thus not have autoID issues with relationships>
????
Gathering data back into a master db is a bit of a pain, but it in no way makes the use of autonumber impossible.
Import/Export stuff is tough, but doable--especially if Call Number is going to be unique.
It has to be coded, but basically you compare tables, and append, while keeping track of the autonumbers generated.
Intellectually, it's tough to wrap your head around at first, but once you get the hang of it, it's no show stopper.
????
Gathering data back into a master db is a bit of a pain, but it in no way makes the use of autonumber impossible.
Import/Export stuff is tough, but doable--especially if Call Number is going to be unique.
It has to be coded, but basically you compare tables, and append, while keeping track of the autonumbers generated.
Intellectually, it's tough to wrap your head around at first, but once you get the hang of it, it's no show stopper.
ASKER
Wait, so I can conserve the relationships (ie. lets say two reviewers had the database and started from autoID 1) so that they didn't create misleading relationships?
That is news to me (very good news!).
I'm going to have to look into this.
Bevo
That is news to me (very good news!).
I'm going to have to look into this.
Bevo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Nick, I'm working through your example above now and hope that I can understand a few things about merging 'split' databases. I would also like to get a chance to talk with you outside of the site. If you could please e-mail me at bevos1984 (at) hotmail.com I would appreciate it. If not, that is fine as well and thanks for continuing to guide me through this process.
Bevo
Bevo
You've basically got the correct syntax, but the rub with a Union is that all the tables must output the exact same number and type of fields. You can't have 30 fields in one table and 20 in another.
JimD.