Automating the creation of tables and queries (Access 2000)

Topic

New Reply

I need some advice on the best way to approach this.
I have a table called sheets, and I want the user to be able to shift that data to another table depending on the month the user chooses.
Ok, something like this
* Data entered into tblsheets
* User then shifts data from tblsheets to another table depending on the month (hence user will create table, eg tblMay2003, tblJune2003)
* All records in tblsheets will then be deleted. Awaiting the next batch.
—–
Now this was my approach with my limited access knowledge..
* I would create all the month tables beforehand, eg tblMay2003, tblJune2003 etc
* Then create and run the append queries to add the tblsheet data to the month that was created, so in this case, i would run a qryMay2003.
* Running a delete query to delete all records in tblSheets.

All this would be done via the form. Now I’m sure there is a more sensible way to do this, in a more automated fashion. Because if I follow my approach, I’ll have to create all the month tables up till God knows when. eg tblOct2004,… tblJuly2005 etc etc
So I think its more common sense to give the user the approach to create the table then and there. As well as the queries.
Any advice would be appreciated.

Reply | Quote

Replies

why not just add a date field to tblsheets?

Reply | Quote

I feel that would exacerbate the table, as i find it will be easier to archive in months. Plus, i think its best to give the user the choice, as at times, it might not be exaclty the month in the data or system clock,,sometimes a user might have to decide between may and june. If he/she is given the choice, it will be much more flexible.

Reply | Quote

Why not create the month tables “on the fly”?

Reply | Quote

Hi Pat
Do you mean create the month tables/queries as each month go by, or do you mean create them all now?.
But i wont be controlling the db in future, so i want to have the user to be able to do it, without actually having to know there doing it (not that
they’ll care anyway

Reply | Quote

I still don’t think it is a good idea to scatter the data over separate tables. If you want to generate monthly reports, you can use queries to select the appropriate data; these queries can be dynamic (based on a choice by the user.) Having separate tables makes it much harder to aggregate over longer periods of time. Maybe you don’t need that now, but why paint yourself into a corner if you can easily avoid it?
You can also use queries for archiving data based on the date.

Reply | Quote

Hans,
Do you suggest that I create one table, and sort them out by date?. So if any data is being transferred from tblsheets into it, the user will be able to search it. I’m not so concerned about monthly reports, just effeciency. There will be 2000 records a month that will have to go into this one table.

Reply | Quote

2000 records a month. I would not even bother with another table, this will take years to grow to any size.
As long as you have your indexes set you should have good performance.
Don’t worry about any extra database, I only did it that way because the database would grow to 300-450 Mb each year, so putting it into different databases gave me a nice performance as well as archiving and deleting archives down the track.

Reply | Quote

LoL, and i thought 15 000 records would be stretching the database. But you see Pat, the data imported into this month, cannnot be mixed with the
data imported next month. Because when the end-user does a search for weight, he/she will want the sum of the weight for that month.

Reply | Quote

(Edited by patt on 25-May-03 07:45. Added comment of Date vs Month)

But that’s where you will have to introduce a month field to this table. Where does the data come from in the excel sheets, I presume that’s the source of this data.

By month field I meant a date field as Peter suggested.

Reply | Quote

Ok., I’m actually working on it now, the thing is, the date on the excel sheets is not important. Only when it was imported. I’m also currently creating a table called archive (with a date field), and all the data will be shifted to it after the month has ended. So user can search via date for past records. So i guess source of the date will be the default value (ie todays date)

Reply | Quote

So you are cool with updating that date, are you?

The reason I took a little time to respond is because I’m watching the Brisbane vs Carlton game.

Reply | Quote

haha, i guess i’m cool with it,,as long as it does the job
Make sure you be cheering the Eagles when pay a visit to Melbourne

Reply | Quote

Eagles, who are they.
I have just watched Brisbane kill Carlton after trailing by 8 goals to 2 goals, Brisbane then kicked 21 to 3 goals after that, they are awesome.

Reply | Quote

Hans has a good point here. Unless archiving is a major issue I would go with what Hans has suggested, otherwise you will need to build queries on the fly to address the various tables.
I built a system a while back that created a different database with it’s name as DByymmdd.mdb, this was done so in the future all they had to do to delete an archive was just delete the appropriate archived database. I built the queries on the fly using the ‘IN databasename’ clause. This could be another way to go.

Reply | Quote

Hi Pat,
I’m not so sure what you mean by on the fly. But from what I gather, your suggestion is I do another database, and have the data transferred to that?, it does sound plausible. Also, archiving is an issue, but the user still has to have easy access to it.

Reply | Quote