Populate a table (Access 97)

Topic

New Reply

Hi folks:

I have a table called tblAssets with items which have an active start month, an active life (in months), and the end month [start month + active life]. For example an item may have a start date of 2, a life of 36 and an end month of 37. I have another table which details these months ie. Month 1 = November 1998, Month 2 = December 1998 etc.

Can anyone tell me how I can automatically create another table from this informationwith all the months in tblMonths, showing the month number as a heading, the Asset Id as the row, and giving a checkbox tick underneath the heading each month where the item is active?? I have a junction table, but want to be able to update it automatically…. I suppose what I am trying to reach within the junction table is that If an asset Id is active in month 1 (or 2 or 3) then I want a value of true. Therefore a conditional statement would be something like (if [1] is in (Between [StartDate] and [EndDate], True, False). Unfortunately I can’t really get there…

Marion

Reply | Quote

Replies

(Edited by HansV on 22-Nov-02 18:13. I added a remark)

What you want to have is derived information (you already have derived data in your table – if you have start month and end month, you can compute duration; if you have start month and duration, you can compute end month, so only two of the three fields are necessary). In general, it is not advisable to store derived information in a table – the moment the underlying data change, the derived data are not up to date any more. Usually, queries are used to compute derived information. Queries can be used to populate a form or report, or to export data to Excel etc. If you really want to, you can use a query to create a new table.

In this case, you want data displayed in a crosstab. Perhaps somebody else has a better idea, but I would create two queries:
1) An ordinary selection query that selects all occurring combinations of Month numbers and Asset ID’s:

SELECT [AssetID], [tblMonths].[MonthID]
FROM [tblAssets], [tblMonths]
WHERE [tblMonths].[MonthID] Between [StartMonth] And [EndMonth]

For the following, I’ll name this query qryMonthsAssets

2) A crosstab query based on qryMonthsAssets and tblMonths that displays more or less what you want:

TRANSFORM Not IsNull([AssetID]) AS Active
SELECT [AssetID]
FROM [tblMonths] LEFT JOIN [qryMonthsAssets] ON [tblMonths].[MonthID] = [qryMonthsAssets].[MonthID]
GROUP BY [AssetID]
PIVOT [tblMonths].[MonthID]

This query returns an extra row with an empty AssetID and zeroes as values.

Added: of course, you must replace table and field names by the actual names you use.

Reply | Quote

Thanks Hans, will try on Monday when the brain feels a little less scrambled.

Marion

Reply | Quote

I have attached your database with the queries added, plus an append query to fill tblJunction.

Reply | Quote