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 Replies
ADVERTISEMENT
Feb 18, 2015
I'm making a query that has the following Fields
Product (Table PDZRN1)
Whse (Table PDZRN1)
Description (Table PDZRN1)
On Hand (Table PDZRN1)
Unit Cost (Table PDZRN1)
Unit Price (Table PDZRN1)
LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales
Table - Sales_History
Total - Sum
Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
View 14 Replies
View Related
Jul 9, 2013
I have a report that is calculating account revenue for my sales reps. I am able to generate the report, group by the sales rep and then total their total account revenue. Now I want to group on the total field that I just calculated. How do I do that?
View 14 Replies
View Related
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
Jul 30, 2015
i would like to know how to replace the sales values in the quarterly sales report with tax values so as to create a "Quarterly tax report" in Northwind.
View 1 Replies
View Related
Jan 9, 2006
Hi,
I have a problem with form design,
I want a form like in Northwind sample database: Summary of Sales by Year Report.
It use Sorting and Grouping for Footer that I can't find it in Form design.
Is it any other way to do it in Form design so I can get the same result like in Report design?.
The reason why I want it, because I want to control the size.
thankyou in advance for your help.
Gunawan.
View 2 Replies
View Related
Aug 30, 2004
Just wondering if anybody could help me on this. I have encountered a problem in producing a report based on my main form (recordsource: tblStudents) and subform (recordsource:tblStudentsClasses), which are joined through their respective StudentID fields. My problem pertains to the fact that I just couldn’t seem to find a way to make my report produce a calculated value using an unbound control. I need it to show the average grade a student gets every semester by multiplying his individual class grades against its corresponding credit units and then add them together, and divide them against the sum total of that particular semester’s credit units. For example, a grade of 1.0 for psychology 101 multiplied by that subject’s 3 credit units + a grade of 2.0 for chemistry 101 multiplied by that subject’s 5 credit units and then divided by the sum total of those two subject’s credit units of 8.
I kinda find it complicated because each semester is made up of between 1 to 10 classes, so it would be inappropriate to just put the one average computation on a footer since each semester has to have an average grade of its own. Tried lots of procedure already and couldnt make anyone of them work. Another fact is that a credit unit of –3 and grade of 5.0 shouldn’t be included in the computation at all. And some of my data are supplied by combo boxes in the subform – cboCreditUnits and cboGrades (rowsources: tblClasses and tblGrades). How should I go about this all? Any ideas will be highly appreciated.
Thanks!
View 1 Replies
View Related
Jul 29, 2015
we have a field in a report, and currently, there is a text box with a control source of
=IIf([Flag1]="Capital",[Reserve]-Sum([Text52]),"")
They want me to add to it, where if [Text106]="0",[Reserve]-[Text52], otherwise it's [Text106]="0",[Reserve]-[Text106].I am able to do this second part OK with
=IIf([Text106]="0",[Reserve]-[Text52],[Reserve]-[Text106])
But then I lose the [Flag1]="Capital" part.
View 4 Replies
View Related
May 6, 2013
I am currently using Access 2007 and have created a huge database for our investment managers to calculate the income based on percentages. The percentages are created in Excel. I have uploaded into the database both the June and the July percentages. When I run the query for my report using the date range for June, it works fine. When I request just the July information (entering the date range for July) the June data is doubled on the report.
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
Mar 3, 2007
I have created a cross tab quarterly query to sjow the 4 quarters sales.
Problem is if there is no sales in one of the quaerters, the report based on that query gives an error as per attached photo and do not open.
any work around
View 1 Replies
View Related
Nov 10, 2006
hi,
i have three tables - Customers, Sales, Stock
i have a form (like an on screen invoice) for sales. This shows the Customers Details and what stock they have bought. The customer details are the main form and i have a sub form with the stock, however i realised that this would not work (as a sub form is for adding stock not appending existing stock.)
Ideally i would like to type the stock number in, then the details of that piece of stock come up, then i want to change bits (a tick/untick box) but this would not work on a sub form. Customers buy UP TO 5 pieces of stock in one sale.
Any ideas?
thanks
View 9 Replies
View Related
Aug 9, 2013
I am pulling sales results per customer and month. For presenting the numbers in a diagram I would like to show 0 sales for months in which no sales were generated. At the moment the query results only the months in which there are sales > 0. Is there a trick that I don't know?
SELECT Year([InvoiceDate]) & ' ' & Month([InvoiceDate]) AS [Month], totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]) AS Ausdr1
FROM Invoices INNER JOIN customers ON Invoices.CustomerID = customers.CustomerID
WHERE (((Invoices.InvoiceDate) Between #1/1/2013# And #7/1/2013#) AND ((customers.CustomerID)=145))
GROUP BY totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]), Year([InvoiceDate]), Month([InvoiceDate])
ORDER BY Year([InvoiceDate]) DESC , Month([InvoiceDate]) DESC;
View 3 Replies
View Related
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 5 Replies
View Related
Jan 8, 2008
I have a table with historic sales values, so an Item has 36 months starting (Field names are ranging from "Oct 04", "Nov 04", "Dec 04" ... to "Sep 07")
I also have a column name First month of Sale. which is filled with for example "Dec 04", for the next Item is might be "Jun 05" or "Aug 06").
I would like to get a Query where I just show sales values of the first 10 months after that first month of sale.
I have tried, and tried, and tried till my head exploded:mad:
It seems a simple task, but I am obviously overlooking something.
can anyone help me with some hints?
View 5 Replies
View Related
Feb 27, 2008
I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.
View 7 Replies
View Related
