Stock And Sales Form/Sub/Append!?? Help
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 Replies
ADVERTISEMENT
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
Sep 16, 2014
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10.
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
View 2 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
Jul 19, 2014
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?
View 6 Replies
View Related
May 6, 2013
In order to create a stock database and make it easy to register mutations in the database.
A mutation is a change in stocks and I want to make it possible to enter a lot of changes at once. There is a mutation table and a mutationlines table that has a relation with articles. In the form I want to show all available articles (about 65) followed by a textfield where I can enter the amount of articles affected.
The problem is that I don't know how to pull all articles from the articles table to use in the form.
Is this even possible?
View 3 Replies
View Related
Nov 14, 2014
I have an order form with an order details sub form. Both are bound to their respective tables and function correctly, with the OrderID being inserted automatically into the order details table.
However I am using VBA to update and check stock and have it working when attached to a separate form called process orders. Which event would be the best place to place this code. I do not really want to have the process orders form. Would it be on the master form or the sub form. I thought of placing it in the after insert event on the subform, but there can be many order items per order and it will run before I have finished adding order items. I tried it in the after insert event on the master form, but I think that this will run the code too soon as at this stage the order details will not have been written to that table.
View 4 Replies
View Related
Jan 9, 2014
I have a form that displays a stock inventory, one of the fields I use is called [ItemCode].
On the Stock_Inventory form I have 10 command buttons that open other forms to display additional product information.
These were previously sub_forms that sat in a tabbed control box within the Stock_Inventory Form but as it now takes a while for the form to load (lots of queries running each time you search for a product) users are getting slightly frustrated.
So, what I decided to do, was move each subform from the tabbed control into a new form and open that form when it is needed by the user.
OK, when I open Stock_Inventory and search for a product [ItemCode = "ABC123"] and then want to see the Sales_History, I have to open my new form, Stock_Inventory_Sales_History, within this form are two sub-forms, each subform has a field called [ItemCode].
However, when Stock_Inventory_Sales_History opens it asks me to input the parameter [ItemCode] twice, I presume this is because the Stock_Inventory_Sales_History does not have a record source and that the subforms need [ItemCode="ABC123"] to run each query and return the data.
I need to pass the [ItemCode] from the Stock_Inventory form to each of the 2 Subforms that are on the Stock_Inventory_Sales_History form automatically.
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
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
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
Feb 18, 2007
hi Guys,
I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.
I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.
I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"
However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.
What am i doing wrong???
Any inputs will be greatly appreciated.
View 3 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
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
