Combining records from 2 tables

[AndyL]

Hello all,

I have two MS Access tables; table1 contains installing records (fields = serial, installdate) and table2 contains de-installing records (fields = serial, deinstalldate). Tables are structured as followed:

Table1:
serialX installdate1
serialX installdate2
serialX installdate3

Table2:
serialX deinstalldate1
serialX deinstalldate2

I'd like to combine the above two tables to get:

Table3:
serialX installdate1 deinstalldate1
serialX installdate2 deinstalldate2
serialX installdate3 null

Is it possible? Right now, whenever I joined the two table on serialX I got 2 deinstalldate's for each installdate, e.g.:

serialX installdate1 deinstalldate1
serialX installdate1 deinstalldate2
serialX installdate2 deinstalldate1
serialX installdate2 deinstalldate2
..........

Thanks in advance for your time and help! I'd appreciate it!

Best regards,

Andy

 

[Zigzag]

Hi Andy,

If Post up your 2 tables we can have a play around with them a try to come up with a solution for you.

Garry

 

[neileg]

You need to change the join type in your query. By default, Access uses an inner join (type 1 in Access speak) which only returns results where there is a match in each table. You need to change the the join to an outer join (type 2 or 3). You need to select the one that returns all of the records in table 1 and any that match in table 2.

 

[AndyL]

You need to change the join type in your query. By default, Access uses an inner join (type 1 in Access speak) which only returns results where there is a match in each table. You need to change the the join to an outer join (type 2 or 3). You need to select the one that returns all of the records in table 1 and any that match in table 2.

I have tried the left join option, and I still got the same results i.e. 2 deinstall date's for each install date.

If Post up your 2 tables we can have a play around with them a try to come up with a solution for you.

Attached please find an Access db containing the two sample tables mentioned in my original post.

Thanks for all your quick responses!

Regards,

AndyL

 

[neileg]

Yes I see now what you want. The first problem is that there is nothing to identify which deinstall date matches the install date other than the chronological sequence. In which case you are going to have to use VBA to step through the data to identify this relationship. I don't like this kind of approach, so can't help you with the code. If I had designed the application, I would have made the connection at the time of entering the deinstall date. Maybe you don't have that option.

 

[AndyL]

So, is there a way to assign an order number to each and every one of the install and deinstall records based on chronological sequence? For example:

Table1:
serialX installdate1 order1
serialX installdate2 order2
serialX installdate3 order3

Table2:
serialX deinstalldate1 order1
serialX deinstalldate2 order2

Then, I can just join the two tables by serial and order, which I believe should give me what I need.

Please let me know if you need more clarification!

Thanks and best regards,

AndyL

 

[neileg]

As I said, you will need to step through the records in VBA to assign the number. This is why I would do it at the data entry stage, not try and apply it later.