Solved

Access 2003 - A count Query/Report

Posted on 2007-11-21
12
1,274 Views
Last Modified: 2013-11-28
Hi Experts
I have got a Contact/Sales DB, with tables, forms, queries and reports already configured and setup.
I would like to set up either a query, which I can in return use in reports, or a report directly which will count how many sales were made by a sales agent.

The fields I thought that could be used are the following:
Franchise(Franchise’s Table)
Sub Franchise(Sub Franchise Table)
Sales Agent(Sales Agent Table)
GR number (reference number to the contact)(Contacts Table)
First Name(Contacts Table)
Last Name(Contacts Table)
Sale Date(Contacts Table)

What I would firstly like to know, what am I going to use here, a query or a can one use the report function straight to get how many sales one Sales Agent made.
For example. John Smith made 5 sales for the month of October. I would like to see the total sales counted for me in a query or report that just shows the total figure.

Any help would be greatly appreciated.
Kind regards
mustekkzn
0
Comment
Question by:mustekkzn
  • 7
  • 5
12 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20329765
You will need to do a COUNT query, something like:

SELECT
      F.Franchise,
      SF.SubFranchise,
      SA.SalesAgent,
      COUNT(*) AS OrderCount
FROM
      FranchiseTable F INNER JOIN SubFranchiseTable SF ON SF.Franchise=F.Franchise
      INNER JOIN SalesAgentTable SA ON SA.SubFranchise=SF.SubFranchise
      INNER JOIN SalesTable S ON S.SalesAgent=SA.SalesAgent
WHERE
      S.SalesDate BETWEEN #1/1/2007# AND #12/31/2007#
GROUP BY
      F.Franchise,
      SF.SubFranchise,
      SA.SalesAgent
0
 

Author Comment

by:mustekkzn
ID: 20334328
Hi Digital thoughts.
I am sorry I am only getting back to you now. I thought to give you a quick update.
Upon entering the above query(which I have to say thank you for, it is much appreciated)
the following Syntax error pops up:
Syntax error (missing operater) in query expression SF.Franchise=F.Franchise
      INNER JOIN SalesAgentTable SA ON SA.SubFranchise=SF.SubFranchise
      INNER JOIN SalesTable S ON S.SalesAgent=SA.SalesAgent
It gives me the options of clicking OK or help.
Other than that I cant really tell you if the query is working or not. Do you need any more info from me?
Thanks so much.
KInd regards
mustekkzn
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20344715
Give this one a try, I forget that Access doesn't like INNER JOINs without parentheses


SELECT
      F.Franchise,
      SF.SubFranchise,
      SA.SalesAgent,
      COUNT(*) AS OrderCount
FROM
      ((FranchiseTable F INNER JOIN SubFranchiseTable SF ON F.Franchise = SF.Franchise)
      INNER JOIN SalesAgentTable SA ON SF.SubFranchise = SA.SubFranchise)
      INNER JOIN SalesTable S ON SA.SalesAgent = S.SalesAgent
WHERE
      S.SalesDate BETWEEN #1/1/2007# AND #12/31/2007#
GROUP BY
      F.Franchise,
      SF.SubFranchise,
      SA.SalesAgent
0
 

Author Comment

by:mustekkzn
ID: 20344955
Hi Digital Thoughts
I just want to say thanks so much for the above post.
Looking back to my first post, I don’t think I gave you enough information to work with in the first place.
Regarding correct tables and columns names.
If you don’t mind, I am going to try and give you the correct fields to work with below:
Tables:                                    Columns in Table:
Franhise                                  FranchiseID, FranchiseName
SubFranchise                          ID, SubFranchiseName, FranchiseName
SalesAgent                              SalesAgentID, SalesAgent, FranchiseID, SubFranchseID
Contacts                                  FirstName,LastName,SalesDate, FranchiseName, SubFranchise,
                                                Sales Agent (there is a space,I know it is not how it is suppose
                                                 to be, my bad) Also, there are more fields in this table, I just gave
                                                 you the ones I thought are necessary for this query.

I just want to say that I am sorry for messing you around like this; I should have done this from the begging.

Regards
mustekkzn
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20350290
Not a problem, this should give you the total sales:

SELECT
      F.FranchiseName,
      SF.SubFranchiseName,
      SA.SalesAgent,
      COUNT(*) AS OrderCount
FROM
      ((Franchise F INNER JOIN SubFranchise SF ON F.FranchiseName = SF.FranchiseName)
      INNER JOIN SalesAgent SA ON SF.ID = SA.SubFranchseID)
      INNER JOIN Contacts C ON SA.SalesAgent = C.[Sales Agent]
WHERE
      C.SalesDate BETWEEN #1/1/2007# AND #12/31/2007#
GROUP BY
      F.FranchiseName,
      SF.SubFranchiseName,
      SA.SalesAgent


You also listed the  First Name and Last Name fields in the Contacts table, but in order to count sales you really can't display those, unless they are always the same values.
0
 

Author Comment

by:mustekkzn
ID: 20350475
Hi Digital Thoughts
It is looking al ot better, but I am still getting an error.
The error says: Type mismatch in expression
If I click on help the following comes up:
A JOIN expression is attempting to join two tables on fields of incompatible data types. For example, you will get this error if you attempt to join a Memo field with a Text field.

Below I am giving you the tables, columns in the tables and the data types for the columns

Tables:                                    Columns in Table:                                   Data Type:
Franhise                                  FranchiseID,                                           AutoNumber
                                                FranchiseName                                      Text
SubFranchise                          ID,                                                          AutoNumber
                                                SubFranchiseName,                              Text
                                                FranchiseID                                           Number
SalesAgent                             SalesAgentID,                                        AutoNumber
                                                SalesAgent,                                           Text
                                                FranchiseID,                                          Number
                                                SubFranchseID                                     Number
Contacts                                  FirstName,                                             Text
                                                LastName,                                              Text
                                                SalesDate,                                             Date/Time
                                                FranchiseName,                                    Number
                                                SubFranchise,                                       Number
                                                Sales Agent                                           Number

Just want to say thank you so much for helping out. Without your help, I dont think  I would ever get this far.
Regards
mustekkzn
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 8

Accepted Solution

by:
digital_thoughts earned 500 total points
ID: 20351162
Ok, give this one a try:

SELECT
      F.FranchiseName,
      SF.SubFranchiseName,
      SA.SalesAgent,
      COUNT(*) AS OrderCount
FROM
      ((Franchise F INNER JOIN SubFranchise SF ON F.FranchiseID = SF.FranchiseID)
      INNER JOIN SalesAgent SA ON SA.SubFranchseID = SF.ID AND SA.FranchiseID = SF.FranchiseID)
      INNER JOIN Contacts C ON SA.SalesAgentID = C.[Sales Agent]
WHERE
      C.SalesDate BETWEEN #1/1/2007# AND #12/31/2007#
GROUP BY
      F.FranchiseName,
      SF.SubFranchiseName,
      SA.SalesAgent

And you are welcome for the assistance.
0
 

Author Comment

by:mustekkzn
ID: 20352082
Hi Digital thoughts
It is working 100%. Its perfect!!
Thanks so much for your patience with me.

I just have one more question for you regarding the Where part in the query.
How easy would it be to set it up so the Where part, to get its date criteria from a date box? (or pop up calendar) This is already created and setup. I just basically need to add this query as a report and get the date criteria field to refer to my drop down boxes and not to the Where part in the query. The user must be able to stipulate this them selves.
All my current Reports are done in code, not in Queries. So I am not to sure in how to get the date retire field to "kick" in this case.  
Before I carry on, just give me an indication that you could assist me. If you can I will post another question.

Once again, thanks so much for your assists, I would not have been able to do this by myself.
Kind regards
mustekkzn
0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20352281
You can have the query reference a value from a form, just use something like the below:

C.SalesDate BETWEEN Forms!FormName!FieldName1 AND Forms!FormName!FieldName2
0
 

Author Comment

by:mustekkzn
ID: 20357931
Hi digital thoughts.
I entered the following:
WHERE C.SalesDate BETWEEN Forms!View Reports!cboStartDate AND Forms!View Reports!cboEndDate
and it is giving me a syntax error: Missing operator in query expression error.
it highlights the View Reports part.
How can I set this up so I can give you points for this. Must I just post another question for you?
Kind regards
mustekkzn
0
 

Author Comment

by:mustekkzn
ID: 20358043
HI digital thoughts.
Forget about above post, I have manage to get it to work like it should
Once again, thanks so much for all your assistants.
Kind regards
mustekkzn
0
 

Author Comment

by:mustekkzn
ID: 20360831
Hi digital thoughts
I just thought I would let you know that I have added another question, very simular as above.
May be you can assist me.
The post link is below. See you there!
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_22985879.html
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

758 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

21 Experts available now in Live!

Get 1:1 Help Now