Reports :: Show Salespeople With 0 Sales Within Date Filter?

Jul 6, 2013

I want a very basic report that shows:* all field reps (sales people),

* their quota in terms of new customer registrations

* their actual new customer registrations for a particular month

The report should include field reps with 0 sales. The query I've created works fine as long as there's no date filter. But adding <1/1/2013 removed all field reps with 0 sales. I want to make sure the filter concept was working before getting into date ranges.

Code:
SELECT DISTINCTROW tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota, Count(tbl_customers.dateRegistration) AS NewRegistrations
FROM tbl_ksFieldRep LEFT JOIN tbl_customers ON tbl_ksFieldRep.[salesRepName] = tbl_customers.[ksFieldRep]
WHERE (((tbl_customers.dateRegistration)<#1/1/2013#))
GROUP BY tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota;

View Replies


ADVERTISEMENT

Queries :: Product Per Date - Show Days With 0 Sales

Aug 11, 2014

I'm trying to build a query that adds up the total sales for a given product per date. The problem I'm running into is some products have $0 sales on a given day and don't appear in the table I'm querying. How would I get the query to add a record for that day for the given product with 0 in the sales field?

To give more details, I currently have one table showing the sales data. It has a record for every sale that includes the product of the sale, the amount, and the date. In the query, I'm summing the sales for a given day and product. Then I plan to export to Excel where I will run additional analysis.

If there are no sales for the product in a day, I'd like it to list the date and product with a $0 in the sales column.

View 5 Replies View Related

General :: Filter Data Sheet To Show Records With No Date

Mar 20, 2014

I am having trouble with a datasheet its ran from a macro button using BrowseTo command.

I am having trouble with the where condition; I would like to show records where the [FittingDate] is blank...

I have tried isnull() and [FittingDate]=""

View 1 Replies View Related

Filter Query - Report To Only Show Records With Most Recent Date?

Feb 26, 2014

I am having trouble getting a query or report to show only the most recent data.

We have salesmen that use a handheld data collector scanners to count inventory in stores. The scanner data is imported to a Access table. Each record line is one scanned item. I have a query with totals that counts the records and gives me a total count of each item at the store on that date.

I then need to filter the data to only show the most recent date. Using Max Date I get the most recent date but the count fields are showing totals for all dates. I am also getting the unique item from the earlier date in this query which I do not want.

Here is my data table: Inventory Scans from stores.

Scan Date
Item Scanned
location

1/1/2014
item123
Store ABC

1/1/2014
item123
Store ABC

......

Here is my Query with Totals that counts the item records:

Scan Date
Item Scanned
location
(Item Scanned) count

1/1/2014
item123
Store ABC
2

1/1/2014
item 456
Store ABC
3

1/1/2014
item 789
Store ABC
1

2/1/2014
item123
Store ABC
2

2/1/2014
item 456
Store ABC
1

This is what I am trying to get - only the most recent date of counted items:

Scan Date
Item Scanned
location
(Item Scanned) count

2/1/2014
item123
Store ABC
2

2/1/2014
item 456
Store ABC
1

View 5 Replies View Related

Queries :: Filter Using Date Range But Show All Records If Text Box Null

Jan 13, 2014

I m trying to make form which filters my records and generates a report..here's where I am

Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"

but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code

Code:
Like "*" & [Forms]![Form1]![Text6] & "*"

the code is in report record source

View 9 Replies View Related

Forms :: DatePart Function To Filter Out Sales Tax By Quarter

Apr 26, 2013

Was wondering if there is a way to incorporate the DatePart function in the below statement to filter out sales tax by quarter? I have two drop down boxes that filter the year and the state but the below only totals the tax for the year and state. I am trying to add 4 text boxes to show the quarterly break down of sales tax.

The below text box is in a form pulling the data from a query.

Text Box

=DSum("[SalesTaxCharged]","[Sales Tax Calculation Qry]","Year([Order Date])=[SelectedYear] And
[StateProv]=[SelectedState] And Not [Tax Exempt]")

View 6 Replies View Related

Reports :: Error Depends On Date Filter?

Oct 8, 2014

I have a cross-tab query that is filtered on user-defined date range using a where condition of:>[Forms]![Reports_Menu]![txtFilterDateFrom] AND <[Forms]![Reports_Menu]![txtFilterDateTo]

the Query functions perfectly. The report based on that query, however, only functions with a DateFrom that is before 1/28/14 and a DateTo that is after 2/27/14. If I enter a date in either field that is outside of those ranges, I get the following error:The Microsoft Access database engine does not recognize " as a valid field name or expression.I have defined both of my Query Parameters as Date/Time in the Xtab query design.

View 7 Replies View Related

Reports :: Date Range Filter For Report

May 27, 2015

The data the query pulls is employee name, course ID, course name and course completed date. I have added criteria in the query that asks for the Course ID to filter on a given course and a dynamic field in the query AnnualReqDate: DateAdd("d",+365,[TrainingCourseCompleted]). What this returns is a list of employees that have completed course X the date they completed and the date (12 months) when the course is due. This works great, have created a report that reflects this very well.

The issue comes in when I try to add the ability to filter by date range on the AnnualReqDate dynamic field. the AnnualReqDate dynamic field does not exist until the query is run the Between [Start Date] And [End Date] criteria add to the AnnualReqDate dynamic field wont work.The query fires off but returns zero records.

View 14 Replies View Related

Reports :: Show The Balance At Transaction Date?

Jul 21, 2013

I created a report to show stock level and transactions. there are date, transaction, quantity and balance fields. the balance field show the total balance of the item at the moment. what I need is to show the balance at the transaction date.

View 7 Replies View Related

Reports :: Formatting Date To Show Day Of The Week

Feb 10, 2015

I'm setting up a query which will eventually run as a report to show sales within a specific date period and so on.

In my table "tblJobs" I have a field named "JobInputDate" - this will act as my date source, everything else included within the query works fine, i.e. costs etc, etc.

The format of the date field is Short Date and the default value is the date the entry was made to the table, this obviously uses the =Date() expressing to generate the date.

Now, when I come to building my query I want the dates to be shown as a day of the week, so that when I eventually build my report, I can group them by day.

I've looked through the various threads on here which all point to using the Format([YourDateField],"dddd") format. However when I run the query I get the "Data mismatch" error.

View 3 Replies View Related

Modules & VBA :: Chart To Show Total Sales Ordered By Customers - Graph Method?

Sep 10, 2013

I've created an access chart to show total sales ordered by customers.

I'm using a form with 5 comboboxes to select 5 particular customers from a customer table and pass this information into a query.

This query is then used on a report to create the graph.

Finally there is a button on my form that prints this report.

My problem is that the chart displays the customers in alphabetical order and I would like to order the customers numerically based on total sales value.

If I change my query so that the total sales order by is ascending then when I run the query from the report I am asked to enter a parameter value for the total sales field. Entering nothing and pressing ok simply returns the same graph I would have got had I not changed the order option at all.

*The customer filed in the report is set to group by and my totals field is set to sum - but I need both of these set to produce the graph.

View 3 Replies View Related

Subtract Sales From Stock With Oldest Date First And Then Move To Next Date?

Sep 16, 2014

Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.

So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.

So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.

I would like also to include the logic that if Item is out of date it would move to the next sell by date.

So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10.

For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.

View 2 Replies View Related

Reports :: Show Date Range In Report From Query

Apr 18, 2013

I have a report, based on a query, where the field name is "Date of Work". The criteria in this query field is >=[Start Date] And <=[End Date], which works fine. However I need to have the start and end dates to show in the resulting report header based on the query.

View 1 Replies View Related

Forms :: How To Filter Reports By Date Types In A Drop Down Or Combo Box

Jun 26, 2013

filter reports by specific date types in a drop down or combo box I would like the dates to be

Today
Tomorrow
The next 3 days and the next 7 days

i don't want a date picker or between dates I have tried searching but cannot seem to find an answer They are for my engineers, they want to know what callouts they have today or tomorrow or the next 3 days or this week.

View 3 Replies View Related

How Can I Create A Sales Form That Can Update Both The Product And Sales Tables

Oct 11, 2007

Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.

View 1 Replies View Related

Reports :: Making Sales Sheet On Access Using Existing Table

Aug 29, 2013

I have a table which has the fields: Agentname,SaleDate,Branch,Sales, Percentage, Comission, Corrections, rent and Total

Total = Sales-comission+Corrections+rent

This table has the name of the agent that made the sale, the branch(location) where he made it, the date, the commission of how much he gets from the sale, Corrections which is various correction that need to be made like a refund and or bonuses, Rent which he pays and the total.

what i want to do is to get access to make me a sheet( a sales invoice) where i can see the sales for a specific agent for a specific branch. agents can work at multiple branches.

so lets say agent A on branch A sold 400 on 1/8, 300 on 2/8 and 500 on 5/8 i want to get a list of all the days from 1/8 till 31/8 and the records of 1/8, 2/8 and 5/8 automatically attached to the correct dates and the rest of the dates should have a value of 0.

View 4 Replies View Related

Queries :: Sum Of Sales - TOP 5 Agents By State Over Given Date Range

Sep 5, 2013

I want to get the sum of sales of the top 5 best agents by state.

I have been able to do the following, however, this only gives me the state of "vic". I want it to show all states.

SELECT TOP 5 filedata.Debtor, Debtors.FullName, Sum(filedata.NetTotal) AS SumOfNetTotal, Debtors.State, Count(filedata.BorQ) AS CountOfBorQ
FROM filedata INNER JOIN Debtors ON filedata.Debtor = Debtors.Code
WHERE (((filedata.BorQ)="b") AND ((filedata.DepDate) Between [Forms]![F-financemenu].[startdate] And [Forms]![F-financemenu].[enddate]))
GROUP BY filedata.Debtor, Debtors.FullName, Debtors.State
HAVING (((Debtors.State)="vic"))
ORDER BY Sum(filedata.NetTotal) DESC;

which returns

DebtorFullNameSumOfNetTotalStateCountOfBorQ1429ABC company147150VIC11360XYZ Company107204.55VIC32534MNO organisation68799.86VIC31168PQR business58934.67VIC31326FGH industries32480VIC2

(count of BorQ) is the number of sales

I have tried using UNION ALL and changing the State but that only caused syntax errors.

View 2 Replies View Related

Reports :: Attendance Database - Show Time In And Time Out For Specific Date

Apr 3, 2014

I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.

Please see the attached screenshot db from attendance machine.

an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.

View 4 Replies View Related

Modules & VBA :: Filter Records - Adding Unbound Date Listbox To Filter String

Feb 10, 2014

I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.

I'm trying to use Allen Browne's Search Criteria:

with another snippete of code I found here:

Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.

[Code]....

It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.

Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.

View 2 Replies View Related

Reports :: Print Out Single Page Reports (or Forms) To Show Detail From Several Tables And Queries

Apr 21, 2014

I have a database of high-school football players, and I am looking to print out single page reports (or forms) that will show detail from several tables and queries. This will act as their resume when they visit schools on recruiting visits. The reason for needing query items, is that I have developed queries that return the most up to date height, weight, 40 time etc., and that single most up to date number is what should print, not the entire table. When I try to build a report it will let me bring in multiple tables, but not queries.

View 2 Replies View Related

Show Filter In Report From QBF

Dec 13, 2004

Hi there,

I have a QBF, and when user inputs the search criteria and clicks on print button, a report prints out based on that search criteria. I would like that report to include the search criteria as well. How can I do that?

I am not so good in Access, so please be as much specific as one can get, when suggesting a solution.

Thank you so much.

View 2 Replies View Related

How To Get Second Filter To Show All Values

Aug 1, 2014

How do I get the second filter to show all the values and allow me to check which ones I would like to select? I cant seem to recall how to do this. I remember having to do it a while back.

View 8 Replies View Related

Reports :: Getting Entire Reports To Show On A Tab Control

Apr 24, 2015

I have tab controls, each of which holds a report. If the report is longer than the tab control the report is stuck there. no slide bars, no overflow. I have set scroll bars but they dont seem to take or work. I need to find a way to add verticle scroll bars to the reports within the tab controls so that you can view the entire report. I have included a screen print of what I mean.

View 1 Replies View Related

Reports :: Show Detail - Using Section Header As Hyperlink To Show / Hide Detail

Aug 13, 2013

I'm trying to create a report where I can use a section header as a hyperlink to show/hide detail, but only for that section. For example, my customer names are:

Code:
ABC Co.
ZYX Co.
123 Co.

If I click on ZYX Co., I want it to show the contracts for ONLY that customer:

Code:
ABC Co.
ZYX Co.
Contract 1
Contract 2
123 Co.

Right now, my code looks like this for On_Click:

Code:
If Me.Detail.Visible = False Then
Me.Detail.Visible = True
Else
Me.Detail.Visible = False
End If

But it shows and hides detail for ALL customers when I do this. Is there a way to only show/hide for the customer on which I click?

View 1 Replies View Related

Queries :: Filter To Show Only The Fields Which Have A Value?

Sep 9, 2014

I have a table with 5 fields (F1, F2, F3, F4, F5).

F1 has 3 values (v1,v2,v3) and the other fields have different entries depending on F1.
-----------------------------------------------
F1 F2 F3 F4 F5
-----------------------------------------------
v1 f31
v2 f21 f41
v3 f41 f51

Is there any way to filter F1 to show only the fields which have a value?

(if I choose v2 to have shown only fields F2 (with f21) and F4 with f41

View 14 Replies View Related

Combo Boxes To Filter N Show Tables

Oct 25, 2005

Im kinda new to Access and only been using the "Access 2003 for Dummies" for learning and making small databases. So far I can find out how to do the things I want, but recently got stuck with a combo box feature. What im trying to do is select entries in my combo box so that it will filter the table and show all its contents based on that filter. Im not sure how to link the combo boxes the right way and it seems some VB coding is needed which I dont normally use. If anyone has any idea how to do it, tell me how! Thanks. BTW im using Access 97.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved