Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automating the creation of tables and queries (Access 2000)

    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. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    why not just add a date field to tblsheets?

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    Why not create the month tables "on the fly"?

  5. #5
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    Hi Pat [img]/forums/images/smilies/smile.gif[/img]
    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 [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  8. #8
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  9. #9
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  11. #11
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  12. #12
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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)

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

  14. #14
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    haha, i guess i'm cool with it,,as long as it does the job [img]/forums/images/smilies/smile.gif[/img]
    Make sure you be cheering the Eagles when pay a visit to Melbourne [img]/forums/images/smilies/wink.gif[/img]

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Automating the creation of tables and queries (Access 2000)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •