Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populate a table (Access 97)

    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
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate a table (Access 97)

    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>
    Thanks Hans, will try on Monday when the brain feels a little less scrambled.

    Marion

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

    Re: Populate a table (Access 97)

    <P ID="edit" class=small>(Edited by HansV on 22-Nov-02 18:13. I added a remark)</P>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.

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

    Re: Populate a table (Access 97)

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

Posting Permissions

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