Linked Tables Merge

[annonymous]

Hi,

I use these forums a lot for advice, and help, so thanks to the community for helping simpleton, like myself

I don't code using freehand, i just use the guided approach.

My issue is, I now want to merge two linked sheets, into one, and using those results create a query. So i want all of the data displayed, from both sheets, so every unique FLOW ID.

As you can see from the screenshot i don't really have that option. Is it not possible to merge using linked sheets?

I've attached a screenshot.

[Bob Fitz]

Not sure why you would have 2 tables with the same fields. Perhaps you could append the data from one to the other.

Alternatively you could use a UNION query to join the data from the two tables. Unfortunately, you would need to write the SQL statement for the query. See: https://www.w3schools.com/sql/sql_union.asp

Post back if you need further help

[Bob Fitz]

Your UNION query would be something like:

SELECT [Flow ID], [Vessel],[Vessel IMO], [Load Country], [Load Port] FROM [Flows]
UNION
SELECT [Flow ID], [Vessel],[Vessel IMO], [Load Country], [Load Port] FROM [Flows 2];

I can't see the names of the other fields. BTW It would be better NOT to have spaces in the names of any Objects e.g. "LoadPort" rather than "Load Port" and "Flow2" rather than "Flow 2"

[Welshgasman]

I cannot see the purpose?, as the ID fields are linked, so surely duplicate information?

[moke123]

So i want all of the data displayed, from both sheets, so every unique FLOW ID.

Maybe a more detailed description of the 2 excel sheets would help.
What's the same and what's different between the 2 data sources?
Are these matching data sets and you want to merge missing data from flow2 into flow, or vice versa?
Are the FlowID's the same in both files?

[annonymous]

I cannot see the purpose?, as the ID fields are linked, so surely duplicate information?

Sorry, its time series data, Historical part is in one sheet, the current part is in the other

[annonymous]

Not sure why you would have 2 tables with the same fields. Perhaps you could append the data from one to the other.

Alternatively you could use a UNION query to join the data from the two tables. Unfortunately, you would need to write the SQL statement for the query. See: https://www.w3schools.com/sql/sql_union.asp

Post back if you need further help

Hi Thanks for this. Really kicked me in the right direction. Im now at this point, trying to do an inner left join to bring the route taken based on each of the ID's. I've lifted the script from one of the other queries i ran, using the guided system. Am I missing something?

SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsCurrent] LEFT JOIN [Traced_Cargoes] .[Flow ID] = [Traced_Cargoes].[ROUTE])
UNION SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsHistory] LEFT JOIN [Traced_Cargoes] .[Flow ID] = [Traced_Cargoes].[ROUTE]);

[annonymous]

Maybe a more detailed description of the 2 excel sheets would help.
What's the same and what's different between the 2 data sources?
Are these matching data sets and you want to merge missing data from flow2 into flow, or vice versa?
Are the FlowID's the same in both files?

Hi, thanks for responding. each flow ID is unique, its data spanning many years, and each flow id represents a new entry, I am combining the data into one query.

[Welshgasman]

So what is the reason for the link? if they can never be the same?

Just append all into Flow3.

[annonymous]

So what is the reason for the link? if they can never be the same?

Just append all into Flow3.

Because they are linked sheets i dont think you can

[annonymous]

Your UNION query would be something like:

SELECT [Flow ID], [Vessel],[Vessel IMO], [Load Country], [Load Port] FROM [Flows]
UNION
SELECT [Flow ID], [Vessel],[Vessel IMO], [Load Country], [Load Port] FROM [Flows 2];

I can't see the names of the other fields. BTW It would be better NOT to have spaces in the names of any Objects e.g. "LoadPort" rather than "Load Port" and "Flow2" rather than "Flow 2"

Hi Bob, Thanks a ton for your help. IVe responded to this, its really helpful, im just waiting for the moderator to approve

[Bob Fitz]

Hi Bob, Thanks a ton for your help. IVe responded to this, its really helpful, im just waiting for the moderator to approve

You're welcome. Always glad to help when I can.

[annonymous]

You're welcome. Always glad to help when I can.

Ill re-post, see if it works. The current query looks like this


SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsCurrent]
UNION SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsHistory];

I want to add to it, so it brings in the route taken. Please see the attached.

SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsCurrent] LEFT JOIN [Traced_Cargoes] ON [Tradeflows].[FlowID] = [Traced_Cargoes].[ROUTE])
UNION SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsHistory] LEFT JOIN [Traced_Cargoes] ON [Tradeflows].[FlowID] = [Traced_Cargoes].[ROUTE]);

[Bob Fitz]

I think you need to save the following query which I think you've called TestQuery:
SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsCurrent]
UNION SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsHistory];

Then create a new query. Add "TestQuery" and "Traced_Cargoes" to the new query and join on the appropriate fields.

[annonymous]

I think you need to save the following query which I think you've called TestQuery:
SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsCurrent]
UNION SELECT [FlowID], [Vessel],[VesselIMO], [LoadCountry], [LoadPort] FROM [FlowsHistory];

Then create a new query. Add "TestQuery" and "Traced_Cargoes" to the new query and join on the appropriate fields.

Ah thanks. Might sound stupid, but in VBA, and native excel, or a result to be held in memory, you need to use an array. If one query creates the table, and you start another query, wouldnt the results be just replaced with the new?