Merger of excel databases in one, with different numer of columns and some matching data.

[Batguy]

Hello, I'm having a trouble with Access and I would appreciate some help.


I have to merge 4 excel databases in one. Those databases have a different quantity of columns, some of them match between the 4 and some of them just appear on 1 or 2 databases. In addition, all of them have to be ordered by the data of one column.


I mean, in all the databases I can find the column "Species", then they have different data that may not appear in all databases. The data inside "Species" is different in every database, some of the coincide and some of them not.


So my objective is to merge all the databases ordened by "Species", then, if there's a match between some databases in the column "Species", I must make a mean with all the data that have that specific species.


In the end, the result shall be one database with the data of all databases, where data can be from one table or, if is from more than one, must be a mean between those.


Well, I try my best to write it clear. I appreciate any help.


Thank you so much.

[alansidman]

Can you upload a sample of each of the databases that you wish to consolidate. If we can see what you have (only need 4-6 entries for each database), then we can offer up a solution. I have an idea on how to accomplish this but would really like to see some sample data to ensure I completely understand.

[Batguy]

Here I've attached a sample for each database. The first one (A) have 14 columns, the second (P) 15 columns, the third (E) 5 columns and the last one (S) 6 columns. The column "Binomial" (I've changed the name from "Species" to "Binomial") is the one that order all data.
(A)
(P)
(E)
(S)

[alansidman]

It would be much more helpful if you upload these samples instead of creating pictures. I cannot test any possible solution without recreating your data. That is something I will not do. I don't have the time and am unsure of how the data is formatted.

I imagine that the solution will involve creating queries to make each of the tables look exactly alike in the layout. Then once that has occurred, a Union query will be developed to join all the data and then a Make table query.

[ArviLaanemets]

Am I right assuming that:
for every Binomial is no more than 1 entry in any of Excel tables;
when 2 tables have same parameter (except taxonomy ones) in different tables, then the sets of species in those tables are different (Binomials aren't repeated).

When yes, then:
1. Link all 4 Excel tables into your Access database;
2. Create an Access table with structure like tblBinomial: Binomial, Genus, Family, Order
2. Create an Access table with structure like
tblBinomialInfo: Binomial, Genus, Family, Order, FemaleMat, MaleMat, DietBreac, Gestation, Habitat, HomeRange, Weaning, LitterSize, LittersPe, InterLitter, MaxLonge, Population, 23Minus1Sex, ActivityPa, BodyMass, Reference;
3. Create e.g. OnOpen event for your database, which runs 2 sets of queries to clear old data from tblBinomial and tblBinomialInfo, and after that to run queries ti insert data from linked Excel tables at 1s to tblBinomial and then to tblBinomialInfo.

Queries on fly

INSERT INTO tblBinomial
SELECT DISTINCT Binomial, Genus, Family, Order FROM LinkedTable1
UNION SELECT Binomial, Genus, Family, Null AS [Order] FROM LinkedTable2
UNION SELECT Binomial, Null AS [Genus], Family, Null AS [Order] FROM LinkedTable3
SELECT Binomial, Genus, Family, Order FROM LinkedTable4

INSERT INTO tblBinomialInfo
SELECT
bi.Binomial, bi.Genus, bi.Family, bi.Order,
lt1.FemaleMat, lt1.MaleMat, lt1.DietBreac,
Nz(lt1.Gestation, lt2.Gestation) AS [Gestation], lt2.Habitat, lt2.HomeRange,
Nz(lt1.Weaning, lt2.Weaning) AS [Weaning], Nz(lt1.LitterSize, lt2.LitterSize) AS [LitterSize], Nz(lt1.LittersPe, lt2.LittersPe) AS [LittersPe],, lt1.InterLitter,
Nz(lt1.MaxLonge, lt2.MaxLonge) AS [MaxLonge], lt2.Population, lt2.23Minus1Sex, lt3.ActivityPa,
Nz(lt1.BodyMass,Nz(lt2.BodyMass, Nz(lt3.BodyMass,lt4.BodyMass))) AS [BodyMass], Nz(lt1.Reference,Nz(lt2.Reference, Nz(lt3.Reference,lt4.Reference))) AS [Reference]
FROM tblBinomial bi
LEFT JOIN LinkedTable1 lt1 ON lt1.Binomial = bi.Binomial
LEFT JOIN LinkedTable2 lt2 ON lt2.Binomial = bi.Binomial
LEFT JOIN LinkedTable3 lt3 ON lt3.Binomial = bi.Binomial
LEFT JOIN LinkedTable4 lt4 ON lt4.Binomial = bi.Binomial

When this is once-time action, then simply skip event creating, run those queries directly, and when all went OK, delete queries, linked tables, (and tblBinomial, when you don't have a need for it anymore).
Btw, access may be not cooperate with you having field name Order in database and even worse, in query.