Hi Tom,
I understand exactly what you're saying, however to change the way the table
is used will mean an almost complete re-write of the database.
The major reference to the data in this table is the couples' number which
belongs to the male in case they change partners in which case I record who
the old partner was, date of change etc in a "history" table. All to often,
couples (the male) don't return in the new year & their number gets
reallocated to a new couple otherwise they get to keep the same number.
I started developing this database at the beginning of the year for the
association I belong to for them to keep up to date records & records the
points they earn at competitions. I've added (as I've learned along the way)
a bunch of functionality & checks to eliminate the user generating errors. I
had not envisaged the functionality I want to implement now for the start of
the new year.
So for now I need to make a "quick fix" method of getting the data so the
association can keep functioning with this database. Then I can consider a
re-write as a pet project to improve it in the new year at my leisure.
"Tom van Stiphout" wrote:
> On Wed, 30 Dec 2009 06:12:01 -0800, Hugh self taught
> <(E-Mail Removed)> wrote:
>
> If I understand you correctly you want to make a copy of a table for
> the benefit of having the data for one year together in that table.
>
> This is a REALLY BAD idea and such approach does not belong in a
> relational database. Rather you should use queries. For example:
> qryCouples2009:
> select * from tblCouples
> where Year(StartDate) = 2009
>
> or perhaps even better:
> qryCouplesThisYear:
> select * from tblCouples
> where Year(StartDate) = Year(Date)
>
> qryCouplesLastYear:
> select * from tblCouples
> where Year(StartDate) = Year(Date) - 1
>
> Make sure you have an index (allow duplicates) on the StartDate
> column. This will improve performance.
> Then you work with these queries like you would with your imagined
> tables.
>
> -Tom.
> Microsoft Access MVP
>
>
> >Hi Special people,
> >
> >I need to replicate a table in my database back end. This is so that I have
> >a separate table with all last years couples that I can run current year
> >comparisons to. Ideally I want a newly created table so I start it afresh.
> >
> >I tried the DoCmd.Transferdatabase method for ease of code but there are
> >existing relationships. In the immediate term I have resorted to using the
> >DoCmd.CopyObject & a Delete query turning warnings off & on. Not what I'd
> >prefer to be doing though. Seems a bit messy.
> >
> >Any alternatives to my dilema?
> .
>
|
Similar Threads
