UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Between Start End Date Returns Table Value, Access 2007    
 
   
birdlady77
post Feb 9 2015, 01:56 PM
Post#1



Posts: 81
Joined: 7-September 05



Hello!

I am fairly sure this question has been asked before, so feel free to point me to the relevant thread. Also, advanced apologies for too much information. I have never taken any classes to use Access, so I tend to elaborate my point (because sometimes I'm not sure of what I'm talking about). :-)

Finally, as I spent the three hours to write this, I think there may be something wrong with my table design for tblYear, which is why I am having so much trouble. But I'm not sure what the problem is. I have attached a sample of the database.

I have developed a database to store information related to our park's hunting program. What I would like to do is create a query that returns the SeasonID if a HunterID has a HuntDate between the StartDate and EndDate of that SeasonID. The tricky part is that the SeasonID is based not only on the StartDate and EndDate, but also on the RMLocationID. (FYI : RMLocationID is a general location, and LELocationID is slightly more specific. The data that is collected differentiates between locations, but for management purposes, a couple of locations can be lumped together.)

Thanks,
Piper

I have the following tables:

tblYear (This table has the Hunting Season Type, the Start Date and End Date of the Hunting Season, and the Location where the Hunting Season takes place)
YearID (PK - autonumber)
SeasonID (FK to tluSeason)
StartDate (Date Field)
EndDate (Date Field)
RMLocationID (FK to tluRMLocation)

tblRegistration (This table has the Date the Hunter Hunted and the Hunter ID Number)
RegistrationID (PK - autonumber)
HuntDate (Date Field)
HunterID (FK to a lookup table)

tblLocationTime (This table is related to the tblRegistration - a hunter can hunt in multiple locations in one day, and thus in multiple seasons - technically, a hunter could hunt in multiple seasons in the same location,
LocationTimeID (PK -autonumber)
RegistrationID (FK to tblRegistration)
LELocationID (FK to tluLELocation)
TimeIn (Date/Time Field)
TimeOut (Date/Time Field)
SWAG (Yes/No Field)

tblLocationLERM
LocationID (PK - autonumber)
RMLocationID (FK to tluRMLocation)
LELocationID (FK to tluLELocation)

tluLELocation
LELocationID (PK - autonumber)
LELocation (Text)

tluRMLocation
RMLocationID (PK - autonumber)
RMLocation (Text)

tluSeason
SeasonID (PK - autonumber)
Season (Text)



Here is an example of data in tblYear:

YearID SeasonID StartDate EndDate RMLocationID
561 Archery 10/1/2014 1/31/2015 North End
562 Archery 10/1/2014 10/15/2014 South End
563 Muzzleloader 10/16/2014 10/25/2014 South End
564 Archery 10/26/2014 11/13/2014 South End
565 Youth 11/14/2014 11/15/2014 South End
566 Archery 11/16/2014 11/28/2014 South End
567 Shotgun 11/29/2014 12/13/2014 South End
568 Archery 12/14/2014 1/16/2015 South End
569 Developed 1/5/2015 1/6/2015 Developed Area
570 Late Shotgun 1/17/2015 1/19/2015 South End
571 Archery 1/20/2015 1/31/2015 South End

Here is an example of data in tblRegistration:
RegistrationID HuntDate HunterID
14540 10/16/2014 3394
14544 10/16/2014 4530
14573 10/17/2014 4532
14600 10/17/2014 4333

Here is an example of data in tblLocationTime:
LocationTimeID RegistrationID LELocationID TimeIn TimeOut SWAG
16234 14540 South End 6:06 AM 7:30 PM No
16238 14544 North End: Walk-In 3:30 PM 7:45 PM No
16279 14573 South End 4:10 PM 7:22 PM No
16306 14600 North End: Boat-In 2:50 PM 7:34 PM No

Here is the tblLocationLERM:
LocationID RMLocationID LELocationID
1 North End North End: Boat-In
2 North End North End: Walk-In
3 Developed Area Developed Area
4 South End South End

Here is the tluLELocation:
LELocationID LELocation
1 North End: Boat-In
2 North End: Walk-In
3 Developed Area
4 South End

Here is the tluRMLocation:
RMLocationID RMLocation
1 North End
2 Developed Area
3 South End

Here is the tluSeason
SeasonID Season
1 Archery
2 Muzzleloader
3 Shotgun
4 Late Shotgun
5 Youth
6 Developed
Attached File(s)
Attached File  HuntDB_UA.zip ( 53.12K )Number of downloads: 6
 
Go to the top of the page
 
MadPiet
post Feb 9 2015, 02:13 PM
Post#2



Posts: 2,255
Joined: 27-February 09



FWIW, when I run into situations like this, I simplify things as much as possible and then add complexity as I go. Otherwise, I don't know where things start to fail.

"I have developed a database to store information related to our park's hunting program. What I would like to do is create a query that returns the SeasonID if a HunterID has a HuntDate between the StartDate and EndDate of that SeasonID. The tricky part is that the SeasonID is based not only on the StartDate and EndDate, but also on the RMLocationID. (FYI : RMLocationID is a general location, and LELocationID is slightly more specific. The data that is collected differentiates between locations, but for management purposes, a couple of locations can be lumped together.)"

So each location can have it's own start and end date for a sport season?
Go to the top of the page
 
orange999
post Feb 9 2015, 02:33 PM
Post#3



Posts: 1,713
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


My recommendation with which I know some will disagree is to NOT USE lookups at the table field level.
SeasonID in tblYear is such a lookup.

It seems your HuntingSeasons are for specific areas/regions and have a StartDate and EndDate

A number of Hunters have multiple HuntDates

sample
CODE
HunterID    CountOfHuntDate
4333    20
3322    14
4532    13
3899    12
35    11
4604    11
32    10
197    10
4530    10
28    9
3315    9
3901    9
1115    8


OOps: I've been looking at your tables, relationships and doing a few queries and I see madpiet has responded in more detail, so will just post a few observations.

Good luck with your project.
Go to the top of the page
 
birdlady77
post Feb 9 2015, 02:45 PM
Post#4



Posts: 81
Joined: 7-September 05



Hello!

Problem solved (I think)! I looked at my tblYear again and discovered that back in 1992 I had entered a Shotgun EndDate of 12/12/2015 instead of 12/12/1992.

The other fix I did was to normalize my tblLocationLERM.

I have attached a copy of the database with the fixes and what I believe is the working query.

Any comments for improvement are greatly appreciated!

Cheers,
Piper
Attached File(s)
Attached File  HuntDB_UA.zip ( 56.12K )Number of downloads: 6
 
Go to the top of the page
 
MadPiet
post Feb 9 2015, 03:18 PM
Post#5



Posts: 2,255
Joined: 27-February 09



QUOTE
"I have attached a copy of the database with the fixes and what I believe is the working query."


Generally, I test by using the minimum number of records possible, because I do my validation by hand. (Basically do the math myself and figure out what the answer should be, and then I run my query to see if i get the same answer.) If I do a few tests and the answers are always right, then I can safely conclude that my query is correct.

the other thing I like to do is to start really simple and model the core "thing" that my database is going to gather data about. Add some tables or whatever and test/modify until the database returns the answers that I know are right (so I start with some dummy data - no more than 3-5 records in any table). then I write down my expected results and compare them to what the queries returned. If I don't get what I expected, I know something is wrong - either with my query or my table structure(s).
Go to the top of the page
 
datAdrenaline
post Feb 9 2015, 07:57 PM
Post#6


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


This should get you started ... if I understood you correctely:

CODE
SELECT DISTINCT tblRegistration.*, tluRMLocation.RMLocation
FROM ((tblRegistration INNER JOIN (SELECT DISTINCT tblLocationTime.RegistrationId, tblLocationLERM.RMLocationId
  FROM tblLocationTime
  INNER JOIN tblLocationLERM
  ON tblLocationTime.LELocationID = tblLocationLERM.LELocationID)  AS vRegistrationLocations ON tblRegistration.RegistrationId = vRegistrationLocations.RegistrationId)
INNER JOIN tluRMLocation ON vRegistrationLocations.RMLocationId = tluRMLocation.RMLocationID)
INNER JOIN tblYear ON tluRMLocation.RMLocationID = tblYear.RMLocationID
WHERE tblRegistration.HuntDate >= tblYear.StartDate And tblRegistration.HuntDate <= tblYear.EndDate


But ... you really need to remove the lookup definitions from your table objects. Your clarity of data will grow exponentially! Also, it seems that your tables, while attempting to follow normalization guidelines, fall short in some areas. You may want to review the attributes of each tables and see if they belong elsewhere -- or maybe you don't need them. In short, revisit the normalization process!

I hope this gets you going on the right path!
Go to the top of the page
 
datAdrenaline
post Feb 9 2015, 07:59 PM
Post#7


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Ok ... that was weird ... I could SWEAR the other replies were not there when I started posting ... but the timestamp says otherwise! ... My SQL is based on the db from the first post.
Go to the top of the page
 
birdlady77
post Nov 22 2017, 10:24 AM
Post#8



Posts: 81
Joined: 7-September 05



Thank you for your help! I had to step away from this project two years ago, and I've finally gotten back to it. Your code did the trick!

Thank you!
Piper
Go to the top of the page
 
orange999
post Nov 23 2017, 01:49 PM
Post#9



Posts: 1,713
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


We all admire your persistence. Thanks for the status update.

--------------------
Good luck with your project!
Go to the top of the page
 
datAdrenaline
post Dec 9 2017, 10:08 PM
Post#10


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Thank you for the followup! ... Made me smile with the time frame! ... also made me sad that two years was like a blink as I remember this thread and it does not seem that long ago that we were in the thick of it!

--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 11:12 PM