Date To Price Relationship Help

Nov 2, 2005

I am building a database in Access which I would like to eventually link to my website in order to sell hotel bookings online. I believe I have set all the fields up properly but when it comes time to set the price I'm lost. Each hotel has about 3-5 different prices throughout the year depending on the date. How can I design a table(s) to accommodate this? Any help is appreciated.

View Replies


ADVERTISEMENT

Price Based On Latest Date

Oct 28, 2004

Hi guys

I am trying to create a simple database to keep track of computer components that I order for custom PCs. The data is fairly basic Quantity, Description, Stock Code, Delivery Status, Cost, Cost+VAT, Date Delivered. The list is currently 500+ rows of data. I simply cut and paste this info directly from my online invoices.

What I want is to be able find the the last price paid for an item (often the same items have different prices week by week) Eventually I would like to be able to create a rough quote using the latest prices (plus mark up) and also view a price history.

I would also like to include a few pictures as URL links to the suppliers website ????

At present the data is in Excel and linked to a single table in Access (but this can be changed). I realise that I have to group the records somehow but cannot get it to work. At present it lists every record either in date order or product order.

The stock code should not change, but product descriptions do. So any links to pics would be based on stock codes

Someone please tell me how to display the full list of products but only the latest price/date

If you think the current format is rubbish then feel free to suggest an alternative. I much prefer Excel but have never really got to grips with Access (used to be a vba programmer with Excel). To link to pictures and hide data that customers might see Access seems the obvious choice. So I try again!!!!!

Thanks

Steve

View 4 Replies View Related

Queries :: Item Without Price Or Duplicate Price

Jul 29, 2013

I have a table called Books, in that table there is 4 columns ChapterName, Auther, ITEM, Price.

Each book has a item number, and each book has a few records with the same data, just the first column is different where its the ChapterName, each book has a price, but only once, meaning in the first record of each book it will be a price in the column price

Now I want a Query where i can get which book dont have a price at all, and which book has more than once a price, how can i do that?

View 1 Replies View Related

Show Item With Last Updated Price By Date

Aug 14, 2005

I need a (group by?) query that shows my items only by the last updated price by date.

Basically i need to show the last date. (MaxDate?)

I keep a database for my restaurant of my food items and change their price everyday. i now have a table that archives individual items by date, price and company in a subform.

Item_id, Item, date, cost , unit, Company

1 asparagus, medium8/12/2005$1.45poundEuro Gourmet
1 asparagus, medium8/13/2005$2.00 poundEuro Gourmet
55 oil, blended 8/12/2005 $4.99 gallon accardi
116 polenta 8/12/2005 $1.58 box accardi

I am trying to kick out an item or inventory sheet by creating this query but i cant seem to group by last date. i have all my items showing up multiple times (as many as i entered)
i have been easily able to create an inventory report off a query i used with only one date that i would change. but now i want to archive prices for charts.

i have tried max dates on item_id fields and date fields and nothing seems to work.

Thank you

View 3 Replies View Related

Forms :: Using Combo Information To Look Up Price Via Date?

Dec 27, 2013

I have a database I'm creating for DVD/blu-ray sales. I currently have a order system in place in a subform, which uses a combo box to find the customer and fills in all the information in the rest of the subform. One piece of information is the date of sale.

I need to be able to have the ability to change the price of the DVDs and have a history of sales with the right price. The easiest way to do this seemed to be by Date of sale. So the price is linked to the date of sale, when the price changes, all orders before that change use the old price, after use the current price.

I was wondering if there is anyway to have a second subform on the same page and us the date of sale from subform1 and have it display the price in the second subform?

View 7 Replies View Related

Product Price Auto Updates Using Affective Date

Dec 9, 2007

Please find attached a mdb (2000) which includes 2 tables and 2 querys

If you open "QryProductsComboSelections" You will see that some product ID's have duplicate entries.

what I am trying to do is allow for contract pricing updates to be added with an affective date so they will not display until the affective date but also those old contract prices that have a date less than the current affective date will also be hidden thereby always limiting it to one record even if the product only has one pricing entry.

I can do this with a boolean "PriceClosed" but it would only be a last resort as I would like to automate this so it requires no user intervention other than adding the new price for A given date.

I have spent hours playing with this so rather than through the comp out the window Thought I would do the sensible thing and come and ask the experts.

Hope somebody can help as done a search and got loads of unrelated results.

Best wishes

Mick

View 5 Replies View Related

Queries :: Average Price Of Last 5 Line By Items By Recent Date

Mar 4, 2015

Any way to filter the average price of of a Product within the last 5 occurences (Line Items). It would pull a week back so WHERE: Between Now()-7 and Now(). Example:

Code:
PARTID | Price | Date
--------------+-------------+---------
111223344 | 5 | 3/1/2015
111223344 | 7 | 3/2/2015
111223344 | 8 | 3/4/2015
111223344 | 10 | 11/22/2014
111223344 | 20 | 10/1/2014
111223355 | 5 | 2/5/2015
111223355 | 6 | 2/1/2015

to:

What I want:

Code:
PARTID | avgPrice | MinDate
--------------+----------------+-------------
111223344 | 10 | 10/1/2014
111223355 | 5.5 | 2/1/2015

View 4 Replies View Related

Queries :: Show Price Valid On Specific Date Based On Two Parameters

Mar 30, 2015

I atrying to make a query that shows the price for a product, based on two parameters.

Parameter one is a product code.
Parameter two is a date. This date falls between two dates.

I have one list (table) where is product code and invoice date.

The second list (table) I have, contains product code, and price valid from date, and price valid to date columns. This price valid to date is often not filled, and the price I still valid as we speak. If the date is filled there is often a new entry with an updated price. But sometimes, even if there is a new entry in the table, the date 'valid to date' is sometimes also not filled.

I would like Access to show me the valid price for the specific product. What criteria should I give in the macro, in order that Access shows what I want?

For illustration purposes, a small overview of my table:

Product code, Price, valid from, valid to
AAAA, 12000, 01.01.2012, 31.12.2012
BBBB, 16600, 01.01.2012, 12.06.2013
AAAA, 13500, 01.01.2013, 28.08.2013
AAAA, 11500, 29.08.2013,
BBBB, 17600, 13.06.2013,

Product, invoice date, price according to price list
AAAA, 02.05.2012, ????
AAAA, 01.08.2012, ????
BBBB, 10.06.2013, ????
AAAA, 31.10.2013, ????
AAAA, 16.11.2013, ????

If you happen to know how this search can be performed in Excel, I am of course also happy to read that. (But my index, or Vlookup functions, give only the first possible result in the table. As I do not know how to give in the date parameter.)

View 11 Replies View Related

Queries :: Getting Transport Rate Based On Date Ranges Due To Fuel Price Floats

Jan 1, 2015

I have tried search through some forums and but still not able to get desire results.

Table Customer Agreed Transport Rate
Customer > Eff Date > Route > Rate
A > 1 June 2014 > AAA > MYR 99
A > 15 Aug 2014 > AAA > MYR 88
A > 1 June 2014 > BBB > MYR 77
A > 1 Sept 2014 > BBB > MYR 66
B > 5 June 2014 > CCC > MYR 88
B > 20 Oct 2014 > DDD > MYR 100
C > 1 Oct 2014 > EEE > MYR 222

If there is order from Customer A for Route AAA loading on 2 July 2014, I can easy to get answer "MYR 99".

How if I wish to make Route as primary key? (as I understand, Primary key must be unique and no repeat).

View 10 Replies View Related

Whats Your Price?

Jan 2, 2006

After being a member on this site for a while now I would like to get some opinions on "cost of application development" I know the forum covers a wide geographic area and the members cover a wide spectrum of degrees of expertise on Access and application development. So the question is this..What would your rates be and how would you determine your charges for developing Access applications for a business?PS... Please include your location if not in your profile.

View 13 Replies View Related

2 Price Fields

Jul 9, 2007

Hi

I have a problem which hope will have a simple solution, which has been
driving me mad all day

I have a client who has two prices for the same service depending on speed
of turnaround
These prices are entered into the Client table seperately using the CDetails form.
I then wish to use this information using a combo box for selection within
another form called SDetails.However I can only get the combo box within
SDetails to put one of the two prices rather than either of the two choices,
because of the bound column within the combo.
The combo when pulled down looks something like this

Price 1 Price 2 Client Name

I would prefer for

Price 1
Price 2

so that I have the choice of the price I want to choose.

Having spent time and many brain cells, I have ground myself to a stop.
HELP!!!!!! if only for the sake of my sanity

View 1 Replies View Related

Price Lookup

Oct 13, 2007

I am creating a db that handles orders.

My problem is that the products being sold are so at different prices depending on the customer. (e.g I am selling 100 burgers to Mcdonalds for £50 whereas to Burger King I am selling them for £35).

Therefore I have split my 'Products' table into two. A 'Products' table which contains the product name and category. And a second 'Prices' table which contains the Product name, Customer and the price.

I have setup a relationship between the two tables based on product name

How to I go about looking up the different prices depending on which customer I have placed an order for?

can it be done in a table or do I need to create a query to sort it all out.

View 8 Replies View Related

Nth Smallest Price By Row

May 15, 2007

I am in need of some assistance. I've been looking on here for an answer, but can only find solution that would give me a by column lowest price, and I need to compare the prices by row. What I have is a table of vendor prices for part numbers we sell. What I want to do is compare all the prices the vendors for each part number and return the lowest and 2nd lowest fields in a query. Here is an example...

Part # vendor 1 vendor 2 vendor 3 Lowest Vendor Price Low Vendor #
10526 .75 .97 .67 .67 3
10527 .60 .82 .85 .60 1


Thanks for any ideas....

View 4 Replies View Related

Adding Price History

Sep 11, 2005

ok i didnt know what section to put this in so i put it here. what i wanna do is add a price change history to my database. for example is the price of an item is changed in my database i want to be able to see what the previous price was and when it was last changed, either in forms of reports doesnt matter i just need somewhere to view this info. problem is i have no idea where to start.

any help provided is muchly appreciated thanks.

View 4 Replies View Related

How To Create A Price List

Jan 18, 2005

Hi all,

Before I start, please know that I am what you call a newbie to a certain extent even though I created in the last 3 years a very complex and efficient database for my business.

This is going to be hard to explain, but I'll try.

The time came to stop creating the Price List in Excel and copy and paste in Access.(mainly to make it easy for other person to maintain and understand)

I know more or less the tables that will need to be created.

My problem is how to store calculated fields in a table (I know I really shouldn't, but how else can I accomplish this), since all the costs and selling prices are the result of complex formulas.

If this information is not enough to understand my question please let me know.

Thank you for any help.
Emilio

View 10 Replies View Related

Tables For Price List?

Jan 22, 2005

Hi all,

I started a new thread, because it is a new subject even though is related to my Price List thread, I hope is OK.

If someone can please take a look at my attached Excel Price List (particulary the factors sheet) and give me an opinion of how my tables should be created.

I cleaned the Price List was too big to attach and it will be easier to be understood, in its entirety is kind of all over the place, exactly why I need to make simpler in Access for another user to update if I am not around.

I am below including what I think the tables should include, but not sure exactly how they should be, please be reminded that I am un unexperienced newbie.
----------------------------------------------------------------------

tblSuppliers

SuppliersID
SupplierName
SupplierCode

tblMarkup

MarkupID
MarkupName
MarkupAmount


tblTaxes

TaxID ------Do I need This?
TaxName
TaxAmount

tblExtras

ExtrasID
ExtrasName
ExtrasPrice
SupplierID

tblDiscounts

DiscountID
DiscountName
DiscountAmount
SupplierID
----------------------------------------------------------------------

Thanks for any help,
Emilio

View 6 Replies View Related

Extended Price Subform

Feb 5, 2005

I am stuck for a calculation expression for 'extended price' which can be seen in the attached screenshot.

I want to times item price by quantity and deduct the discount.

Thanks. :)

View 4 Replies View Related

Two Field For The Price Of One Combo

Sep 5, 2005

I have a combo box which looks up vessel names, it stores the Vessel ID in a field [Vessel ID]. This all works fine, no problem, easy. However as part of the vessel details I also need to know the service the vessel is providing “transhipment” or “direct”. If it is a “transhipment” service I need to show a second Combo box with the on carriage vessels. That’s also easy, I just hide the second combo box and only show it depending on the service status of the vessel.

Now I figure I just need to have a field [Vessel Status] which is updated after the first combo box is updated and then depending on the value show or hide the additional combo box. The problem I’m having is updating the [Vessel Status] field once the first combo box has been update.

View 1 Replies View Related

Look Up Price From Another Table On Form

Jun 13, 2006

Ok, having a very beginer problem here (I Think)

Here are my relevant tables:
I have a table called Items which has itemCode (primary key), itemDescription, and price.
I have a table called bidItems which has bidCode (primary key), itemCode (primarykey), and some other info
I have a table called Bids witch has a bidCode (primary key), and a bidDescription

Here is a form that I use to enter data into these tables and (want) to view prices and totals:
I made a form based on the Bid table with a continuose subform based on the bidItems table, with combo boxes that lets you select Items by itemDescription (then it stores itemCode automatically with the correct bidCode)

What I can't figure out how to do is get each item's price to automatically display on the form (remember price is in Items table, not bidItems). I've tried a number of things that havn't worked out.... any ideas. Then, ideally I would like the form to display the total of all the item prices. (if you have a pointer on that as well it would be much appreciated)

Thanks

View 1 Replies View Related

Discount Price Query

May 27, 2006

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

View 2 Replies View Related

Last Price Of Month Query?

Apr 1, 2015

We have this database:

It has Symbol on the first column, Date (daily on trading days) on the second and Close on the third.

We want a query that shows the last CLOSE of the month for each symbol and sum the volume of the month.

The query table would be like this:

Symbol
Date (last date of the month)
Close (last date of the month)
Volume (sum of whole month)

Here's the sample file:

Sample.accdb

View 9 Replies View Related

Obtaining Latest Price Data

Jul 23, 2007

Hi,

I have a table containing the following fields;

ProductID (UniqueID)
Date
Price

Each day some, but not all, of the products in this table have their prices updated.

When I write a query how can I ensure that the latest price is recalled ?

Any tips greatfully received.

View 2 Replies View Related

Price Calculation In Access Like Excel

Dec 11, 2007

Hi all,

Help me solved my problem with price calculation in Access.

Please kindly view below excel file.

http://www.hwakeat.com/templates/AT05HKGP01/pdf/Products.xls


I like to do formula price calculation in Access like my sample excel file. How many table should I create & How to build query? Whenever exchange rate change or MarkUp price rate change, I only want to type once & it will affect the whole access.

I have tried so many ways in Access but it doesn’t work like my sample excel file.

Please Help me!!

View 1 Replies View Related

Assign A Cost/price Value Toodrop Down Box

Jan 17, 2008

Hi all,

Might be in the wrong place but didnt know it it needed to be in query, form or vb sorry admin.

I need help, what i need is a way of assigning a price too a drop down menu.

Basically its a Form with a number of drop down menus where you can select different varibles colour, finish for a certain product, but theres no method to generate a total cost of selecting different variables.

Do you know of a soloution becuase ive exhausted my capacity!!

Thanks

View 5 Replies View Related

Import Stockquote/ticker Price ....

Sep 15, 2005

how to import stockquote/ticker price directly into a access table, also
if with throu excel or an excel interfcae?

View 3 Replies View Related

Newby Link Price Fields

Feb 20, 2006

So I have a table "InventoryTracker", with one field lookup set to list (drop down) and the source is another table "inventory", field "Item". This works good and all the items that I enter in the "inventory" table are available from the "InventoryTracker" table. Both "inventory" and "InventoryTracker" have a field called price.

Heres what I want to have happen.
When I select a item from the drop down list menue in "InventoryTracker" it automatically fills in the "InventoryTracker" price based on the price from the "inventory" table (for the item with the same name, the one I just selected). This has to be fairly straitforward, I can do it in excell, but not acess.

Can anyone help a newby out?:rolleyes:

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved