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