Find a price for particular date range in Access 2010

JGarza

Board Regular
Joined
Mar 26, 2002
Messages
93
I am using Access 2010.

I have two tables, one with date ranges and prices and another table with a listing of sales by date.

I need to build a query that give me a list of sales for the price in the date range. So...

Table 1
Start Date End Date Price
1/1/2012 1/7/2012 $5.25
1/8/2012 1/12/2012 $8.95

Table 2
Load# Date
25 1/3/2012
26 1/10/2012

Query
Load# Date Price
25 1/3/2012 $5.25

So, because the load#25 from Table2 date falls within 1/1/2012 and 1/7/2012 of the Table1 it will get a price of $5.25.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Ummmm...I dont know how to use sequel... Is there a similar way to do it with a query?
 
Upvote 0
I was thinking of an Iif statement. The "Start Date" is actually the first day of the week. So i was thinking of something like If the "Load date" is >= to the "StartDate" and <= to the "End Date" then Price.

Any ideas for something similar?
 
Upvote 0
Ummmm...I dont know how to use sequel... Is there a similar way to do it with a query?
Sure, you do, you just don't know that you know!

When you create a Query using the Query Builder in Access, all that is is a GUI representation to build SQL code.
- The SELECT fields are the fields you elect to show in your Query.
- The FROM source is the Table or Query you are using as the Data Source in your Query.
- The WHERE values are what you enter in your Criteria row under each field.

If you build a simple Query in Access, try switching to SQL view, and you will see the SQL code that you just built. Likewise, if you write SQL code directly and paste it into the SQL window, much of the time you can switch back to Design View and see what it would look like in Query Builder (caveat: there are some complex SQL queries that cannot be represented in Query Builder).

So, armed with this knowledge, there are a few ways you should be able to go about doing this:

1. Study the link Bob sent you and look and see what criteria is being put on what fields. Go into Query Builder, and follow the same logic to build your query.

- or -

2. Copy the SQL code from the link Bob sent, and replace the tables and field names with your table and field names. Start a new query, switch to SQL view, and copy and paste that SQL code there.

Give it a shot and see how you do. If you run into problems, post back here and copy and paste the SQL code from the query you tried to create.
 
Upvote 0
Hi,
In your query, bring in "Load #" and "Date" from table 1. Create a new column with the following syntax:

Effective Price: iif([table 2].[date] between [table 1].[start date] and [table 1].[end date], “Yes”, “No”)

Bring in both tables 1 and 2 into this query, but do not link the two tables. In the criteria of the column you just created, type in "Yes". As long as there are no overlapping date ranges, you will get a unique price for every load # and date.
 
Upvote 0
Hello

I tried your code and Access 2013 keeps making the Yes and NO into fields. I have the same situation as OP but not sure if this solved.

I always get this
Code:
IIf([testDates]![AdoptDate] Between [TestDateRanges]![StartDate] And [TestDateRanges]![EndDate],[“Yes”],[“No”])

where "Yes" turns into ["Yes"] and cannot remove them.

Any ideas?

I would think finding if a date is in a date range would be a common thing.

Thanks.
 
Upvote 0
Ok, I think I found what works for me.

Thanks to BobLarson's post above and his link to http://www.baldyweb.com/OverLap.htm

I have a date and I need to see what Fiscal Year range it is in and then pull the Fiscal Year number.
Code:
SELECT 
testDates.AdoptDate, 
TestDateRanges.FY, 
[testDates]![AdoptDate]<=[TestDateRanges]![EndDate] And [testDates]![AdoptDate]>=[TestDateRanges]![StartDate] AS Expr1

FROM TestDateRanges, testDates

WHERE ((([testDates]![AdoptDate]<=[TestDateRanges]![EndDate] And [testDates]![AdoptDate]>=[TestDateRanges]![StartDate])=-1));

The trick is the -1 criteria which limits the many to many join.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top