Table With Period Dates

[JWilson]

Can someone shed any light on the following? I am new to MS Access and want to create a table from a query that will create periods based on a start date and an end date.

Eg: I want to say select 01-March-2003 as the start date and twenty 26 weeks (6 months) later select an end date. I want the query to fill in all the dates between these two date (weekly) as in Ms Excel Date()+7 etc etc any one have a clue?

Thanks in anticipation!! John

 

[Rich]

What's your reason for wanting to do this, I suspect there are easier ways

 

[JWilson]

Youth Club (membership)

What's your reason for wanting to do this, I suspect there are easier ways

This is needed when someone joins the club and pays a weekly membership fee. They can join at various time throught the week so therefore a static table of dates with a 7 day increment would be of no use. It has to calculate every 7 days from a give date for a period of 6 months (this could also be longer)!!

Any ideas would be most welcome.

 

[Rich]

Do you just wish to say print say a subscription sheet for the member? otherwise you'd be storing what in effect could be redundant data

 

[JWilson]

As Previous

Do you just wish to say print say a subscription sheet for the member? otherwise you'd be storing what in effect could be redundant data

Yes this is correct i would want to print this say monthly and hand out as proof of payment or non-payment as required. And also view on screen.

 

[Rich]

Yes, well for a small club say 15 members this would be easier to do in Excel, the problem there, is the number of sheets in the workbook, it becomes more attractive to use access when dealing with a larger number of members.
The problem with moving to access is that you need a different thought process to Excel
You are only concerned with
Member details
Date Joined
Amount Reqd Weekly
Date and amount paid
Filling tables with dates that may be redundant isn't recommended although it appears attractive at first.
The correct way to produce payment sheets is to use temporary due dates and compare them in a report.
It's actually quite complicated to produce the type of db you want but it can be done.
Everyone here will help you but you'll need to do some research first on normalising the data.
Search here for normalize there have been many examples
post back if you need more help

 

[JWilson]

Thanks for your reply

Yes, well for a small club say 15 members this would be easier to do in Excel, the problem there, is the number of sheets in the workbook, it becomes more attractive to use access when dealing with a larger number of members.
The problem with moving to access is that you need a different thought process to Excel
You are only concerned with
Member details
Date Joined
Amount Reqd Weekly
Date and amount paid
Filling tables with dates that may be redundant isn't recommended although it appears attractive at first.
The correct way to produce payment sheets is to use temporary due dates and compare them in a report.
It's actually quite complicated to produce the type of db you want but it can be done.
Everyone here will help you but you'll need to do some research first on normalising the data.
Search here for normalize there have been many examples
post back if you need more help

It would help me a lot if you could even give me a clue as to how i can have a make table query produce a list of 26 dates all 7 days apart based on an entry date.

I can hopefully work out what i need and dont need from that. There would be no redundant data as this data would be used for the whole term whether 6 months or 12 months (with a cumlative total showing the weeks amount paid) i only need to know how to populate the table with these dates?? Once again thanks for your help.

 

[Rich]

The normal way is to only add data when you actually have data to enter, in other words when a member makes a payment, you would normally use the Calendar Control to enter the date and then fill in the rest of the payment details.
I understand what your trying to do, but if I can't talk you out of it then I'll post the link that contains the code you need

 

[Mile-O]

I'd build my tables this way - if a set subscription has an amount then the total to be paid weekly would be calculable too:


Can we see how you have your database structured?

 

[JWilson]

The normal way is to only add data when you actually have data to enter, in other words when a member makes a payment, you would normally use the Calendar Control to enter the date and then fill in the rest of the payment details.
I understand what your trying to do, but if I can't talk you out of it then I'll post the link that contains the code you need

I guess you will have to send me the appropriate code sorry!! i need to have this data as entering a nil for a hundred people would be time consuming sorry if they didnt pay as some will pay 2 weeks and nothing for six weeks...

PS: Thanks for being patient

 

[Rich]

That's the point I'm making, if members haven't paid there is no payment recorded for them, the balance is calculated via a simple query, the DateDiff function calculates the amount due per member, a simple Sum[AmountPaid]-Sum[AmountDue] gives the balance at anytime.

 

[JWilson]

That's the point I'm making, if members haven't paid there is no payment recorded for them, the balance is calculated via a simple query, the DateDiff function calculates the amount due per member, a simple Sum[AmountPaid]-Sum[AmountDue] gives the balance at anytime.

Sorry about this Rich, i wasnt specific enough with my last response.

I dont want to record zeros but i need the date and fact that there was no payment to be recorded automatically??

Therefore it should look like this:

Date Member Amount Due Amount Paid Balance
01-03-03 A Name 2.50 0.00 2.50
07-03-03 A Name 2.50 0.00 5.00
14-03-03 A Name 2.50 0.00 7.50
21-03-03 A Name 2.50 5.00 5.00

The first three entries would be automatic based on the start date of 01-03-03. Amount due would be set amount paid would be zero and the balance would be a running total. The only entry therefore, would be the payment of 5.00 on the 21-03-03.. I hope this makes things a little clearer.

 

[Rich]

I dont want to record zeros but i need the date and fact that there was no payment to be recorded automatically??

In Excel you would, the point I'm trying to make is that in access you don't.
You also do not store running balances in a relational db.
You use queries to return balances etc.

 

[JWilson]

my sincere apologies

In Excel you would, the point I'm trying to make is that in access you don't.
You also do not store running balances in a relational db.
You use queries to return balances etc.

when you say you dont in ms access does that mean you cant? (create a table with with a roll on of seven days based on a given date)

 

[Mile-O]

It means, you can, and it causes problems, and it means you just don't!

The fact that Access allows queries to pick the data you need from the tables, calculate upon it, and siplay the data in forms and reports, etc. is why it is different from the Excel style you are proposing - Excel just has the grid.

You should never confuse the grid of an Excel spreadsheet with the grid of an Access table.

 

[Rich]

Yes, but you wouldn't unless you're trying to create a database from a spreadsheet approach, that will lead to all sorts of problems.
Working in Access is different to working in Excel, you need to approach the structure and calculation of data in a different manner, otherwise there isn't much point to moving to Access.

 

[pd06498]

Just reading this thread and I realise it is quite old, but I need to clarify something here for myself.

I am wanting to do the same thing here (i.e. predict dates at 7 day intervals) and record if a client uses a service in that period. If they do, then they get charged, if they don't, then they don't get charged.

So can anyone suggest how to tackle this and record this data in a db? I have a table of clients, services provided, and WAS going to have a table of weekly dates.

Where do I go from here?