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.
The only problem is that in the two tables I was given in my assignment, there were multiple dates for the purchase, thus even though I have 74 products there is multiple listings i.e.
110-10 Sofa Chair 5x $2 profit:$10 - october 11 110-10 Sofa Chair 6x $2 profit:$12 - october 12
I need to group it so that it becomes this.
110-10 Sofa Chair 11x $2 profit:$22
The date/location is not important, only the aggregate sales. Here is my SQL ....
SELECT SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, Sum([Quantity]*[Price]) AS Sales, [Quantity]*[Price]*[Gross_Margin] AS Profit, PRODUCTS.WIDTH, PRODUCTS.DEPTH FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID GROUP BY SALES.PRODUCT_ID, PRODUCTS.ITEM, PRODUCTS.PRICE, SALES.QUANTITY, PRODUCTS.GROSS_MARGIN, [Quantity]*[Price]*[Gross_Margin], PRODUCTS.WIDTH, PRODUCTS.DEPTH, SALES.TRANSDATE HAVING (((SALES.TRANSDATE)>=#9/1/2011# And (SALES.TRANSDATE)<=#12/31/2011#)) ORDER BY Sum([Quantity]*[Price]) DESC;
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.
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.
I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year
So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.
I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!
Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?
Hey, I am currently doing an A-level ICT project and seem to be stuck at the first hurdel. My problem being that i wish to update the 'Number of sales' field for all the staff in the 'Staff' table. I have tried serveral queries to count the number of sales, which are in the 'Tickets' table for each and indidual 'Staff ID'. This was suscuessful although i am wondering how to transfer these values into the Staff.[Number of Sales]. Also some staff had the value of 0 so did not come up in results of the querie. Is there any possible solution or is there a more simple way to solve the problem. Many Thanks in advance Dan Parker
I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?
I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.
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.
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.
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!
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?
