Multiple Linked tables update into one main table. 1

[raboo2u]

Hi everyone. I have 4 linked tables.

Bugs1
Bugs2
Bugs3
Bugs4

All of these tables have the following fields.

IssueID
Title
Description
Priority
State
Release
SubmitDate
PlannedCompletion
CloseDate
ReasonForRejection

I have one main table called BUGS that contains the exact same fields. I need to combine all these fields from the linked tables into one table [BUGS] and have it automatically update when new data is added to any of the linked tables. For instance I add any information to any field in Bugs1, I want it to automatically update the BUGS table.

 

[shannonp]

Sounds like you need a union query rather than duplicating these tables information within another database. Try this:

1. Make a New Query

2. DONT select any tables

3. Go to 'SQL' view

4. Add the following SQL statement:

SELECT *
From Bugs1
Union Select *
From Bugs2
Union Select *
From Bugs3
Union Select *
From Bugs4;

5. Save your union query (as 'Bugs')and run.

As long as your table field names are standard throughout it should work fine.

If you still wanted to make a MASTER table with all information then simply create a 'Make-Table' query based on the above 'Union' query saving it to your nes table "BUGS". Then every time you run the 'Make-Table' query you're essentially overwriting / refreshing with the latest data.

Let me know how you go

 

[raboo2u]

That sounds like it will work, but is there a way to run those queries automatically so that when Bugs1, Bugs2, Bugs3, Bugs4 have new information the Table (from Make-Table) is automatically rebuilt? Also, on the union query does that have to be run each time the new information is added to the 4 tables? If so, is there a way to have that run automatically? The goal is to have this all automated.

 

[shannonp]

The union query basically joins ALL four tables together although it does need to be re-run it is a simple matter of requerying the query.

You can make it look like it is automatically updating but any way you look at it you'll have to do a Requery or table update anyhows.

 

[raboo2u]

I followed your steps to create a union query and I get the following message. "Can not use memo, ole, or hyperlink object in field description in the select clause of a union query." My description field is memo in all tables. So does this mean that that field can not be a memo type?

 

[shannonp]

Umm yeah, unfortunately memo fields cannot be used when combining tables via a union query. You have two options, firstly, if it were possible for you to change this to a text field it would of course work, but I guess most likely your description field goes over 255 characters (which is why I guess its of memo type tyo begin with). Second option would be to ommit Description all together.

I realise both of these options come with compromises but if I think of an alternative, I'll let you know...

 

[raboo2u]

Thank you for all your time. I really appreciate it. Unfortunately I really need to keep the description. If you think of anything please do let me know.

 

[shannonp]

OK try this...

Copy the following code into a Union Query (call it "BUGS_A&quot note that at this stage we're ommitting [Description] from our Union Query.

SELECT [IssueID],[Title],[Priority],[State],[Release],[SubmitDate],[PlannedCompletion],[CloseDate],[ReasonForRejection]
From Bugs1
Union Select [IssueID],[Title],[Priority],[State],[Release],[SubmitDate],[PlannedCompletion],[CloseDate],[ReasonForRejection]
From Bugs2
Union Select [IssueID],[Title],[Priority],[State],[Release],[SubmitDate],[PlannedCompletion],[CloseDate],[ReasonForRejection]
From Bugs3
UNION Select [IssueID],[Title],[Priority],[State],[Release],[SubmitDate],[PlannedCompletion],[CloseDate],[ReasonForRejection]
From Bugs4;

Next make a 'standard' select query and copy this SQL code to it (using SQL view)

SELECT BUGS_A.IssueID, BUGS_A.Title, [Bugs1].[Description] & [Bugs2].[Description] & [Bugs3].[Description] & [Bugs4].[Description] AS Description, BUGS_A.Priority, BUGS_A.State, BUGS_A.Release, BUGS_A.SubmitDate, BUGS_A.PlannedCompletion, BUGS_A.CloseDate, BUGS_A.ReasonForRejection
FROM (((BUGS_A LEFT JOIN Bugs1 ON BUGS_A.IssueID = Bugs1.IssueID) LEFT JOIN Bugs2 ON BUGS_A.IssueID = Bugs2.IssueID) LEFT JOIN Bugs3 ON BUGS_A.IssueID = Bugs3.IssueID) LEFT JOIN Bugs4 ON BUGS_A.IssueID = Bugs4.IssueID;

Call this query 'BUGS' which will now act as your "Combined Master Bug Query".

This may not be the smartest way to circumvent memo fields but it does work.

 

[raboo2u]

Ok...after I did the union query we realized that the ReasonForRejection field is also memo. So do I need to modify the second statement?

 

[raboo2u]

I removed the ReasonForRejection from the union join and that one brings up all the data from the other fields perfectly. So I took the code you gave me above and that adds the description perfectly. I am not sure how to get the ReasonForRejection added to that.

 

[shannonp]

Oh ok

GO to Datasheet view of 'BUGS' and add a column with a Field Value of:

ReasonForRejection: [Bugs1].[ReasonForRejection] & [Bugs2].[ReasonForRejection] & [Bugs3].[ReasonForRejection] & [Bugs4].[ReasonForRejection]

Basically what Im doing is concatenating all four [ReasonForRejection] fields together, being that 3 of the 4 fields are NULL (or blank) it should always show valid data.

 

[raboo2u]

Ok. Now it is saying that the field is too small to accept the amount of data I attempted to add. Try inserting or pasting less data. I really appreciate your help! I am impressed with your knowledge.

 

[shannonp]

yes...here's the full SQL code to use...

SELECT BUGS_A.IssueID, BUGS_A.Title, [Bugs1].[Description] & [Bugs2].[Description] & [Bugs3].[Description] & [Bugs4].[Description] AS Description, BUGS_A.Priority, BUGS_A.State, BUGS_A.Release, BUGS_A.SubmitDate, BUGS_A.PlannedCompletion, BUGS_A.CloseDate, [Bugs1].[ReasonForRejection] & [Bugs2].[ReasonForRejection] & [Bugs3].[ReasonForRejection] & [Bugs4].[ReasonForRejection] AS ReasonForRejection
FROM (((BUGS_A LEFT JOIN Bugs1 ON BUGS_A.IssueID = Bugs1.IssueID) LEFT JOIN Bugs2 ON BUGS_A.IssueID = Bugs2.IssueID) LEFT JOIN Bugs3 ON BUGS_A.IssueID = Bugs3.IssueID) LEFT JOIN Bugs4 ON BUGS_A.IssueID = Bugs4.IssueID;

 

[shannonp]

hmmmm, never had that error before??? How big is your program? - you could send to giavargo@xtra.co.nz if its < 2MB.

 

[raboo2u]

Hi. I figured out what was the problem. I am linking the Bugs1 - Bugs4 from excel spreadsheets and excel was reading some rows as text data type and others as memo data type. I had to trick excel to reading all rows as memo. Now I added the SQL code to that and it works great. Thank you again for all your help!!!