I am looking for a way to have different prices saved for each individual client.
I would have one main price list but i want to set certain items to have special prices for different clients. How is this possible in Access in excel its so simple.
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?
Hi,
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.
any ideas?
Hi all I have an order entry database. I need to talk through this problem I have, if anyone would like to let me call them then pls pm me.
Within the database there is an add ne w order & details form where, the functionallity behind this prolem will need to be explained to me in very shall we say easy terms.
I need the product price, to be different all customers. The product list is the same for all customers.
When the customer name is chosen by the "user" in the order entry form the form automatically tallies up orders based on the costs to that specific customer
any ideas guys on the best way forward.
Thanks for reading
Mark
I am trying to set up a database to track truck maintenance, repair, and inspection records for a trucking company. Parts are ordered from vendors, and I have forms set up to facilitate the entry of the information regarding those orders. The problematic form is based on a query. In that query (this form and query deals with the purchase details - product, price, extended price), the product ID is gathered from the inventory transaction table, the product name and price are gathered from the product table (I did this so that price would enter automatically).
Of course, when I change the price, it is changed across all related orders, including those that have been saved previously. I know that the problem more than likely stems from having the info pulled from the products table. I'm just at a loss as to any other option to have the price automatically pulled.
I've got an order detail form which has
product id, product description, customer, quantity and below the form a Total quantity
When I enter the same product id, and when i press the button to update, the table of stock quantity deducts from total quantity
If I use different product ids, it doesn't work, but updates only the current product id, where the mouse current record is set by mouse.
By the way i use dlookup to check if the stockquantity has enough items.
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.
Hi!
I need to create a database of the info of a few hundred companies. Each company (food producers) specializes in various product categories, not just one. This is my problem/question: each company entry should then have a possibility to have as many product categories as needed. How can I do this without using "free text" fields? The website user should obviously also be able to search the db - by clicking checkboxes with the categories maybe?
(+Also: When the admin user needs to add the info of a new company (with, say, 9 product types) into the database, what kind of an SQL clause I need to INSERT INTO the db? For example if the product categories where laid out as checkboxes, and the user would just click the ones he wants.)
I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc
View 3 Replies View RelatedWhat I have is a table that contains plant info (plants that produce a product not vegetation plants). I also have a table that contains all the fields related to the products themselves. Finally I have a third table that contains the constituent items that make up products (think raw ingredients if you will). I have various junction tables set up to define relationships between all these.
What I'm trying to do is create a way of inputting a product and having an option on the form to send that product information to multiple plants. I don't know why but I cannot visualize an approach to doing this.
I am (kind of) new to Microsoft Access and have a question about making validation rules more complex.
I am making a quality control database with 3 tables. One containing the product and their unique codes, the 2nd with their unique lot numbers for each different production run, and the 3rd with all the required quality control data for each product.
Each different product has different required specifications. For example, Product AB-544 needs a value between 11-20 while product RY-233 needs a value between 21-30.
My question is: Is there a method of defining different validation rules for different product codes? I want the QC tech entering the data to only be able to enter in quality data that is within the required specification defined by the product code. If the current product is AB-544 he should only be able to enter in 11-20 but if he is entering in data for RY-233 he should only be able to enter 21-30.
I have the following form and subform combination:
The subform is linked to the main form by way of the "ProductID" control. (It's hidden in the subform).
Basically, each product (the finished article) is made up of many components.
What I would like to do is this:
1. Increase the stock of a "Product".
2. The Stock level control on the main form will then increment as much as selected
3. Contrary to that, I would like each component to decrement the relevant amount based on how much of the component is used up to make the product.
E.g. If I have Product A and it contains 2 components of which component A is used once per product but component B is used twice, then when I increase stock of product by 1, then component A goes down by 1 and component B goes down by 2. But If I put two of a product in to stock, I would obviously need component A to go down by 2 but component B to go down by 4.
I am thinking I need some way to loop through each record in the sub form for the current form, such as "Do While Not", or a "For Each". And then stick an Update query within the loop, but to be honest I can't think of how I need to address it!
I've now populated my database with all my sales information.
Now, towards plan/prepare 'replenishments' from suppliers, I need to know how many of each product I've sold (ideally between two given dates ....because recent sales history is more important than say sales information from 1 year ago), but let's keep it simple!
How can I get such info out of my database, the info is contained in my database something like this...
Code:
Order date Qty Product ID
1-feb-2014 1 widget A
2-feb-2014 1 widget A
2-feb-2014 1 widget B
3-feb-2014 2 widget A
4-feb-2014 5 widget C
(in my database, qty is declared as 'double', where Product ID is categorized as 'text')
So I'd like an 'order summary' from the above raw data (contained in my database) to look something like this...
Code:
Widget A Sales = 4 units
Widget B Sales = 1 units
widget C Sales = 5 units
How can I do that please? (I looked at access reports, but couldn't see a way of multiplying 'product by quantity' & then total it all up).
creating a product catalog over a range of products.
I want to create a catalog where each page of the catalog contains a product, describing; Performance data, pictures of the product, technical drawings, and documents on the product e.g.
Also it should be possible to search in the performance data, so that one or more parameter from all products can be compared used for benchmarking the products to each other.
I am using the 2007 version, because it's the one that I have available at the current time. My questions are mostly related to the possibilities of creating such a product in Access 2007, or if I need to upgraded to a newer version or even use another software program?
I'm trying to make a database for some product that have barcode, but i don't understand how to scan product barcode then interpret it into data in access,,, and how later I can look for these data by using barcode.
View 4 Replies View RelatedI have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
ive got a database for tickets for a school play. how can i create a query that adds up prices from multiple records? would the total be displayed in a form?
View 1 Replies View RelatedI am a beginner Access Programmer, so my understanding at this point is pretty basic, so that could be why I have run up against a wall with this one.
I am trying to create a database that creates Estimates. In the most basic setup, I have a customer table, an estimate table and an item table. The item table would have a description and a price per unit. With those tables I can set up a basic form that creates an estimate for someone. The wall I am referring to is when I ask myself, "how am I going to deal with price increases?" With the current setup, when I change the prices in the item table it will change all previous estimates prices.
How have others dealt with this scenario? I would have a hard time believing this is a rare question. In the real world this is going to happen. Yet I have several access 2007 books and none of them seem to address this. The closest thing I have seen that seems like it might be in the right direction would be some sort of update query.
Any thoughts on this would be great,
James
Hi, please help if you can, i have searched the threads, but unfortunately didn't see anything close. Please point out if i missed some.
I am trying to lock or archive old records/invoices from updating when prices update for the same products/services.
Anyway of doing it a quick and dirty way perhaps?
U people rule! - built my entire system based on on your help threads/samples!
Thanks
V
I recently got asked by a friend to set up a database for him to store his business contacts (so he can do mailshots etc) along with some basic order history. I've come up with the basics of this database (attached). There is still some work to do on it, a bit of tidying up and some reports etc but I need to go and see him show him what I've done and to clarify exactly what reports and stuff he wants.
I'm only doing this in my free time but he has said he'll pay me for my efforts. I don't feel I can really charge an hourly rate though as I'm still only learning the ropes and what might take me several hours might only take an experienced developer an hour or even 10 mins! :o
So I guess what I'm asking, is could those of you who are self-employed, or indeed anyone who has some idea of what the going rates for databases are, take a look at what I've done and give me an estimate of how long it would have taken to put together something similar and how much you would charge for it (either in total or per hour).
I'm also sure that some of what I've done could have been done a lot better, so if anyone has any views on improvements I could make, I'm all ears. :)
I am trying to create an Access database that can be used to configure computers for sale in a small pc repair shop.
What i need it to do is have drop down boxes for different parts, each part having a value, and at the end being able to update the total value depending on which parts are selected.
I have tables with different types of CPUs, Motherboards, RAM, etc etc... each table has description and price values ( a couple more for relational stuff but i don't think that is needed to figure this out ) Just to test it out i have a form where i can go through and select the different products, but i am stuck on the adding of values of the different computer parts.
So anyway, i am trying to take prices matched with values from multiple tables, then add the prices all together at the end for a total value, like any of the pc configurators online.
If anyone can help, greatly appreciated, if i make no sense just ask to clarify...
james
I am trying to write a breakout formula to identify if today's close is greater than the maximum close of the last 20 days. This is what I have so far:
SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose
FROM WZ07
WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));
If I replace the = symbol with a > symbol near the end will it compare today's close to the max of the date range selected? I thought I had this figured out but apparently not.
I can do a query that will give me the last 20 days using the "Between date()-28 and date()-1 criteria. I can do a separate query for the maximum close using select max([WZ07].DClose) from [WZ07] but when I try to put them together it screws up. I need to have it first do the last 20 days query and then take the maximum close from it, and then compare to see if today's close is greater than that number. Can this all be put together? Thanks for your help.
hey guys,
I'm creating my tables on paper.
What im hopping for is to be able to add a second price in my inventory list that will show up on all the order form between a time periode and if the time periode is empty it takes priority .
Regular prices: 14,99$ (this amount never changes)
price a 4.95$ between 10-10-04 and 15-10-04 (the price of the item will go back to its regular price)
price b 10.99$ no date specified (this becomes the new regular price and take priority on "regular price")
price c 7.99$ no date specified (this becomes the new regular price and take priority on "regular price" and price a)
price d 4.95$ between __-__-__ and 30-12-04 (the price of the item will go back to its regular price,in this case it will be price c)
I figure i need to creat a second table (otherprices) that is linked to inventory list. do i keep a price field in the inventory list or do i make a query. if so im not sure how to aproach neither of them, can someone explain to me witch way to go and what it takes to make it happen, i dont want to tackle this proble when it arrives because then it will be a toal mess.
thank you
Mark
Here is my problem:
I have a database which we use to track customer orders of products that we provide to agents to sell to their customers. We have to keep track of the end-customer for redemptions/cancellations, and pay commissions to agents based on sales.
So, I have a Customer Table, Product Table, Order Table, and Agent Table. These are all tied together via the Order Table (Each Customer buys a Product from an Agent and this is known as an "Order").
We occasionally change the commission levels to the agents and the cost of the Products. However, these are mostly fixed costs and they apply across the board to all products of a type sold by that Agent. We institute changes as of a particular time period (all products sold after October 1st will cost $, etc.)
My concern is that if I change a price or commission now, it effects the commission and income reports for all time periods prior to that when the price change wasn't in effect. So if the price change takes place in October, I don't want that change to cascade to August orders.
I'm thinking that I need a price table with a date range in it and the ability to associate that with commissions and prices. I'm thinking I may have to further distinguish between prices and commissions, but I'm not sure. I'm sure that this issue has been addressed before (hopefully by someone on this forum :) ) and am looking for advice on setting this up.
As you know, the prices are, unfortunately, dynamic "objects".
So, I have a table for products:
tblProducts
ID_Product - Autonumber (PK)
ProductName
and a table for prices
tblProductsPrices
ID_ProductPrice - Autonumber (PK)
ID_Product - Number (FK on tblProducts
Price - Double
PriceDate - Date/Time
The problem: I have a certain date. I need to know the available prices at that date for all products.
I have a solution; but, for each product, I apply an external function that use two aggregate functions: DMax and Dlookup.
For my small tables, this isn't a problem but I like to know if it is a better way.
I'm asked to do a specific task, a database concerning of:Tables: 25Forms: 25Reports: 40-45Build from ground up, multi-user, accounts, account-restrictions, has to be linked to an already existing database, will need a fair amount of VBA coding.I would like to know specifically two things:--> what is your common hourly rate --> what would your common total-price-estimate for a project like this.
View 1 Replies View Related