E
Evi
In my Accounts database, the user sometimes needs to store invoices in a
dated month folder which does not relate to the date on the invoice eg an
Invoice dated 29/12/07 may sometimes have to go in the Jan08 folder. To
allow this, I've put a Year/Month table in my database with a Foreign Key in
the Expenses table so the user can choose the month in which she wishes to
store the invoice but actually, instead of adding items to this Year/Month
table when a new year starts, I could generate the year/months using a query
based on a number table, 1 to 12, combined with a union query which combines
a query containing all the years in the database minus 1 and all the years
in the database plus 1 (so I get the year before the earliest query date and
the year after). To create a 'Primary Key' for this query, I concatenate the
month and year number. All the queries ran fine on their own But, when I
tested it out, by updating a field in my expenses table to hold the correct
'foreign key' for my month/year query and adding my MonthYear query and
creating a query from the 2 tables, the results ran like a total slug. Why
is this? It seems mad to run Append queries to store something as regular as
months.
Evi
dated month folder which does not relate to the date on the invoice eg an
Invoice dated 29/12/07 may sometimes have to go in the Jan08 folder. To
allow this, I've put a Year/Month table in my database with a Foreign Key in
the Expenses table so the user can choose the month in which she wishes to
store the invoice but actually, instead of adding items to this Year/Month
table when a new year starts, I could generate the year/months using a query
based on a number table, 1 to 12, combined with a union query which combines
a query containing all the years in the database minus 1 and all the years
in the database plus 1 (so I get the year before the earliest query date and
the year after). To create a 'Primary Key' for this query, I concatenate the
month and year number. All the queries ran fine on their own But, when I
tested it out, by updating a field in my expenses table to hold the correct
'foreign key' for my month/year query and adding my MonthYear query and
creating a query from the 2 tables, the results ran like a total slug. Why
is this? It seems mad to run Append queries to store something as regular as
months.
Evi