R
Rod
How can I write the result of dcount using tblCandidates to tblStatistics?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
O
Ofer
Are you trying to update existing record, or insert a new record?
Can you provide some more details of what you are trying to do.
Can you provide some more details of what you are trying to do.
R
Rod
I am accumulating data in tblCandidates. In the tbl I have a field Called_On
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
O
Ofer
In that case why do you want to save the data in a new table, it mean that
you will need to maintain it every day, if not more often.
Create a query to return all the data you need and display the count per
day, every time you will run the query, the data will be current
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
That will list the count of calls per day
you will need to maintain it every day, if not more often.
Create a query to return all the data you need and display the count per
day, every time you will run the query, the data will be current
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
That will list the count of calls per day
R
Rod
Place this query in the query used behind the form used when calls are made?
Ofer said:In that case why do you want to save the data in a new table, it mean that
you will need to maintain it every day, if not more often.
Create a query to return all the data you need and display the count per
day, every time you will run the query, the data will be current
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
That will list the count of calls per day
Rod said:I am accumulating data in tblCandidates. In the tbl I have a field Called_On
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
R
Rod
Sorry, I have not created a query which looks like this; how would this look
if I just added your idea to my qCalling? Would I past this in the
eexpression builder?
if I just added your idea to my qCalling? Would I past this in the
eexpression builder?
Ofer said:In that case why do you want to save the data in a new table, it mean that
you will need to maintain it every day, if not more often.
Create a query to return all the data you need and display the count per
day, every time you will run the query, the data will be current
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
That will list the count of calls per day
Rod said:I am accumulating data in tblCandidates. In the tbl I have a field Called_On
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
O
Ofer
You can use the SQL I provided
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
TableName = The name of the table assuming it tblCandidates
DateField = the name of the field that contain the date in table
tblCandidates
It should work
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
TableName = The name of the table assuming it tblCandidates
DateField = the name of the field that contain the date in table
tblCandidates
It should work
Rod said:Sorry, I have not created a query which looks like this; how would this look
if I just added your idea to my qCalling? Would I past this in the
eexpression builder?
Ofer said:In that case why do you want to save the data in a new table, it mean that
you will need to maintain it every day, if not more often.
Create a query to return all the data you need and display the count per
day, every time you will run the query, the data will be current
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
That will list the count of calls per day
Rod said:I am accumulating data in tblCandidates. In the tbl I have a field Called_On
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
:
Are you trying to update existing record, or insert a new record?
Can you provide some more details of what you are trying to do.
:
How can I write the result of dcount using tblCandidates to tblStatistics?
R
Rod
AT this point this question probably does not belong in this forum, but
perhaps you will bare with me. When in the Select Query screen of qCalling I
went to an empty field and entered the following:
Field:
Expr1: [Count_Of_Calls] {the system modified my entry of Count_Of_Calls to
this}
Table:
tblCandidates
Criteria:
Count ([DateField])
I receive an error:
Cannot have aggregate function in WHERE clause (tblCandidates.DNC=FALSE And
tblCandidates.CANDIDATE Like [Beginning of Candidate's Name] & "*" And
tblCandidates.INPUT_NUMBER like "*" & [Phone NUmber] & "*" & [Phone NUmber] &
"*" And tblCandidates.CALL_RESULTS like [Call's Result] & "*" And
tblCandidates.Cou). <- this is where it cuts off.
It looks to me like it does not like the user inputs, which are used for
search in the tbl, in conjunction with this query. thoughts?
perhaps you will bare with me. When in the Select Query screen of qCalling I
went to an empty field and entered the following:
Field:
Expr1: [Count_Of_Calls] {the system modified my entry of Count_Of_Calls to
this}
Table:
tblCandidates
Criteria:
Count ([DateField])
I receive an error:
Cannot have aggregate function in WHERE clause (tblCandidates.DNC=FALSE And
tblCandidates.CANDIDATE Like [Beginning of Candidate's Name] & "*" And
tblCandidates.INPUT_NUMBER like "*" & [Phone NUmber] & "*" & [Phone NUmber] &
"*" And tblCandidates.CALL_RESULTS like [Call's Result] & "*" And
tblCandidates.Cou). <- this is where it cuts off.
It looks to me like it does not like the user inputs, which are used for
search in the tbl, in conjunction with this query. thoughts?
Ofer said:You can use the SQL I provided
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
TableName = The name of the table assuming it tblCandidates
DateField = the name of the field that contain the date in table
tblCandidates
It should work
Rod said:Sorry, I have not created a query which looks like this; how would this look
if I just added your idea to my qCalling? Would I past this in the
eexpression builder?
Ofer said:In that case why do you want to save the data in a new table, it mean that
you will need to maintain it every day, if not more often.
Create a query to return all the data you need and display the count per
day, every time you will run the query, the data will be current
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
That will list the count of calls per day
:
I am accumulating data in tblCandidates. In the tbl I have a field Called_On
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
:
Are you trying to update existing record, or insert a new record?
Can you provide some more details of what you are trying to do.
:
How can I write the result of dcount using tblCandidates to tblStatistics?
D
David C. Holley
So you're building a summary table?
I am accumulating data in tblCandidates. In the tbl I have a field Called_On
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
:
R
Rod
Right, but the solutionthat was supplied is something I am unfamiliar with
implementing. It sounds like "Ofer" is close, but I do not know how/where to
apply his solution. I think my previous comment is close, but I am getting
an error when I try to apply it which is likely my error.
implementing. It sounds like "Ofer" is close, but I do not know how/where to
apply his solution. I think my previous comment is close, but I am getting
an error when I try to apply it which is likely my error.
P
PC Datasheet
You shouldn't be doing this. Statistics are calculated data and should be
calculated on the fly when you need them. The reason for this is that say a
user goes back and edits the data in TblCandidates for whatever reason, your
statistics in TblStatistics are then wrong unless the user goes back abd
changes that data also.
calculated on the fly when you need them. The reason for this is that say a
user goes back and edits the data in TblCandidates for whatever reason, your
statistics in TblStatistics are then wrong unless the user goes back abd
changes that data also.
O
Ofer
I'm still here, just working in a different time frame, sorry.
The Sql I provided you with, first open a new query in design view, in the
menu bar select View > SQL , delete what you have there already and paste the
sql I gave you with the changes of names to suit your table, try it first to
see if the query works, if so then you can base the form/report on that query.
The Sql I provided you with, first open a new query in design view, in the
menu bar select View > SQL , delete what you have there already and paste the
sql I gave you with the changes of names to suit your table, try it first to
see if the query works, if so then you can base the form/report on that query.
R
Rod
Isn't that what "Ofer" is suggesting? Help me out... doesn't his solution do
what you suggested instead of my orginal thought of a statistics table? His
solution looks good to me, but i am new at access. I agree with your logic;
what are your thoughts, other than what was posted, on my approach?
what you suggested instead of my orginal thought of a statistics table? His
solution looks good to me, but i am new at access. I agree with your logic;
what are your thoughts, other than what was posted, on my approach?
P
PC Datasheet
Yes, Ofer is correct!
Create a new query based on TblCandidates. Pull down the Called_On field
into the first field of the query design grid. Type the following inti the
second field of the query grid:
NumOfCalls:[Called_On]
Click on the Sigma (looks like a capital E) buttom on the toolbar at the top
of the screen. Under NumOfCalls change Group By to Count. When you run this
query you will get a list of distinct dates calls were made and the number
of calls made each date. You can now use this query as the basis of a form
or report to display the results of the query.
Create a new query based on TblCandidates. Pull down the Called_On field
into the first field of the query design grid. Type the following inti the
second field of the query grid:
NumOfCalls:[Called_On]
Click on the Sigma (looks like a capital E) buttom on the toolbar at the top
of the screen. Under NumOfCalls change Group By to Count. When you run this
query you will get a list of distinct dates calls were made and the number
of calls made each date. You can now use this query as the basis of a form
or report to display the results of the query.
R
Rod
Although you answered my original questions, my hope was I would be able to
modify the solution to pull out the following for the given day:
1) Total Calls (your solution takes care of this)
2) Total messages_left (needs to be something like: "total messages_left
where date is the same")
3) Total Interviews_Set (needs to be something like: "total Interviews_Set
where date is the same")
Any thoughts on this?
modify the solution to pull out the following for the given day:
1) Total Calls (your solution takes care of this)
2) Total messages_left (needs to be something like: "total messages_left
where date is the same")
3) Total Interviews_Set (needs to be something like: "total Interviews_Set
where date is the same")
Any thoughts on this?
Ofer said:You can use the SQL I provided
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
TableName = The name of the table assuming it tblCandidates
DateField = the name of the field that contain the date in table
tblCandidates
It should work
Rod said:Sorry, I have not created a query which looks like this; how would this look
if I just added your idea to my qCalling? Would I past this in the
eexpression builder?
Ofer said:In that case why do you want to save the data in a new table, it mean that
you will need to maintain it every day, if not more often.
Create a query to return all the data you need and display the count per
day, every time you will run the query, the data will be current
Select [DateField], Count ([DateField]) as Count_Of_Calls
From TableName
Group BY [DateField]
That will list the count of calls per day
:
I am accumulating data in tblCandidates. In the tbl I have a field Called_On
to log the date when the cadidate was called. What I want to do is
eventually have statistics on how many calls were made every day (except
weekends) stored in a separate tble named tblStatitics and field named dates.
So a specific entry in tblstatistics should look something like:
Date Calls
09/02/2005 100
:
Are you trying to update existing record, or insert a new record?
Can you provide some more details of what you are trying to do.
:
How can I write the result of dcount using tblCandidates to tblStatistics?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.