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
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.
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?
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.
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.
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.
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.
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
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.
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??
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 .
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.....
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 :)
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.
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"));
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)
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
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.
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!
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.
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;
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?
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.
