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

Hi Experts ,

I need help in the SQL statement of Ms Access 2013.

It will query by using SQL
statement through searching
records in table(tblChanges) and tblDates(with dates pre put say 1/9, 2/9,3/9...8/9) to generate data.

Let's say today date 8/9

SQL statement that do the query:
SELECT qryRelevantDate.Date, tblChanges.Value, tblChanges.ConNo
FROM (SELECT tblDates.Date, Max(tblChanges.Date1) AS MapDate FROM tblDates INNER JOIN tblChanges ON tblDates.Date >= tblChanges.Date1 GROUP BY tblDates.Date)  AS qryRelevantDate INNER JOIN tblChanges ON qryRelevantDate.MapDate = tblChanges.Date1
ORDER BY tblChanges.Value, tblChanges.ConNo;

Date   ConNo         Value
1/9      001           A
3/9      001           B
7/9      001           C
1/9      002           A
3/9      002           B
              .
              .
              .



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

The results looks great but is only good for first 2 containers.That is 001 and 002 but the rest of containers that I plug in 003 ,004, 005 etc, only partial data is generated.

What can I do to SQL statement in order that all data are generated?

Thanks for the help.
power12345sgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Probably the best solution would be to write a VBA method that will iterate all records and fill the missing dates/containers
0
Ryan ChongCommented:
>>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.
0
power12345sgAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

power12345sgAuthor Commented:
tblDates has only 1 field

date
1/9  
2/9  
3/9  
4/9  
5/9  
6/9  
7/9  
8/9
0
Ryan ChongCommented:
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?
0
power12345sgAuthor Commented:
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
0
power12345sgAuthor Commented:
looks like cannot use union  .. i think need to do a loop as the ConNo has thousands of them..
0
Ryan ChongCommented:
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

Open in new window

0
power12345sgAuthor Commented:
Yes , can make a" temporary " table..always
0
power12345sgAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
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?
0
Gustav BrockCIOCommented:
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
0
Ryan ChongCommented:
@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?
0
power12345sgAuthor Commented:
I will be using query wizard to count ConNo by querying the temporary table. That's about it.
0
Ryan ChongCommented:
>>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?
0
power12345sgAuthor Commented:
The purpose is to count for ConNo on a certain date at various location(Value).
Hopefully it is clear now of the purpose.
0
power12345sgAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
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.
0
power12345sgAuthor Commented:
Hi, please search my previous attempt..
" query with a loop generate data then append to a temporary table"
0
PortletPaulfreelancerCommented:
http://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:

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
IS THIS THE RESULT YOU EXPECT?

Date1   ConNo  Value
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

(if that is not the full and correct result, please provide it)
0
power12345sgAuthor Commented:
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
0
power12345sgAuthor Commented:
to make it simple, yes ...that is the result I want.
0
PortletPaulfreelancerCommented:
>>"to make it simple, yes ...that is the result I want."
Thank you.

>>"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"

I am going to display this using SQL Sever syntax because it's way easier for me to share a small test case with you using sqlfiddle.com. But you will be able to "translate" this into Access I believe

This is the tblChanges I used:
CREATE TABLE tblChanges	
    ([Date1] datetime, [ConNo] int, [Value] varchar(1))
;
    
INSERT INTO tblChanges	
    ([Date1], [ConNo], [Value])
VALUES
    ('2015-09-01 00:00:00', 001, 'A'),
    ('2015-09-03 00:00:00', 001, 'B'),
    ('2015-09-07 00:00:00', 001, 'C'),
    ('2015-09-01 00:00:00', 002, 'A'),
    ('2015-09-03 00:00:00', 002, 'B'),
    ('2015-09-02 00:00:00', 003, 'A'),
    ('2015-09-04 00:00:00', 003, 'B'),
    ('2015-09-03 00:00:00', 004, 'A'),
    ('2015-09-08 00:00:00', 004, 'B'),
    ('2015-09-01 00:00:00', 005, 'A'),
    ('2015-09-03 00:00:00', 005, 'B'),
    ('2015-09-07 00:00:00', 005, 'C')
;

Open in new window

Note there are NO ROWS with a date1 = 2015-09-05

For the test case we need a date parameter; I have called it @what_day

This is then used in this query with the parameter set to 2015-09-05 :
declare @what_day as date = '2015-09-05'

select
      convert(varchar(10),c.Date1,121) as Date1
    , convert(varchar(10), @what_day,121) as Paramdate
    , c.ConNo
    , c.Value as LastRecordedAT
from tblChanges as c
inner join (
        select ConNo, max(Date1) as Date1 from tblChanges
        where Date1 <= @what_day
        group by ConNo
      ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
;

Open in new window

The result is this:
|      Date1 |  Paramdate | ConNo | LastRecordedAT |
|------------|------------|-------|----------------|
| 2015-09-03 | 2015-09-05 |     5 |              B |
| 2015-09-03 | 2015-09-05 |     4 |              A |
| 2015-09-04 | 2015-09-05 |     3 |              B |
| 2015-09-03 | 2015-09-05 |     2 |              B |
| 2015-09-03 | 2015-09-05 |     1 |              B |

Open in new window

In SQL Sever the equivalent of NOW()  is GETDATE(), so this time we set the date parameter to getdate()
|      Date1 |  Paramdate | ConNo | LastRecordedAT |
|------------|------------|-------|----------------|
| 2015-09-07 | 2015-10-10 |     5 |              C |
| 2015-09-08 | 2015-10-10 |     4 |              B |
| 2015-09-04 | 2015-10-10 |     3 |              B |
| 2015-09-03 | 2015-10-10 |     2 |              B |
| 2015-09-07 | 2015-10-10 |     1 |              C |
        

Open in new window

Modify the query slightly, to this:
declare @what_day as date = getdate()

select
      c.Value as LastRecordedAT
    , count(*) as count_of
from tblChanges as c
inner join (
        select ConNo, max(Date1) as Date1 from tblChanges
        where Date1 <= @what_day
        group by ConNo
      ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
group by
      c.Value
;

Open in new window

and the result is a count of containers by location:
| LastRecordedAT | count_of |
|----------------|----------|
|              B |        3 |
|              C |        2 |

Open in new window

The purpose of these test cases it to show that you can "locate" your containers, at any date from today or in the past simply by using tblChanges (tblDates does not even exist in my sample db)

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

see all this running as a demo here: http://sqlfiddle.com/#!6/fbbe3/2
details:
**MS SQL Server 2014 Schema Setup**:

    CREATE TABLE tblChanges	
        ([Date1] datetime, [ConNo] int, [Value] varchar(1))
    ;
        
    INSERT INTO tblChanges	
        ([Date1], [ConNo], [Value])
    VALUES
        ('2015-09-01 00:00:00', 001, 'A'),
        ('2015-09-03 00:00:00', 001, 'B'),
        ('2015-09-07 00:00:00', 001, 'C'),
        ('2015-09-01 00:00:00', 002, 'A'),
        ('2015-09-03 00:00:00', 002, 'B'),
        ('2015-09-02 00:00:00', 003, 'A'),
        ('2015-09-04 00:00:00', 003, 'B'),
        ('2015-09-03 00:00:00', 004, 'A'),
        ('2015-09-08 00:00:00', 004, 'B'),
        ('2015-09-01 00:00:00', 005, 'A'),
        ('2015-09-03 00:00:00', 005, 'B'),
        ('2015-09-07 00:00:00', 005, 'C')
    ;
    
**Query 1**:

    select count(*) as NumRows
    from tblChanges as c
    where Date1 = '2015-09-05'
    

**[Results][2]**:
    | NumRows |
    |---------|
    |       0 |
**Query 2**:

    
    
    declare @what_day as date = '2015-09-05'
    
    select
          convert(varchar(10),c.Date1,121) as Date1
        , convert(varchar(10), @what_day,121) as Paramdate
        , c.ConNo
        , c.Value as LastRecordedAT
    from tblChanges as c
    inner join (
            select ConNo, max(Date1) as Date1 from tblChanges
            where Date1 <= @what_day
            group by ConNo
          ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
    

**[Results][3]**:
    |      Date1 |  Paramdate | ConNo | LastRecordedAT |
    |------------|------------|-------|----------------|
    | 2015-09-03 | 2015-09-05 |     5 |              B |
    | 2015-09-03 | 2015-09-05 |     4 |              A |
    | 2015-09-04 | 2015-09-05 |     3 |              B |
    | 2015-09-03 | 2015-09-05 |     2 |              B |
    | 2015-09-03 | 2015-09-05 |     1 |              B |
**Query 3**:

    
    
    declare @what_day as date = getdate()
    
    select
          convert(varchar(10),c.Date1,121) as Date1
        , convert(varchar(10), @what_day,121) as Paramdate
        , c.ConNo
        , c.Value as LastRecordedAT
    from tblChanges as c
    inner join (
            select ConNo, max(Date1) as Date1 from tblChanges
            where Date1 <= @what_day
            group by ConNo
          ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
    

**[Results][4]**:
    |      Date1 |  Paramdate | ConNo | LastRecordedAT |
    |------------|------------|-------|----------------|
    | 2015-09-07 | 2015-10-10 |     5 |              C |
    | 2015-09-08 | 2015-10-10 |     4 |              B |
    | 2015-09-04 | 2015-10-10 |     3 |              B |
    | 2015-09-03 | 2015-10-10 |     2 |              B |
    | 2015-09-07 | 2015-10-10 |     1 |              C |
**Query 4**:

    
    
    
    declare @what_day as date = getdate()
    
    select
          c.Value as LastRecordedAT
        , count(*) as count_of
    from tblChanges as c
    inner join (
            select ConNo, max(Date1) as Date1 from tblChanges
            where Date1 <= @what_day
            group by ConNo
          ) as md on c.ConNo = md.ConNo and c.Date1 = md.Date1
    group by
          c.Value
    

**[Results][5]**:
    | LastRecordedAT | count_of |
    |----------------|----------|
    |              B |        3 |
    |              C |        2 |

  [1]: http://sqlfiddle.com/#!6/fbbe3/2
  [2]: http://sqlfiddle.com/#!6/fbbe3/2/0
  [3]: http://sqlfiddle.com/#!6/fbbe3/2/1
  [4]: http://sqlfiddle.com/#!6/fbbe3/2/2
  [5]: http://sqlfiddle.com/#!6/fbbe3/2/3

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
power12345sgAuthor Commented:
Good Jobs! Thanks for your advice..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.