Selecting Data Based on Multiple Date Range Fields

[2stew]

I have 2 tables:
1) Data Pull-Table

• AcctDate
• Location
• Property

2) Property-Table

• LOC
• BeginDate
• EndDate
• OSShare (which is the percentage)

I need access to find the Percentage based on:

• Location must match LOC
• AcctDate must be between BeginDate and EndDate

Example: Table 1: the Acct Date = 8/2017
Table 2: has 3 entries for Location/LOC 0124

• BeginDate: 1/2015 & EndDate: 3/2015 at 33.687%
• BeginDate: 4/2015 & EndDate: 5/2015 at 36%
• BeginDate: 6/2015 & EndDate: 1/9999 at 38.5973%

Correct Answer is 38.5973% (If it can't find the LOC then the % = 0)

I created a join between Location and LOC as "includes all records from Table 1 and only those records for Table 2 where there is a match".
I created and IF formula but the results are creating records record in Table 2.

Example Results:

• BeginDate: 1/2015 & EndDate: 3/2015 is 0%
• BeginDate: 4/2015 & EndDate: 5/2015 at 0%
• BeginDate: 6/2015 & EndDate: 1/9999 at 38.5973%

Here's what the SQL looks like:


SELECT [Copy Of 02-1_Acct Recon 232010-Data Pull].Location, [Copy Of 02-1_Acct Recon 232010-Data Pull].AcctDate, [Copy Of 02-1_Acct Recon 232010-Data Pull].Location, [Property Listing with Percentages].BeginDate, [Property Listing with Percentages].EndDate, IIf([Location]=[LOC] And [AcctDate]>=[BeginDate] And [AcctDate]<=[EndDate],[OSShare],0) AS OSSharePercent
FROM [Copy Of 02-1_Acct Recon 232010-Data Pull] LEFT JOIN [Property Listing with Percentages] ON [Copy Of 02-1_Acct Recon 232010-Data Pull].Location = [Property Listing with Percentages].LOC;

How do I fix this?

[ranman256]

make a form with 2 text boxes: txtStartDate and txtEndDate.
make a query to pull the data based on the dates:

qsDataDateRng = select * from tData where [AcctDate] between forms!myForm!txtStartDate and forms!myForm!txtStartDate
qsPropDateRng = select * from tProperty where [beginDate]>= forms!myForm!txtStartDate and [EndDate]<= forms!myForm!txtEndDate

now you can make % queries using these 2 queries above.

[pbaldy]

You can join the tables with a non-equi join:

SELECT...
FROM FirstTable F INNER JOIN PropertyTable P ON F.Loc = P.Loc AND F.DateField >= P.StartDate And F.DateField <= P.EndDate

note that this can't be represented in design view, so you have to use SQL view.

[2stew]

Tried using your coding and it ran but it was still duplicating. Interestingly, when you go back and look at the SQL it drops AND F.DateField >= P.StartDate And F.DateField <= P.EndDate. I reenter it but it keeps dropping.

[pbaldy]

What's the full SQL? Like I said, you can't view that in design view. It will drop the join because it can't represent it.

[2stew]

Here it is
SELECT [Copy Of 02-1_Acct Recon 232010-Data Pull].KeyId, [Copy Of 02-1_Acct Recon 232010-Data Pull].AcctUnit, [Copy Of 02-1_Acct Recon 232010-Data Pull].AcctPeriod, [Copy Of 02-1_Acct Recon 232010-Data Pull].AcctDate, [Copy Of 02-1_Acct Recon 232010-Data Pull].Module, [Copy Of 02-1_Acct Recon 232010-Data Pull].Type, [Copy Of 02-1_Acct Recon 232010-Data Pull].Date2, [Copy Of 02-1_Acct Recon 232010-Data Pull].JeNbr, [Copy Of 02-1_Acct Recon 232010-Data Pull].Ref, [Copy Of 02-1_Acct Recon 232010-Data Pull].Activity, [Copy Of 02-1_Acct Recon 232010-Data Pull].Location, [Copy Of 02-1_Acct Recon 232010-Data Pull].Property, [Copy Of 02-1_Acct Recon 232010-Data Pull].AcctCat, [Copy Of 02-1_Acct Recon 232010-Data Pull].Description, [Copy Of 02-1_Acct Recon 232010-Data Pull].Amount, [Copy Of 02-1_Acct Recon 232010-Data Pull].Clear, [Property Listing with Percentages].BeginDate, [Property Listing with Percentages].OSShare
FROM [Copy Of 02-1_Acct Recon 232010-Data Pull] INNER JOIN [Property Listing with Percentages] ON [Copy Of 02-1_Acct Recon 232010-Data Pull].[Location] = [Property Listing with Percentages].[LOC];

[pbaldy]

That doesn't include the date fields. Can you attach the db here to play with?

[ssanfu]

Just wanted to point out that you have several reserved words as field names:

[Copy Of 02-1_Acct Recon 232010-Data Pull].Module
[Copy Of 02-1_Acct Recon 232010-Data Pull].Type
[Copy Of 02-1_Acct Recon 232010-Data Pull].Ref
[Copy Of 02-1_Acct Recon 232010-Data Pull].Property
[Copy Of 02-1_Acct Recon 232010-Data Pull].Description


Problem names and reserved words in Access

[Micron]

Yikes. You might want to consider using alias names if you have a lot of this sort of sql. Something like

SELECT T.KeyId, T.AcctUnit, T.AcctPeriod, T.AcctDate, T.Module, T.Type, T.Date2, T.JeNbr, T.Ref, T.Activity, 
T.Location, T.Property, T.AcctCat, T.Description, T.Amount, T.Clear, P.BeginDate, P.OSShare FROM 
[Copy Of 02-1_Acct Recon 232010-Data Pull] AS T, INNER JOIN [Property Listing with Percentages] AS P ON T.[Location] = P.[LOC];

[ssanfu]

'Zactly what I thought. But I used "A" and "P".

FROM [Copy Of 02-1_Acct Recon 232010-Data Pull] A INNER JOIN [Property Listing with Percentages] P ON A.[Location] = P.[LOC];

"A" for "Acct" and "P" for "Property".

And you beat me (again) posting about this. (I forgot to add it to my previous post)

[pbaldy]

Aliasing, why didn't I think of that?

SELECT...
FROM FirstTable F INNER JOIN PropertyTable P ON F.Loc = P.Loc AND F.DateField >= P.StartDate And F.DateField <= P.EndDate

[2stew]

Sorry been away and just getting back to this. I don't know what "Aliasing" is. Could you please explain.

[2stew]

I finally got it! THANKS TO EVERYONE WHO REPLIED!