UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Missing Dates Query, Access 2010    
 
   
Ibrahim789
post Jan 1 2018, 01:17 AM
Post#1



Posts: 4
Joined: 1-January 18



Hi,

Is that possible to make a query to fill with missing dates?
eg:
Table1 (available data)
.
TransDate Balance
17-Aug-17 100,000.00
19-Aug-17 150,000.00
20-Aug-17 200,000.00
23-Aug-17 230,000.00
.............. ...............
.............. ...............
.
I would like to get the result of query as follows:
.
TransDate Balance
17-Aug-17 100,000.00
18-Aug-17 100,000.00
19-Aug-17 150,000.00
20-Aug-17 200,000.00
21-Aug-17 200,000.00
22-Aug-17 200,000.00
23-Aug-17 230,000.00
.............. ...............
.............. ...............
.
Appreciate your support.
.
Ibrahim
This post has been edited by Ibrahim789: Jan 1 2018, 02:06 AM
Go to the top of the page
 
theDBguy
post Jan 1 2018, 02:04 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi Ibrahim,

Welcome to UtterAccess!
welcome2UA.gif

Check the Cartesian Query articles on my blog site. I am hoping they will give you an idea.

Sent from phone...
Go to the top of the page
 
RJD
post Jan 1 2018, 11:49 AM
Post#3


UtterAccess VIP
Posts: 8,964
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

See the attached for another approach. It uses domain functions, so may slow down your processing. But there are other methods if the response speed is not satisfactory. I also converted the dates to YYYYMMDD text in the query since I don't know the date format you are using (US or European).

Note that a table of dates is required in this approach.

A VBA approach can be used to build a separate table of results (avoiding the domain functions). This involves a bit more effort to build, but should run quickly and should not require the extra dates table.

Perhaps this will give you a start on a solution ...

HTH
Joe
Attached File(s)
Attached File  MissingDatesQuery.zip ( 18.36K )Number of downloads: 10
 
Go to the top of the page
 
Ibrahim789
post Jan 2 2018, 02:16 AM
Post#4



Posts: 4
Joined: 1-January 18



Thanks a lot sir.

The Dlookup Function gives the result what I need. But required a separate calendar table and slow processing as you mentioned.
Anyway, the idea of using Dlookup in query was very helpful for me.
Thanks a lot for your valuable time and support.
Best regards,

Ibrahim


Go to the top of the page
 
projecttoday
post Jan 2 2018, 06:42 AM
Post#5


UtterAccess VIP
Posts: 10,181
Joined: 10-February 04
From: South Charleston, WV


The Cartesian query would generate dates more efficiently, though I'm not sure how you would plug in the balance amount.

You could do this with recordset coding. It would process much faster, but the result would be a table, not a query.
Go to the top of the page
 
Ibrahim789
post Jan 2 2018, 01:15 PM
Post#6



Posts: 4
Joined: 1-January 18



Hi,

I have no idea about using Cartesian Queries. Actually, I am trying to create a small db to store and view my investments in mutual funds. Here are a part of my two query results. I would like to get the profit / loss in my investment balance on each NAV dates. I would prefer to get the same by a query without using of coding. I have successfully done this using DLookup function by the help of UA VIP (Joe). However, I would like to try with Cartesian Query for which I need the help of the esteemed UA forums.

QueryTest1
TransDate InvestAmt Balance
06-Dec-17 100000.00 100000.00
07-Dec-17 750000.00 850000.00
13-Dec-17 150000.00 1000000.00
14-Dec-17 50000.00 1050000.00
18-Dec-17 125000.00 1175000.00

QueryTest2
NAVDate NAVChangePCT
06-Dec-17 -0.66
07-Dec-17 1.22
08-Dec-17 0.81
11-Dec-17 0.31
12-Dec-17 -0.58
13-Dec-17 -0.23
14-Dec-17 0.18
15-Dec-17 0.85
18-Dec-17 0.62

Thank you,

Ibrahim
This post has been edited by Ibrahim789: Jan 2 2018, 01:19 PM
Go to the top of the page
 
theDBguy
post Jan 2 2018, 01:20 PM
Post#7


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi Ibrahim,

Re: "I have no idea about using Cartesian Queries."

Did you get a chance to read the articles I suggested earlier?
Go to the top of the page
 
projecttoday
post Jan 2 2018, 01:31 PM
Post#8


UtterAccess VIP
Posts: 10,181
Joined: 10-February 04
From: South Charleston, WV


So you need a "Running Sum" or a "Running Total". Yes, the Dlookup method is one of the simplest, but not very efficient. What we want is to total down the rows. Is a Cartesian approach good for that? I would tend to think not.

Do you really need to show dates on which there was no activity?
Go to the top of the page
 
Ibrahim789
post Jan 2 2018, 01:31 PM
Post#9



Posts: 4
Joined: 1-January 18



Sorry. Can you please provide me with full site address for this articles?
Go to the top of the page
 
theDBguy
post Jan 2 2018, 01:39 PM
Post#10


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi Ibrahim,

Here you go...

Cartesian Query

A Practical Use of a Cartesian Query

Hope it helps...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 07:19 AM