"merging" tables into one 2

[uscitizen]

i have a table with fields "IRB#" and "Name" among others in my a2k db. i have an excel spreadsheet that has the said two fields in it as well as others that i'd like to 'join' to the table. doable? i figure that converting the ss to an a2k table and then a query. could the results of the query be then converted to a table having the results from both a2k tables 'side by side' so to speak?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[bhoran]

Yes you can do this I am not exactly sure what you mean by "join" but it sounds like you need to import your spreadsheet into a new table.

Using the Get External data/Import in the File menu.

Then you can create a query over the existing table and the new table once that have been created you can change the query to a maketable query and create a new table. The only issue will be the names of the fields you cannot have 2 fields by the same name in one table, you may wish to assign aliases to the make table query or simply change the names on the new table after you import the Spreadsheet.

Why and how do you want to join these fields if you want to compare the data on the spreadsheet with that on the existing table you don't need to create the "make table" query but simply create a query that compares adat over the 2 tables (the new one imported from the SS and the existing)

 

[BNPMike]

What I normally do is to import the spreadsheet using the wizard. You can then type in field names that are the same as your target table. Press the button and check your data is imported cleanly. Now create a select (all fields), check that and if it's showing everything you need click on append query. Access will automatically fill in all the field names for you.

If you're doing this regularly you can retain the import specifications, and build a simple macro to automate these steps.

Remember you'll need to analyse your data model to see if you want to break it into more than one table, etc etc

 

[uscitizen]

Thanks folks. 'Tworks.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

but let me ask you folks this question:

i just discovered that when running a make table query all of the good properties like the formatted y/n and combo box tables seem to fall by the wayside :-( which means having to cobble them back into the synthesized table one would guess. but, is there a workaround.....could i use the select table query and still let my user edit data in the resulting join query?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[BNPMike]

Your target table has fields from your original table and your imported table.

Set up your target table by hand giving it all the configuration settings you want (eg Y/N). You load this initially from your original table with and Append Query. Then you add in the spreadsheet data by joining to the spreadsheet table and Updating matching records. Afterwards all the records and fields have the settings of your table.

To answer your question, you can leave the tables separate and update them from a select query which joins them. If however you expect items to generally be on both sets of data I would prefer to merge them into one actual table.

 

[uscitizen]

hi, let me use some names and hopefully elicit a detailed prescription....

i have an existing table called 'Protocol' (which has btw ca 842 records) and i have another table called 'Pathology' (the one i generated from the xls spreadsheet) with 36 records (not every protocol has pathology data associated with it). the field for linking would be "ID_Number" is common to both tables.

i would be developing a data entry form for a user with a couple of fields from "Protocol" and all 4 fields from "Pathology" which the user would like to have add/delete/update capability.

after using make table query to synthesize the two tables, it became obvious that the niceties were gone (the l/u tables and y/n fields etc). ideally i'd like to have the synthesized/join table but i don't quite get the streamlined method you allude to. if not a synthesized table, would having a select query as the data source to the form i'm developing work for this user?



“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[BNPMike]

I don't understand why you are doing a make table. Protocol already existed. Pathology is one I suggested you design yourself.

I suggested you might want to merge them but as one only had 36 rows maybe keep them separate.

As long as each table only has one row per ID_Number than you can create a Select statement joining them together and displaying the fields you want the user to see. You can set the properties of this Query so for example it shows a tick box for Y/N fields. This query us updatable so you can use it for forms. However I'm not sure what Access will do if you try to add a record, given there may or may not be Pathology data. You'll have to try it but I recommend you read through the help sections on Forms and Sub Forms (eg Link a main form and a subform. There is a wizard that will create this type of form for you.

 

[uscitizen]

i guess i must've not gotten the message i intended across....there is only supposed to be one form (w/o any sub-forms). the form will have some fields from the 'Protocol' part of the query's join and some from the 'Pathology' part's.

two things, the relationship between the 'Protocol' and 'Pathology' tables (using the ID field - of which there will never be more than one having the same value per table) is such that when the join takes place all 'Protocol' records and only those 'Pathology' table records having a matching ID number in 'Protocol' will get through. oh, yes, i asked for 'Referential Integrity' because I think that we don't want the person working with the 'Protocol' table by itself deleting any records that have matching ID numbers in the 'Pathology' table.

perhaps things would be easier if there weren't so many fields that lose their 'nice' properties (y/n, l/u) when you use a make table query to create a synthesized table having the joined data from the 'select' query using the above said relationship.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

oh, yes, on the question of what happens when you use a form whose data source is the joined select query result into which you add a new record. empirically, i added a new record and had no problem completing the fields from the 'Protocol' table (they number around 4) until i arrived at the first of the two remaining fields which occur on the 'Pathology' table. at that point, it wasn't possible to enter the data ---- until, i.e., i scrolled back to a previous record and then scrolled to the just entered record; at that point, things behaved more copacetically, and the data from the 'Pathology' table was entered successfully

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

...err, however, i should've added that deleting the just added record proved slightly more complex. despite what appears to be the successful carving out of the record from the form's interface, it persists, requiring my removing it from the 'Protocol' table itself!? can anybody explain?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[bhoran]

Ok it depends what you are doing here.

It seems you have 2 tables and want 3 functions available.

tbles
Protocol
Pathology

Protocol has ID linked to Pathology ID (but some Protocol ID's have no matching Pathology ID's i.e. no pathology records.)

I suggest you setup 2 forms, 1 for adding a new record to Protocol & thereby Pathology if necessary. This form might best be split into a main form (source Protocol) and subform (source Pathology). then put the fields to be added in the Proctocol (main form) and then the ID of the subform can equal that of the Protocol. If I am correct in believing a protocol record can be entered with without a pathology record you will need to have a user defined function such as a button to define if the pathology ID should be updated and the rest of the pathology record be made available.

You will also need another form to update and delete the records. You can update by simply overwriting records where the controls are linked to the tables and again using the main/Subform will allow tis. However, you cannot delete the record by blanking out the record on the form, you will instead have to perform a task, probably building a delete query will be the best option here, and a button to invoke it.

After you have all that sorted you will need to look at validation - is the user updating with the correct information, have they left something blank when they should not, are they adding a record that already exists etc etc.

I hope I understood your questions correctly.

NB you will also need to look at the type of join you have in your queries as I imagine you will want to view all records in Protocol and those that match in Pathology where as the standard join will only allow you to see records that have the same ID in both tables thereby excluding the Protocol ID's without a Pathology ID.

 

[uscitizen]

thanks, worthwhile delving into all this later.....although (scratching my noggin) i'm starting to think that perhaps it'd be somewho simpler to just make a table with the added fields (that resulted from the join) and cobble back into it all the lost properties (y/n, combo box goodies) that get lost in the process of sql's making the new table.

someone earlier suggested using an append...is what was meant that a) first an empty structure having all the properties/goodies be created and then b) after sql's created the new table with the added fields from the 'Pathology' table that we c) create an append query to append the table to the empty structure?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[bhoran]

Thts is correct ao you could copy one table or the pthwr (structure only, add any extra fields and get the correct formatting etc then use delete and append commands/queries to delete/add data as necessary.

I am still not sure whay you want a new table to store this data - unconvinced it is needed or prudent.

If you build a new table how will you protect the integrity of it in data in the other tables change (how will it be reflected in the new table)?

 

[uscitizen]

i think i'll answer the last point you're raising and then consider the issue resolved.....

the new table will take the place of the 'Protocol' table (i.e., the new table will do the job of both the 'Protocol' and 'Pathology' tables); i hope this helps.

thanks a lot for the bandwidth!

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[bhoran]

I wonder if you would be better off simply linking the 2 separate tables via queries to give you the data you want in the format you want rather than building a new table that will not automatically reflect the changes in either Protocol or Pathology table.

If you keep the 2 table scenario you can update either table with relevant information fron Spreadsheets or wherever else without affecting the integrity of the other table.

if you use the same type of query that you used to make the new table (as a select query) you can report on the data or export it to another format (back to excel?) and do whatever else with it.

This would be a better alternaltive to merging those tables into one. Unless of course you have a great reason for merging them.