create query to generate data by searching through records between 2 tables.

Probably the best solution would be to write a VBA method that will iterate all records and fill the missing dates/containers

>>but the rest of containers that I plug in 003 ,004, 005 etc, only partial data is generated.

can you provide us some data for your tables: tblDates and tblChanges?

I'm just thinking to use several sets of "unions with left join" (just a guess...) to construct the results you want, but first pls provide the required info so we can better understand your issue.

tblDates(with dates pre put  1/9, 2/9,3/9...8/9)

tblChanges
Date1   ConNo  Value
1/9      001           A
3/9      001           B
7/9      001           C
1/9      002           A
3/9      002           B
             .
             .
            005

tblDates has only 1 field

date
1/9  
2/9  
3/9  
4/9  
5/9  
6/9  
7/9  
8/9

Do you have a table to store the values of ConNo ? or can we assume all the possible values of ConNo are available in table: tblChanges?

tblChanges
Date1   ConNo  Value
1/9      001           A
3/9      001           B
7/9      001           C
1/9      002           A
3/9      002           B
2/9      003           A
4/9      003           B
3/9      004           A
8/9      004           B
1/9      005           A
3/9      005           B
7/9      005           c

After compile,
1/9   001   A
2/9   001   A
3/9   001   B
4/9   001   B
5/9   001   B
6/9   001   B
7/9   001   C
8/9   001   C
1/9   002   A
2/9   002   A
3/9   002   B
4/9   002   B
5/9   002   B
6/9   002   B
7/9   002   B
8/9   002   B
2/9   003   A
3/9   003   A
4/9   003   B
5/9   003   B
6/9   003   B
7/9   003   B
8/9   003   B
3/9   004   A
4/9   004   A
5/9   004   A
6/9   004   A
7/9   004   A
8/9   004   B
1/9   005   A
2/9   005   A
3/9   005   B
4/9   005   B
5/9   005   B
6/9   005   B
7/9   005   C
8/9   005   C


Yes...all values of ConNo are available in tblChanges

looks like cannot use union  .. i think need to do a loop as the ConNo has thousands of them..

can we use Query or create a "temporary" table in your database?

so far, this is what I have, just that due to the missing of the records, so some of the Values are empty.

Select a.*, b.Value
from 
(
    Select a.Date, b.ConNo
    from tblDates a,
    (
      select ConNo from tblChanges group by ConNo
    ) b
 ) a
 left join tblChanges b
 on a.Date = b.Date1 and a.ConNo = b.ConNo
Order By a.ConNo, a.Date

Select allOpen in new window

Yes , can make a" temporary " table..always

The compiles data need to start with Starting Date of ConNo as we can see and not generate all the dates..
Will the Ms Access database causes overloading situation as the size grow big?
It will be expecting 2000 conNo needed to generate data from the beginning..
Thks.

Ryan has used a "Cartesian product" of
    every date (in tblDates) MULTIPLIED by
    every ConNo (as a distinct list from tblChanges)

That result is then LEFT JOINED to tblChanges. This technique could be relevant, but what I cannot see in the question so far is; what are we aiming to do?

From this sample:

tblDates
date
1/9  
2/9  
3/9  
4/9  
5/9  
6/9  
7/9  
8/9

tblChanges
Date1   ConNo  Value
1/9      001           A
3/9      001           B
7/9      001           C
1/9      002           A
3/9      002           B
2/9      003           A
4/9      003           B
3/9      004           A
8/9      004           B
1/9      005           A
3/9      005           B
7/9      005           c

What is the expected result?

Splendid article here about finding missing values in a sequence:

Find and Generate Missing Values in an Access Table

For dates, goto page 2 and Listing 1.

 /gustav

@power12345sg

>>Yes , can make a" temporary " table..always
so perhaps we could insert the result of SQL statement I had provided in comment: ID: 41031670 into a table.... and do you want the results to be displayed in a Form or a Report, etc?

For the missing values, yup... perhaps we should do some looping to populate those missing values (sorry, can't think of a better solution at this moment), before display it on to a form/ report. See if what @gustav had provided is useful to complete this part for you?

Do you think this approach make sense to you?

I will be using query wizard to count ConNo by querying the temporary table. That's about it.

>>I will be using query wizard to count ConNo by querying the temporary table. That's about it.
ok.. yes, you can get the (record) count by querying to that table, but what you intend to do after that? What's the purpose for having this generated results?

The purpose is to count for ConNo on a certain date at various location(Value).
Hopefully it is clear now of the purpose.

At Data Entry, they only key in Arriving Containers , not further keying in containers that are not moving.So in order to make sure every containers has to key in once everyday, data has to be generated.

Recently another expert described working on a question as: "it is like looking through a keyhole"

It is the similar here: I DO NOT UNDERSTAND WHAT YOU WANT

"The purpose is to count for ConNo on a certain date at various location(Value)"
"... not further keying in containers that are not moving.So in order to make sure every containers has to key in once everyday, data has to be generated."

Those words are not clear to me.

PLEASE

Provide AN EXAMPLE using data, not words.

Hi, please search my previous attempt..
" query with a loop generate data then append to a temporary table"

https://www.experts-exchange.com/questions/28735487/query-with-a-loop-generate-data-then-append-to-a-temporary-table.html

Regretfully it does not help my understanding much.
==================


Can you consider this? If I have only the following data:

IS THIS THE RESULT YOU EXPECT?


(if that is not the full and correct result, please provide it)

Data Key in by Clerks( In network environment)

tblChanges
Date1   ConNo  Value(---->this is actually location)
1/9      001           A  ( Container 001 key in by clerk from A location)
3/9      001           B  ( Container 001 Key in by clerk from B location)
7/9      001           C  ( container  001 key in by clerk from C location)
1/9      002           A  ( Container 002 key in by clerk from A location)
3/9      002           B  ( Container 002 key in by clerk from B location)
2/9      003           A  ( Container 003 key in by clerk from A location)
4/9      003           B      .
3/9      004           A      .
8/9      004           B      .
1/9      005           A      .
3/9      005           B      .
7/9      005           c       .

I want to get the following results
After compile,
1/9   001   A
2/9   001   A  --->because  001 container hasn't arrive at its destination B, so still  consider as location A
3/9   001   B
4/9   001   B  ------>because  001 container hasn't arrive
                                 at its destination C after by pass location B on 3/9 so still  consider as location B
5/9   001   B  -------->because  001 container hasn't arrive
                                 at its destination C after by pass location B on 3/9 so still  consider as location B
6/9   001   B                .
7/9   001   C                .
8/9   001   C                .
1/9   002   A                .same principle apply throughout
2/9   002   A
3/9   002   B
4/9   002   B
5/9   002   B
6/9   002   B
7/9   002   B
8/9   002   B
2/9   003   A
3/9   003   A
4/9   003   B
5/9   003   B
6/9   003   B
7/9   003   B
8/9   003   B
3/9   004   A
4/9   004   A
5/9   004   A
6/9   004   A
7/9   004   A
8/9   004   B
1/9   005   A
2/9   005   A
3/9   005   B
4/9   005   B
5/9   005   B
6/9   005   B
7/9   005   C
8/9   005   C

So when I search any of the day, my containers will be always at a constant of 5 , I will not miss any containers.I will be able to count the total number of containers at various location by picking a day.......

But how to go about generating those data, I was stuck .
Many  thanks

to make it simple, yes ...that is the result I want.

Good Jobs! Thanks for your advice..

related question: https://www.experts-exchange.com/questions/28746960/a-transalator-that-can-convert-MS-SQL-2014-query-to-MS-Access-SQL-query.html