DSum Function To Sum Sales Of Each Customer?

Jun 5, 2014

Can Dsum function sums the sales of each customer. For example, let say we have the following query

Code:
QueryCustomerSales
Customer Name
Order ID
Item
Amount
Price

So, can i get output something like :

Code:
John 200.540 $
Mark 300.350 $

I tried the following code "

Code:
Dsum ("[Amount] * [Price]", "QueryCustomerSales", "[Customer Name]= [Customer Name]")

However, it returns the sales of all customers (all together ):

Code:
John 500.890 $
Mark 500.890 $

View Replies


ADVERTISEMENT

General :: DSum For Current Month Sales

May 28, 2014

I'm using the following expression to find the sum of the current month sales.

Code:
=DSum("[Price]*[Quantity]","QueryCurrentMonth Sales","[ORDDate]=Month (now())")

However, it returns no result. The above expression is located on the control source of an ab textbox. Moreover, it works well when i try the following expression

Code:
=DSum("[Price]*[Quantity]","QueryCurrentMonth Sales","[ORDDate]=[ORDDate]")

It returns the sum of all sales.

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

Dsum Function In A Query

Mar 19, 2007

hello
i have a query summing sales data by month.
what i need to do is be able to list in this query the sales summed for same period last year.
I have some other criteria but this is the just of it.
trying to use a dsum function in the query itself and using product id and date range derived from fields that calc starting date last year and ending date last year for time period.

example , sum this years jan and feb 2007 sales and also have the dsum function sum jan and feb sales of 2006 in a field right next to the 07 values.

I can not get the dsum function to work???
any
ideas????

i enen tried creating a temp table with the 07 sales data summed and values for each period in seperate fields ie date1 = 01/01/06 and date2 = 1/31/2006 and the same for feb. The dsum did not work that way either.
If i hard code the dates into the dsum it works by i would like it to be more dynamic based on the date1 and date2 values.

View 1 Replies View Related

Aggregate Function DSUM

Feb 17, 2008

Hi All,

I have a table namely, leave_details wherein there is a column called DAYS and TDAYS,
DAYS columns represent number of leaves an employee has taken in a single leave application and
TDAYS represents Total number of leaves an employee has availled till date.

To calculate the TDAYS i need to SUM the DAYS column for every employee.

When an employee again log in the application he should be aware of the total number of leaves which he has already availed.

Please help me for the same as I am unable to do so.

Kindly revert back for any queries or any further information.

With Warm Regards,
Sanjay

View 3 Replies View Related

Pareto Graph And The DSum Function

May 1, 2008

Hi All,

I currently have a Pareto graph created by a query, that counts occurence and then uses the Dsum function to create the cumalative %. The function is as follows:

CumPct: DSum("[CountofConcern_Number]","qry_ProbByConcern","[CountofConcern_Number]>=" & [Total Concerns] & "")/DSum("[CountofConcern_Number]","qry_ProbByConcern")

This is as per MS article: http://support.microsoft.com/kb/208373

Now this is OK and is as per the MS article but if you have counts that are the same, it just give the same cumalative % for each one that is the same. Please see attached image for an example of what this query produces.

Does anyone know how I can sort this out? TIA

View 1 Replies View Related

Forms :: DSum Function In Form

Dec 7, 2013

How is the collection of a sum of money to a particular substance.There is, for example, in the table named baby milk product.How can I collect the amounts of such material only.I used a DSum

View 6 Replies View Related

Syntax For DSum Function For Currency Fields

Nov 25, 2006

I have a Number field with value as
DSum function where the criteria is
Textfield which works well.

=DSum("[MCRef]","Miracle_Cloth_Main","[Cust_ID]='" & [Cust_ID] & "'")

It doesn't show the SUM when the type of
textBox is CURRENCY

Can anyone suggest IF Ineed to change the syntax ?

View 2 Replies View Related

Forms :: Getting Error With DSum Function In Subform?

Feb 16, 2015

How to use Dsum Function? Am using below following Dsum functions but they are not working and shown an error result.

=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]=MILK"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]='MILK'"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]<>MILK"),0)

View 3 Replies View Related

Queries :: Format Date Parameter In Dsum - Undefined Function

Sep 25, 2014

I m using Access 2010.I m Facing a problem in the undermentioned expression,

OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)

when i run the query it gives "Undefined Function 'Format' in Expression" ?

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

General :: Create Check On A Field (customer ID) Of Customer Table In MS Access?

Sep 7, 2012

create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"

View 6 Replies View Related

Add Customer Name Into Table Each Time Unique Customer Added To SaleTable

Dec 11, 2013

I have a Table Sales - that lists customer and order information

Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale

(Without having to add the custome to Customer Name Table - then going to Sales Order)
OR
Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.

So:
Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)

View 6 Replies View Related

Combining Customer And Customer Contact Tables

Dec 24, 2011

I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.

View 1 Replies View Related

Cash Sales Vs Invoices

Mar 25, 2007

I'm creating a database where I have a customers and invoices table, obviously there is a relationship between the two tables using a customerID field where I have referential integrity enabled. However not all invoices require a customer account as some are cash sales where no customer details are required.

I'm unsure of the correct procedure, all I can think of is to create a separate table for cash sales??

View 3 Replies View Related

Querry About Sales Commisions

Oct 1, 2005

Hi There
I Need A Querry For Commission Sales.
I Have 10 Items 7 Items At 2% Of Commission 3 At 4%. Each Salesman Have To reach A Min Of 5000 USD Of Sales Per Month Then All Sales Above That Amount Commission Will Be Calculated.
I Need A Querry To Calculate Sales And If Salesman reached The Min Sales Commission Is Calculated .

Any Help ???????
Thanks

View 1 Replies View Related

Calculating MTD And YTD Sales For Report

Dec 1, 2005

I have been stuck on this one for a while. I have an Access 2003 Database that I need to calculate the MTD sales and the YTD sales of parts ordered

Here are the relevant tables and thier attributes
Part -
PartNum
Description
UnitPrice
OnHand
ReorderPoint

Orders -
OrderNum
OrderDate
CustomerNum
CustomerPONum

OrderDetail -
PartNum
NumOrdered (Quantity)
QuotedPrice
NumShipped

If I have left anything off that may be vital please ask and I will supply the info. I need this info ASAP though.
I think the answer has something to do with the Date() and CurrentDate() but I have no clue. Please let me first know the queries I need to run and that should be it. I just need to put MTD and YTD Sales in a report.

I REALIZED THIS SHOULD BE A QUERY THIS WILL BE POSTED IN QUERY INSTEAD

View 10 Replies View Related

Query Sales Anlaysis

Jan 16, 2006

Good afternoon

I have two tables - sales 2004 and 2005 - both showing account number, product, quantity, sales and margin. When I link account number and try to show both years the results are incorrect.....I know I am doing something wrong!! I have tried creating new table with blank fields for 2005 basing the table on 2004 and then updating 2005 - but no go.....

Help please

Many thanks

Catherine

View 4 Replies View Related

Sales Transaction Query

Mar 17, 2006

hi there...

I have been looking around the forums for a little while now... and cannot seem to find anything which will help me.... heres my query..

I have a stock control database.. which allows for sales transactions via a barcode reader...

my first query is.... when the user scans a product.. how can this be 'looked up' in the (UPC data table)... returning the correct information.. ie.. description.. but then taking the price from the product table.. the query doesnt seem to let me be able to join the two tables.. no idea why!?

secondly.. I want to create a form.. for the 'sales transaction'.. when the user clicks 'pay' i want the form to clear and store all these items in the sales table.. (deducting the units in stock at the same time..)

I hope anyone has any suggestions.. or pointers! Many Thanks
:)

View 7 Replies View Related

Negative Sales Goals

Nov 30, 2006

Sounds weird huh?

I have a salesman that has a negative "Net Flow" goal (sales goal). Others have positive goals.

His YTD Net Flows (Sales) is say...$1062 (million) and his goal is "-266 million"
Of course he is doing quite well, when I use the following code to get a % to goal I get "0" whenever there is a negative goal involved.

% to Goal: IIf([NetFlowsGoal]>0,([Total Net Flows]/[NetFlowsGoal]),0)

But it works fine if all numbers are positive as you might guess.

Any pointers would be great...Thank you

View 4 Replies View Related

Display Only Last Week's Sales

Jan 19, 2006

Hi all,

I dont know much about access forms - but I have a table that contains all of the members on my site, and the date they joined. Each member can be seen as a sale on my product, so at the moment Im using a simple graph (made using a wizard - shame on me) that just drags out the DateJoined values from the members table.

The result is a bar chart indicating how many sales I made on each day.

Its getting a bit big, and I want to know how I can get it to only drag out last week's sales. I think it will have something to do with the 'Row Source' property of the graph. At the moment its value is...
Code:SELECT (Format([DateJoined],"DDDDD")),Count(*) AS [Count] FROM [tblMembers] GROUP BY (Int([DateJoined])),(Format([DateJoined],"DDDDD"));

Thanks all!

View 2 Replies View Related

Difference Between Weekly Sales

Sep 11, 2011

In a table I have the sale of a department:

week SP Value UNIT
N John 100 8
N-1 John 80 6
N-2 John 120 10
N-3 John 90 9
(just the last 4 weeks and N sale professional)

I would like to have a form with

SP Value N-1 N-2 N-3 Units N-1 N-2 N-3
JOHN 100 20 -40 30 8 2 -4 1

And this go on for each SP

View 1 Replies View Related

Query Based On The Higher MTD Sales

May 29, 2006

Hello,

I need to write a query based on the higher MTD sales in the series of each fabrics within series of Sales Group and Prod Group . All info is from one table

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6...(PK)

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6......(PK)

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: a,b,c,d,e,f...
StyleNum: 1,2,3,4,5,6......(PK)

Thank you

View 1 Replies View Related

Sales Order Line Pricebreaks

Jul 11, 2007

I'm not sure if I have a table structure, query or Form/subform problem, so I'll post here and see what happens

Objective - From a Sales Order form (and linked Sales order Lines subform) once the Item to be sold has been selected and quantity entered, I wish to check against a Pricebook table, where each item has 3 price/quantity breaks stored and have the correct price drop into the sales order line

Any suggestions on what is the best technique to achieve this?

ps not too bad using standard query builders etc, but pretty hopeless at code (which is one of the reasons for joining, to improve in this area)

So far found this a great place to be, lots of good advice from lots of helpful people

Thanks

View 9 Replies View Related

Setting Up A Table For Sales Figures

May 6, 2005

Dear All,
My boss wants me to create a budget report as follows:

Rep Cust ActM$ BudgetM$ VarM$ ActMProfit BudgetMProfit VarMProfit

ActYTD$ BudgetYTD$ VarYTD$ ActYTDProfit BudgetYTDProfit VarYTDProfit


I have a table with the following headings:

Date State Product Prod Code Customer Cust Code Category Jan Feb Mar etc

State has the domain Vic, NSW, Qld
Category has the domain Sales GP

Question 1:

To the field Category, should I either

A: add to the domain actual sales and actual profit? I can past these into the table at the end of each month.

OR

B: set up a separate table for the actual sales and actual profit for the month?

I think A.

Question 2:

Instead of having a separate column for each month, should I either:

A: just have a heading Month and put the figures in that column

OR

B: Have the sales figures in separate columns for each month?

I think A

Question 3:

When I have set up my table correctly, and assuming the answers to my questions above are all "A", I am now unsure how to create a query which will give me the data for the report my boss wants.


Can someone please give me some help?

Thanks
Bon

View 4 Replies View Related

How To Treat Tiered Sales Commissions

Nov 1, 2005

I'm working on a contact manager/sales manager database for a company where commission plans are tiered based on the gross margin generated from the sale. The company also has two types of commission plans available to the sales rep (Straight commission & Commission + Base). Here is an example:

I would like to be able to use a table to hold the tiered levels and then use code that would look at the gross margin and determine the correct tier that the sale falls into. Is this possible? If so, can someone please share an example?

Your help will be greatly appreciated and I will definately share the finished project with the group. Thanks!

View 5 Replies View Related







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