Tables :: Same Product Same ID BUT Different Prices
Sep 19, 2014
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 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.
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
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 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'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?
Whenever I try to type in the DLookup function, it does not calculate.
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 am very new to access. Logic and method needed in order to create a data base that records weekly orders.
What types of tables do i need? How can i input more than one product per order? How do o group the information in a organized way?
Ideally i would like to Input the clients name the week of the order and then all the products etc that are ordered.
Then be able to see in an orders table each client that made an order and a subdatasheet (the small plus in the corner) that once clicked i see all the products that are in that order?
You have a company that sells a small line of very exotic, non-perishable, chemicals to manufacturers.
You source these chemicals from around the world and resell them to the manufacturers.
The chemicals are not only very expensive, but may or may not be required for any given batch of product the manufacturer is producing.
The manufacturer does not know if the chemicals will be required, or not, until the last minute, so must have them immediately available for each batch.
You keep track of the process with a simple database that is built around a "Chemicals" database and a "Transactions" database. Which are linked by [Product Code]
You are seeking a way to make these chemicals accessible to manufacturers who cannot afford to buy them, just to have them sit around waiting for the day they are needed.
You set up a system where a manufacturer can purchase an "Option" on the chemicals whereby:
They pay you a certain amount of money for the right to hold the chemicals at their plant for a given period, denoted in weeks.
After the pre-determined period is over they must either:
Return the chemicals to you and you keep the money they paid for the option.
If they used the chemicals, pay you the pre-determined purchase price --you still keep what they paid for the option.
My problem is incorporating the options into the database built around "Chemicals" and "Transactions," e,g,:
(1) The "Chemicals" table has fields such as [Product Code],[Quantity in Stock],[Cost],[Sales Price per Kilo]. The options require a unique set of fields such as [Product Being Optioned], [Expiry Date of Option],[Sales Price per Kilo -If Used],[Weekly Price for Option]
It doesn't seem correct to jam these into the same "Chemicals" table although I could be wrong.
(2) The same is true for the "Transactions" table, i.e., it now identifies the chemicals in each transaction using [Product Code].
To identify an option in a transaction one needs to use [Product Code] and [Expiration Date of Option]
(3) When a chemical is optioned the company has to track the fact that that quantity of it is not available for outright sale until the option expires
(4) A further problem exists even if I have figured out how to structure the tables.
Before selling a chemical one must create a record for that chemical in the "Chemicals" table--not a problem.
Options on the chemicals, however, do not exist until they are sold i.e., instead of using the "Chemicals" table to look up data for each transaction. Each transaction (to sell an option) has to create a record in the table being used to track the options
I got a product table that I want to link with Customer Table.
To relate one to many I have a IDCustomer on my product table that is about 1000 rows.
I can link and all is working.
But in the product table I need a Primary Key that is shorteer in rows (only 15 rows) and access won't let me create such a primary key because it contains null value.
I Need Product ID (as primary key) to relate to another table which I will name Product details.
I am attempting to create a database for managing nonconforming product internally.
Different stages will need to be assigned to an individual within the organisation, is it possible to have a lookup value in my table which draws its information from active directory?
All the users are obviously in here and managed already, I dont want to have to create and manage a separate list of users just for my database.
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.
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?
I 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.
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...
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.
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
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.
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.
I wan't to calculate the price totals but for each instance of Uvoz ID separately. I've sucessfully used Dsum function but I managed only to calculate all price totals for all instances of Uvoz ID. Is it possible to do it for each number of Uvoz ID separately? How do I do it?
I have two excel tables that I linked in access. From excel tables I created query that gives me all data I need.
First table has data of periods and prices of apartment per period. For example:
PropertyID 1111 PropertyName Apartment Zagreb Mila StaringDateOfPeriod 1.1.2015 EndingDateOfPeriod 1.7.2015 PriceForPeriod 100 . . PropertyID 1111 PropertyName Apartment Zagreb Mila StaringDateOfPeriod 1.7.2015 EndingDateOfPeriod 31.12.2015 PriceForPeriod 120
Now as you can see I have apartments with prices that change per periods.
Second excel table gives me information of total paid service that customers need to pay , number of days that he will stay and CheckIn and CheckOut dates of those guest.
In calclutated fields in query I created calculations like TotalyPiad(from 2. table) - OwnerPrice*TotalyNumberofDays . I guest good result IF i have reservations that matches or is it BETWEEN StartDate and EndDate of periods.
BUT If I have reservation that is overlapping , where CheckOut of guest is 1 or more days > that EndDate of selected period I have problem with calculation because my calculation is based on WRONG price in that period.
For now what I manage to do is to create separate field in query where I calculated number of days that are out of range
CHECK_IN - StartingDate of period CHECK_OUT -EndingDate of period Check-in date - starting date of reservation of guest Check-out date - ending date of guests reservation nabavna cijena - owner price
But still when I calculate those number of days I multiple those number of days with old period.
So How can I MULTIPLE THOSE NUMBER OF DAYS WITH PERIOD +1 , I meen next period not this old one just for those days that are out of range? [CHECK_OUT] +1 doesn't work it just add extra day in my CHECK_OUT filed
My final idea is : Separately calculate in one field number of days that are out site of range Calculate number of days of reservation of guest that are in range = make sum of those two fields
From that I will create report which I will send to our owners of properties (number of reservations, reservations details, price that they need to pay to us ...)
I also thought this could be possible to make using VBA but I am still not skilled anough in it. Also I was thinking of creating calendar where I would that add those two queries but how to create simple calendar in Access. Is there some template for simple calendar in Access?
I have a problem with a form which I created for a table. The purpose of the form named OrdersForm is to simply add Orders in the table named TableOrders using labels such as Customer (combo box) Products (Combo box) Date (which controls the column Date in the table with a date function) Quantity (the user puts a value) Price (automatic) Totalp (as a Product between Quantity and Price)
My issue here is that when i use the form and enter an order Price and Totalp will not appear in columns in the table TableOrders.
