Combining a Linked data table with a seperate non-linked table

[SPisarek]

Hello everyone!

I'm new to Access in general, and I am creating a database for the company I'm with right now. I was wondering if anyone could help me out with this, as I am thoroughly stuck.

We have an outside source of excel formated information that is being consistently updated by a linked table. And we have additional information of the same kind being put into a different table because it isn't from the same source. There is so much data that going through and transferring them all to one table is just not reasonable.

Is there a way to combine them, and then use a form to be able to access/see these reports? Do I need to use a query?

Thank you for your help!

[June7]

Use query or form/subform.

Do these tables have a relationship - is there a common identifier? One-to-One, One-to-Many, Many-to-Many?

What you mean by 'combine'. Do you want to JOIN them on a common identifier? Or do you want to UNION the tables?

[SPisarek]

Thank you June7!

I have been thinking of creating a relationship between them, but I don't know if I need to create a relationship between all the fields they have in common, or just one. And I'm sure it would be a one-to-one relationship. I don't have any need for the information to be duplicated to any more tables.

I was thinking if I could put all of the information into one table, that would be great. I have heard of union queries, but when you join tables on a common identifier, what does the final product look like?

[June7]

If you join on common identifier(s), the result will be a dataset that has columns from both tables. If tableA has fields EmpID, LName, FName and table B has fields EmpID, Address, City, State then a join would be a dataset with fields EmpID, LName, FName, Address, City, State.

Now with a union, if tableC (20 records) has fields EmpID, LName, FName and tableD (15 records) also has fields EmpID, LName, FName the query dataset would have fields EmpID, LName, FName and if none of the records are identical between the tables, the record count will be 35.

[SPisarek]

Alright, so based off of what you have said, I need to use a union query to combine them. Do I still need to worry about report ID's when doing that? Or does it not matter when making a union query?

[John_G]

Hi -

A union query with two tables, and a join between two tables are totally different concepts.

For a union query, the two (or more) tables to be combined must have the same (or almost the same) structure, i.e. they must contain essentially the same fields. A union query combines all the records in one table with all the records in another table, for example combining two lists of addresses into one big list.

A join on the other hand takes the data from two tables and joins them together using one or more fields that are common to both tables (there MUST be at least one such fields). Each record in the resulting query will have all the fields from the first table PLUS all the fields from the second table.

Example:

Supposing a business has all the personal data for employees in one table (Employee_ID, name, home address, home phone, etc., and all the office data (Employee_ID, cubicle, office phone, office E-mail,..etc. in another table.

If you join these two tables using the Employee_ID, then each record in the resulting query will have all the home and office data for one employee.

Now, which of these two you need depends on the data you are working with. It sounds like you have the same type of data from two different sources, and you want to combine them in one report, so probably a union query will be the one to use.

[SPisarek]

John_G,
Yes, when you said it sounds like I have pretty much the same information in each table, that's right. I haven't really done a union query before, so I'm going to look it up before I try, but it sounds like the best fit.
When making a union query, one of the tables I have has like two or three additional fields that aren't in the second linked table. That is still ok right? The linked table just won't fill in those fields?

[John_G]

I think union queries (I don't use them much) get unhappy if the number of fields in each Select is not the same, but thats easily fixed - just adjust the different Select parts so you have the same number of fields:


Select F1, F2, F3, F4 from table1
UNION
Select F5, F6, F7, F8 from table2

to select 4 from each, and as long as the data types match up, it should work fine.

[June7]

Remember I said a UNION will not allow duplicate records (UNION All will). This means if you include the 3 additional fields from one table, you could end up with duplication of data from the other fields.

SELECT Field1, Field2, Field3, Field4, Field5, Field6 FROM TableA
UN ION SELECT Field1, Field2, Field3, Null, Null, Null FROM TableB;

See if that structure gives you want you want.

There is no query builder/wizard for UNION, must type or copy/paste in SQL View of query designer.

The first SELECT row sets the field names so can use AS alias if you want to use a name more descriptive for the new dataset.

[SPisarek]

Ok. Thanks guys. I think that makes sense! I'll test it out and see how it goes. Thank you for the help!