Solved

domain function question

Posted on 2014-02-19
4
285 Views
Last Modified: 2014-02-20
Hi all,

I need to know what is the most efficient way to get the following in vba

count of distinct dates for certain criteria.

for example, how many distinct dates are scheduled for this week.

two schedule records for Sunday are considered 1, while Sun, Mon, Tue are counted as 3.

I know I can do it in a saved distinct query and use DCount of that query, however wonder if that is the most efficient way of accomplishing it, as i am dealing with a very large table.
0
Comment
Question by:bfuchs
  • 2
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39871944
start with this

Select Count(A.Dates) as CountOfSchedules
from
(select TableX.Dates From TableX
  Group By TableX.Dates
) As A
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39871950
hi,
and how do i get this results into a variable?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39871971
save the above query as QCount

open it as recordset

dim rs as dao.recordset, xCount as integer
set rs=currentdb.openrecordset("QCount")

xCount=rs!CountOfSchedules
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 39874778
Thanks allot Ray, that worked, however just wonder if the following approach would perform faster?
create a SQL function that accepts a int variable and returns a int, then have this function being called from the app.
p.s. its a ms access front end application linked to SQL Server back end.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now