Breakout Query For Prices

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 Replies


ADVERTISEMENT

Breakout Query

Oct 5, 2007

I have a table with the following Fields:
Symbol
DeliveryMonth/Year
DDate
Close

I am trying to evaluate today's close in relation to the close from the previous 20 trading days to see if it is a new High. I can create a query that looks at the contracts between date()-21 and date()-1 to get the last 20 days. Then I need to see if today's close is greater than the maximum from this range. This is where I need some help. I thought I could use:

DDate:
Criteria: date()

Close:
Criteria: >(Select MAX([Close] where date between date()-21 and date()-1) - The name of the Date field is DDate since "date" is a keyword in access. I wasn't sure if I needed to put the name of the field here or the word date?

But this doesn't quite do it. It is the max of a range criteria that is messing me up. Can you guys help me to get this to work?
Thanks for your help,
Tyler:)

View 1 Replies View Related

Queries :: Calculate Prices For Reservations Using Query

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

Adding Prices

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

Update Prices

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

Lock Old Prices From Updating

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

Access Development Prices

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

Computer Configurator - How To Add Prices Together?

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

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 3 Replies View Related

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?

View 4 Replies View Related

Product Sizes And Differing Prices

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

Date Range For Prices - How To Handle?

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

1 Product Base, 30 Customers, Different Prices For Each

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

Queries :: Available Prices At Certain Date For All Products

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

General :: Multiple Prices Per Product

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

Restricting Updates Of Product Prices

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

Concerning Prices: Hourly Rate / One-time Amount

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

Calculating Total Prices Only For Specific Records

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

Forms :: Prices And Totals Will Not Show In Table?

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

Adding Prices To "products" In A Database

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

General :: Database For Metal Market Prices To Be Used In Another Database

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

Query In Forms Question (printing A Single Selected Query Record From A Form?)

Oct 19, 2004

What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?

I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.

So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.

Thanks in advance

Todd

View 1 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Opening Design View Of Append Query Without Timing Out Source Query

Mar 4, 2015

In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.

All is well and fine until source data changes and the pass-through query runs too long and times out.

If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table

Is there a way to open an Append or Make-table query in Design view without invoking the source query?

View 1 Replies View Related

UNION Query MS Jet Database Engine Cannot Find The Input Table Or Query.

Oct 28, 2005

Hello All,

I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".

Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.

I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID

Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)

View 9 Replies View Related

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)

I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.

I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.

View 5 Replies View Related







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