1 Item And Up To 5 Diffrent Prices,what To Do?
Dec 1, 2004
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
View Replies
ADVERTISEMENT
Dec 9, 2004
I Have a few diffrent querys that use diffrent part of the same tbl to lookup information, but they should always be the same same report just not all the customers will be present.
Do i need to make report for every query i got.
Thanks, mark
View 1 Replies
View Related
Mar 10, 2005
Hello all,
If i have a query as a Record Source of a form then i can't write to the fields i want, only if i have a table as a Record Source i can.
Any suggestions why might this is happening?
How can i add fields from diffrent tables in one form?
Thank you in advance!
View 3 Replies
View Related
May 16, 2006
Hi everyone!
I whant to make a append query that append data from a table that have a field formated as "text" to a table that is formated as "number"
Is that possible? I appreciate all answers.
Thanks in advance...Magnus
View 3 Replies
View Related
Aug 18, 2006
I have a form that simply lists the items:
DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE:
If IsNumeric(DEPARTMENT_NBR) = False Then
If DEPARTMENT_NBR <> "00" Then
MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly
DEPARTMENT_NBR.SetFocus
GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks)
End If
End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
View 4 Replies
View Related
Apr 28, 2005
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 Related
Jun 28, 2007
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.
Any thoughts on this would be great,
James
View 7 Replies
View Related
Oct 4, 2005
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
View 4 Replies
View Related
Oct 16, 2005
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. :)
View 2 Replies
View Related
Feb 20, 2006
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
View 1 Replies
View Related
Aug 31, 2007
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.
View 4 Replies
View Related
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?
View 4 Replies
View Related
Jun 20, 2007
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?
View 1 Replies
View Related
Oct 14, 2004
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.
View 4 Replies
View Related
Mar 24, 2007
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
View 1 Replies
View Related
Dec 6, 2013
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.
View 10 Replies
View Related
Dec 4, 2013
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.
View 14 Replies
View Related
May 28, 2013
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.
View 3 Replies
View Related
Mar 26, 2008
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
Mar 22, 2008
Look at this query:
http://i237.photobucket.com/albums/ff304/hankymskdghdfag/qryUvoz.jpg
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?
View 9 Replies
View Related
Dec 31, 2014
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
Code:
PreklapanjePerioda: IIf([Check-out date]>[CHECK_OUT];[Check-out date]-[CHECK_OUT];Null)*[nabavna cijena]
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?
View 11 Replies
View Related
Aug 14, 2013
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.
View 4 Replies
View Related
Nov 7, 2005
Dear List,
I have made a database of locations. Each location has a subform which lists the "products" at this location. There are only about 5 sorts of product, and these are in a look-up table.
I would like to add the price of these products, so that when I open a different form/report, I can see how much money is spent at each location, etc etc. When I add a new product to the list at the location, the price automatically is logged somewhere on the database. I would also like to add another product, which is delivery. The price shouldn't be visible on the subform of the location form.
The thing that bothers me, is that the database is already filled with data. How can I add these details so that existing products have prices attached to them without exporting and reimporting all the data? There are other linked tables which would suffer.
I've tried to look at the sample databases on Access but I am, sadly, none the wiser.
Many thanks,
Mrs Miggins
View 14 Replies
View Related
Jun 24, 2015
I 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?
View 14 Replies
View Related
Dec 21, 2006
Hey all.
I'm trying to get a combo box to populate the box next to it.
Basically pick a project number in the 1st box and have the project name show up in the second box.
I have it working, but the problem is that when it puts in the name of the project, it fills the whole column with that name, and not just the one box.
Here is what it's doing:
http://img156.imageshack.us/img156/2659/dbhm4.jpg
Here is the code im using.
Private Sub ProjectNumber_AfterUpdate()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
ProjectNumber.SetFocus
If ProjectNumber.Value > 0 Then
strSQL = "SELECT * FROM ProjectList WHERE ID = " & ProjectNumber.Value
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.ProjectName = rs("ProjectName")
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If
End Sub
Let me know what I can do.
Thanks!!
View 5 Replies
View Related
Feb 22, 2005
Hi
I have a field that contains some kinds of the student status, such as "Current", "Alumni" and "Expire". The table has already got a lot of records. How can I make a new item available to the field, for example, "Other", without entering a new status?
Like, "Student A" is "Current" but he is now "Other" status. When the user see his record, he/she needs to change the student status from "Current" to "Other". I don't want the user to enter "Other" manually. I want it to be available from a comb box at the first place. How can I add a new item into a field without entering a new student record?
Sorry, I am unsure that I explain enough ..
Thank you.
Gaufres
View 3 Replies
View Related
