Solved

Access 2003 - A count Query/Report

Posted on 2007-11-21
12
1,296 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create macro from runcode 30 24
VBA SQL statement - 2 "OR"s and 1 "And" 4 27
trying to catch ODBC error when database opens 2 24
aggregate query? 3 29
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

786 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