concat two different tables to one new table (different field names) for single projection

[andriesb]

I want to concatentating two tables : the extra information should create duplicated records...

I have two tables where the second tabel contains ‘extra’ information within a 1:n relation.

The first table contains the vehicle general information

vehicle	number	Color
Alfa	12-34-56	White
BMW	55-66-77	Blue

The second table contains the inspections:

Number	inspection types	Last inspection	next inspection	Header 5
12-34-56	Yearly inspection	01-01-2010	01-01-2011
12-34-56	Yearly inspection	05-01-2011	05-01-2012
12-34-56	4 years inspection	06-05-2010	06-05-2014
55-66-77	Yearly	05-05-2011	05-05-2012

I would like to merge these two tabels into one where the outcome should be :

vehicle	number	Unique inspection types	Last	Next
Alfa	12-34-56	Yearly inspection	05-01-2011	05-01-2012
Alfa	12-34-56	4 years inspection	06-05-2010	06-05-2014
B	55-66-77	Yearly	05-05-2011	05-05-2012

It should merge from the original database the unique different inspection types (1:n relation) and project only the last known inspection date info my overview table: this means i could filter (by date) only the inspection and inspection types to still be done within the selected month

as an example, the resulting merged table should be shown in a 'flat' perspective:

How could I join information into a resulting table whereby the resulting table creates extra (duplicate) records to contain the information.

In words the soluton could be : iterate through the cars list ; search for the unique types of inspections avaiable for this car, select by mapping the max(first date) from the database...

I'm used to program, but to add all these conditions into a single "load" seems to be quite difficult.

Some ways to solve this by providing pseudo code or step by step info would be appreciated.

[Anonymous · Accepted solution]

Hi Andries,

Take a look at the attached file.

Laat me even weten of dit de oplossing biedt is wat je zoekt 🙂

Succes,

Dennis.

[giakoum]

check out the attached file

[lironbaram]

hei

check this example

all the calculating is benig done in the script

the result is one table