Hi I'm hoping someone can help me with this problem (the access problem.. not the other one ! :cool: ) I have a table of Products, each item belongs to a product group, I have a table of Customers, amoungst the normal customer fields I have fields named after the item discount groups so that each customer has his own discount for that group of products... when I enter an item into the sales order form I would like the discount for that customer for that item(item group) to go into the discount box.. sounds easy if you say it fast... I hope someone can help me with this !
For an assignment I am required to do the following:
Quote: Use an expression (in field name of design view by using the build button) to create a new field (in Design View of a Query) called "Discount Price" in table "Stock" which is a 25% discount for all stock items from the new inflated price in query B (use expression builder if needed and create it the field name of a query; it will not appear in the table if you try to open the table).
So sorry to bug again. Any ideas or processes on how to go about this would be welcomed
I have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?
I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe
Product A - CustomerADiscount - 10% Product A - CustomerBDiscount - 20%
Product B - CustomerADiscount - 5% Product B - CustomerBDiscount - 7%
This link these values up within a query? Would this be the best way of doing it?
Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?
I am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now. I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?
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.
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
I have a form called 'add product' and when the form is accessed it opens up a blank form.. the user scans the barcode of the product.. heres my question..
I want it to check the products table first to see if the barcode is already in there.. if it is... to bring the correct record up.. so that the quantity can be updated..if not.. to return an error message.. stating that the product already exists..
Any suggestions.. its been one of those days today.. cant think straight! Cheers
I have to calculate the quantity of the ordered products, grouped by Product_ID, and I have to be able to print this totals for specific dates.
For this query I need data from 3 tables:
tblProducts Product_Name
tblOrders Product_ID Ordered_Quantity
tblCustomers Delivery_Date Products_Ready (Yes/No type, used for showing only those records which I need, in this case I will set it in my query to "No")
Whithout including in query Delivery_Date, everithing it's simple. I made a query, grouped by Product_ID, and running a sum on Ordered_Quantity.
But if I include Delivery_Date, because it can be different from order to order, the query doesn't show the totals the right way, once/Product_ID.
Can anyone help me on this? A piece of SQL code or everithing else is greatly appreciatde.
Can some help, I have got a form with a field showing different product number on there, how is it possible to calculate the total of each product on the form?????
Thank you in advance for any assistance you be able to provide.
I would like some help on searching for product codes or serial numbers and formatting this into an order form a current customer form. I have 4 main tables
CustomersID (PK)– Name, Address, City, Post Code, Tel Number OrdersID(PK) – CustomersID(FK), Order Date, OrderDetailsID(PK) - OrdersID(FK), ProductID(FK), Quantity ProductsID(PK) - Code Number, Serial Number, Description, Color, Unit Price
I think I have all of these linked correctly
Here's what I am trying to achieve (so far with little success) I have a customer form that I fill in name address etc; I then have a button that will take me to a customer order form, easy so far.
In my product table there are about 2000 separate products (spare parts for electrical appliances) so what I would like is for a popup box to appear when the order form is loaded (as a drop down or combo box isn’t practical ) so I can search for the product by code number or serial number only for a particular spare part. The description and price are fixed in the products table and should not be changed on the customer order form, I would then like the selection of the code to transpose to the order form.
In other words I would like when the code number has been entered and the product found that the info should then be added to the order form (model or serial number, part description, color and price all taken from the products table) I would like it that when the product has appeared on the order form that it cannot be changed, only by being deleted.
I know this appears to be rather basic but for some reason I am hitting a brick wall, I have looked at many db's and even tried to dissect Northwind to do what I want but cannot make it happen, now after 8 days in the wilderness I need help or guidance.
I have been away from access for a while now and I am sure that this is simple. I tried a search but found nothing.
The issue: A)The user enters in a product number, if not found then a message appears to tell them so...this I know how to do B)If the product is in the table then I would like it to display some of the fields on the form. EX: Prd # 0659875 MSG = does not exist
Hi I've been trying to get my head around this and after coming up with no search results Gonna ask for ya help.
I've attached a jpg which uses sample data so nobody needs worry as the program displayed will still be available free.
What I'm trying to do is:-
Should a user build programs for general sale they can then sell the first copy at full price but should a customer wish to install other copies then the user will have the option of selling additional copies at decreasing rates depending on how many copies a client purchasesHas so saying that looking at the jpg if I was to bye a program for a network of 20 systems and want to install legal copies on each I would pay the full price for copy 1 and £75 For copies 2-20
My thinking is I first check for any multiie prices for a product when the productID is selected then if that returns >0 then I need to check the invoice system to see if a client has already payed for X copies that way I can get the correct price for the EstimateInvoice.
The Problem I'm having is I can't seem to get my head around the getting the correct record as it uses a low and high number with a listed price Please see Jpg I have been thinking of using Between Low and High of Low >= X And High<=Y But that's bothering me as I'm not sure if it would just return one record. If returned more than one record maybe I could use sorting to get me the lowest price for X & Y but I don't feel happy with that?.
I'm new to Access (2002) and have been trying to learn by reading books, posts, forums and practicing with models. I've set up a database for inventory, primarily to track the cost of goods sold as raw material costs change from month to month. So far I have tables for raw materials, container sizes, suppliers, and finished products. But I have a problem - in some cases a finished product is also a raw material, so cannot appear in both tables. (Let's say we make cookies and we make ice cream, and we also make ice cream with cookies on top as a third/separate finished product!) Should cookies have two records ('CookieFinished' and 'CookieRM') and can they be linked together so cost is determined before it's part of another? Is there a better way? Thanks in advance!
I have 2 tables a project table and a project additions table, they have a relationship of 1 to many. When I run the query using both tables it only gives me the results of the project numbers that match in both fields. I need to have all information from both fields.
I have started a type of CRM database in which all my customer information and product info is stored.
In order to create quotations for each customer, I want to be able to search and select products by product ID, adding quantities if necessary, to be printed as a report.
I'm going to design an access database for an LPGas company. This company sells LPGas and LPGas cylinders.
Suppliers:
1.AlfaCylinder supplies it's cylinders(not the same kinds of cylinder as ME's) 2.MECylinder supplies it's cylinders(not the same kinds of cylinder as Alfa's) 3.PerfectGas Co. supplies it's LPGas.
Products :
This company sells cylinders and fills different kinds of cylinders with LPGas. for example, 11 kilogram LPGas in one kind and 25 kilogram LPGas in another kind and so on. Therefore, products are :
See the attachment
No problem with customers table. There are two kinds of customer. 1. Known customers 2. Unknown (everyday changing) customers. (there is a record for them in customer table named as Uncustomers, that at the end of the day, the sum of sale of this kind entered under this name.)
Problem is : As you see, there are some products with the same ID and same name or same type but different prices. How can I arrange the product table?
I was creating a access web app for my company and want to create a data for its product. Each product will have multiple colors and different material are used for each color. The number of colorways for a product varies.
We want to be able to select a product and a lookup field will appears for us to choose its colorway.
In my database i have a field called product code which the user enters the code as T0001, T0002, T0003 and so on for each record.
would it be possible when adding a new record for that field to auto fill the product code as T0004 for example, so the user doesnt have to type the code in all the time.
i am developing a clothing order management database.
I have so far got [order details], [products], [product details] entities. This is so that products with the same model code can have a selection of colours and sizes which are held in the product details table.
I followed this website which was very helpful: http://www.princeton.edu/~rcurtis/ul...database2.html
The question is now: each size and colour will have a different stock quantity. How do i model this, for example, so when a Small Red t-shirt is added, the small red t-shirts quantity decreases rather than the overall quantity for the model.
