Group data and generate report with missing dates

[Mgrama1]

I have data in the following format.
12345678, start-1-1-17, stop-20-1-2017


12345678a, start-23-1-2017, stop-31-1-2017
13567, start-.....

I wish to group the similar numbers regardless the alphabets and compare their dates. If there is a missing date, the report should reflect it. E.g. the sample above, 12345678 has date 21-1-2017 and 22-1-2017 missing.

Please guide. Appreciate it.

[June7]

You would need a dataset of all possible pairs of whatever values 12345678 represents and the dates to determine what is missing from the data.

[CJ_London]

As June says, create a reference table of dates - just a single date field - and populate it with all dates for your period under review - might be from 1/1/2017 to 31/12/2017 or 1/1/2000 to 31/12/2100, whatever you need

then left join this table to your other table on the start/stop date field.

You will then need a criteria to limit the date range for each part to the earliest and latest dates.

if your start/stop date fields are two separate fields in your table, you have made life complicated for yourself - you should have a table with a single start/stop date field and a field to indicate which it is

providing your alpha part of your code is always at the end, you can use the val function to exclude the alpha character

[Mgrama1]

You would need a dataset of all possible pairs of whatever values 12345678 represents and the dates to determine what is missing from the data.

Apologies.
What do you mean by "dataset of all possible pairs and dates"

[Mgrama1]

Thanks and i appreciate it. Will try and revert.

[Mgrama1]

As June says, create a reference table of dates - just a single date field - and populate it with all dates for your period under review - might be from 1/1/2017 to 31/12/2017 or 1/1/2000 to 31/12/2100, whatever you need

then left join this table to your other table on the start/stop date field.

You will then need a criteria to limit the date range for each part to the earliest and latest dates.

if your start/stop date fields are two separate fields in your table, you have made life complicated for yourself - you should have a table with a single start/stop date field and a field to indicate which it is

providing your alpha part of your code is always at the end, you can use the val function to exclude the alpha character

Could you please walk me through the steps. I am a rookie. Appreciate it.

[CJ_London]

Due to work (paying) commitments, I don't have time to help at a detailed level, the steps are self explanatory so what specifically do you not understand?

[Mgrama1]

Due to work (paying) commitments, I don't have time to help at a detailed level, the steps are self explanatory so what specifically do you not understand?

1. I managed to create the date table which only has one field. I believe this table is where the dates would be drawn out or compared against.

2. The other table has dates as well but separated into 2 fields, start and stop date.

3. Can you please explain how I could go about convert both the date fields into a single field.

Thanks.

[June7]

After looking at the original post again, I realize this is even more complicated than I thought. Since the table has date range fields for each record instead of a record for each date within that range, finding gaps will be difficult. Your date range fields aren't even date/time type. Looks like this will involve VBA and writing records to another table. Sorry, not something I want to take on.

[CJ_London]

Can you please explain how I could go about convert both the date fields into a single field.

instead of a table structure

Product...Start..........Stop
12345....01/01/17....20/01/17
12345a..23/01/17....31/01/17


you have

Product....Event.....SSDate
12345.....Start......01/01/17
12345.....Stop......20/01/17
12345a...Start......23/01/17
12345a...Stop.......31/01/17

note: do not be tempted to use 'Date' as a field name - it is a reserved word (the equivalent of Excel's Today function)