Between 2 dates (1 Viewer)

[Infinite]

Hello! I have this query:

Work: First(IIf([Date]>[RStartDate] And [Date]<[REndDate],[Price]*[Quantity],[RCost]*[Quantity]))

And I will explain what I want it to do, and upload a small database so you can see what it should do.


If you open the database, you will see the qryItemRetail. That is the main query for now. In it, you can see the rows. The row named "Work" Is what I want to work. If you look in the tblItemShowSales, you will see I have 8 Sales. That means, in all the query's, there should not be more then 8 records. Or so one would think. What I want is to get the Retail price of each item. In the qryItemsSold I have total sales of the items. And in qryAllItemsSold, I have the totals and the names sorted out to the correct items, so I only have there totals. The problem I am having is in qryItemRetail.

If a items sale falls between the start and end date in tblItemRetail, then that is what the Quantity sold should be multiplied by.
So, as you can see, I sold a total of 3 "22 Pistols" on 3/31/2012. If we look at the start and end dates in tblItemRetail, we see that falls between the 1st record. And that records price is $4. So the total retails for that should be $4*3. That would give us $12 for the 22 pistols retail for that show. The problem im having, is that as I have 2 records for the 22 Pistol, the qryItemRetail gives me 2 records. So even though I have a total of 8 sales the qryItemRetail gives me 12 records. The reason being its giving me a record for each item that falls between the dates. I have 3 records.

Its confusing me, and im not sure why its not working. Any help would be appreciated! Thanks!

 

[arnelgp]

you should put ModelID in both tblItemRetail and tblShowSales in that way you can related it to tblItems. Relating to Model, which is a description of item does not guarantee uniqueness of records.

 

[sneuberg]

It would be better to get what you without having to resort to aggregate functions but I could not find a solution doing that. So one way to do this is with DLookups; basically just grapping the data you need by brute force. I demonstrate how to do this in the attached database. If you look at the query qryDLookupOfPrices you will see I get the RCost from the tblItemRetail with the following DLookup:

RCost: DLookUp("[RCost]","[tblItemRetail]","[RStartDate] < #" & [Date] & "# AND [REndDate] > #" & [Date] & "# AND [RItem] = '" & [Model] & "'")

One thing to note here is that this does not include the start or end dates. I’m just going by the code you posted which wouldn’t catch them either. Change these to less than or equal (<=) and great than or equal (>=) if you want them included. This DLookup returns null if nothing is found. That’s good.

To get the regular prices I use the following DLookup

Price: DLookUp("[Price]","[qryModelNameToPrice]","[ModelName] = '" & [Model] & "'")

This DLookup uses a query, qryModelNameToPrice, I created. This query simply maps the various model names to the prices. This DLookup returns a price for each record, which it should.

So to get where you final want to go it's just a matter of substituting the Price where the RCost is null and this is done with the following expression:

Work: IIf(IsNull([RCost]),[Price],[RCost])

I included another column (RCostCount) in this query which counts the number of records that satisfy the conditions for the RCost. I did this to show a problem that needs to be resolved. Note that RCostCount shows 2 for one of the records. This is because there are two records in the tblItemRetail which have intervals in which the purchase date occurs. The Dlookup picks the 8 dollar over the 11 dollar one I guess because it comes first the primary key order. If you want to have overlapping intervals for the same model you will need to establish additional criteria to determine which record is selected. If you are going to disallow them in the table, then here’s a handy function that determines if intervals overlap.

'Returns True if the interval a0 ñ a1 overlaps interval b0 ñ b1
'It is assumed that a0 <= a1 and b0 <= b1
Private Function IntervalsOverLap(a0 As Date, a1 As Date, b0 As Date, b1 As Date) As Boolean

IntervalsOverLap = a0 <= b1 And b0 <= a1

End Function

I didn’t format any of the Dlookup results. I figure you know where, how and when to do that.

 

[sneuberg]

While my previous post might solve your immediate problem, I have to agree with arnelpg in that your structure seems a bit off. Does it make sense to have the price of an item related to the ModelID but have the RCost related to the model name? That would mean you could have a different RCosts for 22 comboy pistol and 22 comboy, but you would only have one price. Do you want that? If not I suggest using the ModelID in the tblItemRetail instead of the model number. If you need help adapting the DLookups in my previous post to that structure let me know.

Also I suggest changing the name of the Date field in the tblShowsSales to something like SaleDate. It's probably best not to use reserve names.

 

[Infinite]

The reason I need to have the ModelName connected the the Model is because the model is what I input from copy and pasting large amounts of show sales, and they dont have a model ID with them, so I have to output the Model Name. Then the reason in qryAllitemsSold that is is connected to the tblItemOtherNames is because, as the name implies, the items have other names, and then from there, there tied the the model ID, so I can see the total sales for Model ID 1, and Model ID 1 = 22 Pistol, 22 Cowboy pistol, etc, etc.
I have been really busy, and have yet to try what you said. I will attempt to try those, but with New Years Eve I dont think I will be able to.

Speaking of that, Happy New Years!

 

[Infinite]

I download your modified query, and it seems to do exactly what I want.

I have now imported your table, im going to see how that goes.

After changing nothing, it works fine
I was able to get everything working fine so far, ill post if anything happens.

Until then, Thanks a Ton for the help! Hope you have a Great New Years!

EDIT: Yes, but you did change the database. For the better yes, but it was changed. I am in NO WAY saying that I dont like it, or anything of the sort.

 

[sneuberg]

I download your modified query, and it seems to do exactly what I want.

I have now imported your table, im going to see how that goes.

After changing nothing, it works fine
I was able to get everything working fine so far, ill post if anything happens.

Until then, Thanks a Ton for the help! Hope you have a Great New Years!

I didn't change the tables from what you posted.

Same to you in the New Year

 

[Infinite]

I am having a new problem. For some reason running that query really hoses up Access, most of the time ending up in a crash. And im not sure. If I dont include the query result "Work" then it works fine. Other wise it hoses up.

 

[sneuberg]

Is it really hosed or crashed or just very busy? If your database has a lot of records this scheme may not work as aggregate functions inside queries can cause performance problems. If this the case then, we'll have to see if your data can be restructure so as to avoid these aggregate functions.

I can't help today as I'm in the middle of fixing my car port which is about to come crashing down. For now I suggest you make a copy of your database and try the query with less records. Also see if you can upload your system here or find a place where you can. I'll try to find out what's going on first thing tomorrow.