How to query a table for previous 30 day records and count the number of days stayed in the 30 days.

[TLCTech]

I am recording a table for campers coming into my campground. I have a form to fill out with different criteria including Arrival Date and Departure Date. I also have a field for counting the days between the 2 dates entered. I need to have a query to let me know if in the past 30 days did this registered camper already stay 14 days or "how many days does he have that he can camp to achieve his 14 day stay in 30 days.

[Micron]

At first glance, your query would need to retrieve records where the Arrival Date is >= Date-30 and the Count of Arrival Date >= to 14, plus a calculated field that determines the difference between 14 and the count of arrival date. The amount of information you've provided doesn't allow me to expand upon this much more than that. For instance, is your field for counting the days in the table, and are you manually entering this number? The values for aggregate functions (sum, count, percent, etc.) should not be stored in tables - only handled by forms/reports/queries. Also, I advise you to refrain from using spaces (or special characters) in your field names and maybe Google 'ms access naming convention' and adopt a method that suits you. If you create a spreadsheet with your table/field names, enter dummy data and copy/paste into a posting here, it's easy to create a table in your post. This will help us understand your underlying data setup so we can further advise on a query for you.
One thing you seem to have not considered is whether or not to be concerned with multiple arrival/departures for one camper within the 30 day period. Also, at this point I did not determine what to do if there is an arrival date within 30 days, but no departure date. You might want to default to the current date in that case.

[TLCTech]

I made a query for the DateDiff to come up with the number of days between the ArrivalDate and DepartureDate in a field called DaysStayed. The registered campers will be coming in a few times within this 30 day period that I am asking about. This is why I need to have a query so that it will check to see if they are allowed to stay at the campground more within that 30 days. They are only allowed to stay 14 days in a 30 day period. It would be nice if the query could tell me yes or no with a number of days that they can still stay in that 30 day period. Below is a speadsheet for the Table Camper Registration (tblCamperRegistration) and Query for adding the days of the stay (qryStay).

Registration.zip

[Micron]

Your posted zip file contains no data. Sorry to say it's not realistic to have to work without data (I will get no results when testing a query) and it's not fair to ask us to build a data set to test a problem for you. Sorry to say that the query xls is of no help as you've created it - better to have provided your sql statement along with some usable data in the other spreadsheet. As per my response to your PM, either zip a db copy per my suggestions or provide the table info and some data. I suspect your registration table is not properly normalized (guessing that the camper type is not correctly set up) and the dates suggest you do not have that info in the correct table. There should be a table for registrations, one for types, and one for arrival/departures.
This is another reason why posting a db copy is the best route to take.

[TLCTech]

So sorry about the data. Here is the updated db with 7 entries. Hopefully this is enough.




Camper Registration Files.zip

[Micron]

You indicated you need a quick solution, so I suggest you use your qryStay as a table source and calculate the sum of days stayed for each camper where the arrival date is >= date()-30. Create a new query (just close the table dialog when you do), go to sql view and paste this in:
SELECT tblQryStay.[Campers Last Name], Sum(tblQryStay.DaysStayed) AS SumOfDaysStayed FROM tblQryStay
WHERE (((tblQryStay.StartDate)>=Date()-30)) GROUP BY tblQryStay.[Campers Last Name];

and you will get the sum for each camper for the period. In the meantime, I'll work on some suggestions for your db (there are many).

[TLCTech]

Since my last reply, I have made different tables that you suggested. I now have tblCamperRegistration, tblCamperType, tblArrivalDates, and tblDepartureDates.


Tables.zip

[Micron]

Your latest post shows some table structure improvement, and here's what I came up with. NOTE: the symbols (#$?) are not part of the field names. The forum table tools don't allow cell colouring to show the relationships, so I substituted symbols. As for *, it's a suggestion for logging attributes of the sites and may not be part of your scope, but I think the table itself is a good idea.
The actual spreadsheet is attached. CamperRegistrationT.zip

tblRegistrations			tblCamperType			tblSites
field	type		field	type		field	type
CamperID#	autonumber		TypeID$	autonumber		SiteID?	autonumber
TypeID$	long		Desc	text		SiteNum	text or long
Fname	text					Notes	text or long
Lname	text		tblStays			Elec*	yes/no
Address	text		field	type		Water*	yes/no
City	text		StayID	autonumber
StateProv	text		CamperID#	long
PostalCode	text		SiteID?	long
Phone	text		ArriveDate	date
			DepartDate	date

Post back if you have questions about the structure or the use of it.

[TLCTech]

I do not need the site attributes but thanks. We already have a software for the sites themselves. This database is solely for check to see if the camper coming in can stay or not. The new rule is that they cannot stay more than 14 days in a 30 day period. This database is just to help us do a quick check before checking them into the campground. We would have to go through all of our records in order to find out otherwise. What do you want me to do next. I cannot figure out your last message about making the qryStay a table and copy/paste your suggestion for the sql.

[Micron]

I cannot figure out your last message about making the qryStay a table and copy/paste your suggestion for the sql.

When you create a new query, the add table diaolg opens. Just close it and switch the view of this new query to sql view. Paste in the code I posted and switch to datasheet view, which will cause the query to run. If it looks OK, save it as a new query. The result will be that this new query uses the query you created as a data source. As long as the query you created returns what you need, you should have your solution. You could build a report or form that uses this new query as its data source. If you add fields to the original table or query, and include them in this new one, you will likely find that rows will be repeated unless you do not Group By these new fields. Adding new Group By fields will upset the apple cart, so to speak.

Not sure if you agree with the rest of the suggestions. You should consider setting up the rest of them that way, even if you omit the site table.