Access Help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jfrancis@cityofgreenville.org
    #1

    Access Help

    Hello,

    Let me simply explain what I'm trying to do and maybe it will make
    sense. I think it's a fairly easy thing, but I've been looking at the
    DB too long. Any suggestions are welcome.

    I've got an tblAsset that has a list of assets. The key field is
    obviously Asset Number which will be unique to each record. What I am
    needing to do is pull up an Asset, and add assets under that one
    particular assets. Example, I have a building asset which I want
    fixture assets to fall under to show where those assets are. With
    that said, I've created a tblContents table that simply has these
    fields: id (autonumber PK), asset number, content number (the sub
    asset numbers). This concept would work fine but I want to be able to
    pull description and value for the subassets on forms, and reports but
    this won't allow me. It pulls the Main asset's description and value
    instead. I didn't really want to put the description and value in the
    tblContents just to avoid duplicate data in the database. Any
    suggestions are greatly appreciated!!! Sorry for the long post!

    Thanks!

    Tags: None
    • Salad
      #2
      Re: Access Help

      jfrancis@cityof greenville.org wrote:
      Hello,
      >
      Let me simply explain what I'm trying to do and maybe it will make
      sense. I think it's a fairly easy thing, but I've been looking at the
      DB too long. Any suggestions are welcome.
      >
      I've got an tblAsset that has a list of assets. The key field is
      obviously Asset Number which will be unique to each record. What I am
      needing to do is pull up an Asset, and add assets under that one
      particular assets. Example, I have a building asset which I want
      fixture assets to fall under to show where those assets are. With
      that said, I've created a tblContents table that simply has these
      fields: id (autonumber PK), asset number, content number (the sub
      asset numbers). This concept would work fine but I want to be able to
      pull description and value for the subassets on forms, and reports but
      this won't allow me. It pulls the Main asset's description and value
      instead. I didn't really want to put the description and value in the
      tblContents just to avoid duplicate data in the database. Any
      suggestions are greatly appreciated!!! Sorry for the long post!
      >
      Thanks!
      >
      If I understand you correctly, I think you are needing one more asset
      field in the tblContents. For example, you have the asset of Building
      and a subasset of Fixtures. I think you need a MainAssetID field that
      links to your Building asset record and a SubAssetID field that links to
      the Fixture asset record.

      -- tblContents --
      | |
      tblAssets tblAssets
      | |
      MainAsset SubAsset
      | |
      Building Fixtures

      Hmmm...maybe this is what you discribed above. Maybe the query for the
      report's recpordsource is goofed up. Drop tblContents in the query
      buiilder and 2 copies of tblContents. Set the relationships; contents
      to main (mainasset it) and contents to sub (sub asset id), and you
      should be able to get the correct data.

        Comment

        • John Welch
          #3
          Re: Access Help

          Hi. It sounds like you might need the subassets to be actual full
          fledged 'assets' with all the properties of an 'asset', such as value
          and description. If so, you should probably have just one table for
          assets. You could include a field called 'parentAssetID' that would link
          a particular asset to it's parent (in the same table). In a report, you
          could then have a subreport that for each asset listed all the assets
          that have that asset as their parent. It would be the same kind of thing
          as having a single table of employees, but some of them are marked as
          being supervisors of others. It's called a 'self join' because you join
          a table to itself. You might look for examples of that on the internet.
          One question to think about is whether you will need multiple levels of
          'parent' or whether everything is strictly either a 'parent asset' or a
          sub-asset'.

          hope this helps
          -John

          jfrancis@cityof greenville.org wrote:
          Hello,
          >
          Let me simply explain what I'm trying to do and maybe it will make
          sense. I think it's a fairly easy thing, but I've been looking at the
          DB too long. Any suggestions are welcome.
          >
          I've got an tblAsset that has a list of assets. The key field is
          obviously Asset Number which will be unique to each record. What I am
          needing to do is pull up an Asset, and add assets under that one
          particular assets. Example, I have a building asset which I want
          fixture assets to fall under to show where those assets are. With
          that said, I've created a tblContents table that simply has these
          fields: id (autonumber PK), asset number, content number (the sub
          asset numbers). This concept would work fine but I want to be able to
          pull description and value for the subassets on forms, and reports but
          this won't allow me. It pulls the Main asset's description and value
          instead. I didn't really want to put the description and value in the
          tblContents just to avoid duplicate data in the database. Any
          suggestions are greatly appreciated!!! Sorry for the long post!
          >
          Thanks!
          >

            Comment

            Previous template Next
            Working...