If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using a query as a lookup table
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 |
Thread Tools | |
Display Modes | |
|
|