Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Andries,
Take a look at the attached file.
Laat me even weten of dit de oplossing biedt is wat je zoekt 🙂
Succes,
Dennis.
Hi Andries,
Take a look at the attached file.
Laat me even weten of dit de oplossing biedt is wat je zoekt 🙂
Succes,
Dennis.
check out the attached file
hei
check this example
all the calculating is benig done in the script
the result is one table