Results 1 to 2 of 2
  1. #1
    Join Date
    May 2015
    Posts
    1

    help with shifting from spreadsheet to database

    Hi all, first post here, hope you guys can offer some advice.

    Im looking for help with a design concept that I just cant get my head around at the moment to control and document the components we use in building circuit board assemblies.
    At present we use a spreadsheet that is updated and saved with a new revision ID in its file name each time a new component is (or multiple components) are added or a change made to existing component(s)
    Currently this is around 1600 components, each with multiple approved manufacturers.

    To create a snapshot of this information, I would plan to use something like;

    tblComponents
    [ID]
    [Desc]
    ... other information specific to designed part - tolerance, size etc.

    tblApprovedParts_Alternatives
    ' would list multiple manufacturers and their part numbers for each ID part
    [ID]
    [Manufacturer]
    [ManufacturerPartNumber]

    What Im struggling to get my head around is how to create the third dimension provided by the spreadsheet file name essentially, how to create an revision history.
    The current approach for this data is that any change to any of the data results in a new revision of the document. So a new component added, an old part obsoleted, an error in a manufacturer or part number corrected will result in 'components rev 123.xls' being updated and saved as 'components rev124.xls'

    I can add a revision field and every time a component is added / changed, the whole data gets copied with a new revision ID, which to me is a quick and dirty (and unsustainable) way to make it work
    How should I structure the tables and then what would the format of a query be to retrieve a particular revision of the data.
    Really having a mental blank.

    Thanks in advance,
    Richard.

    Eg

    Rev 1
    ID.....Desc..........Manufacturer.....MPN
    1......RES 10K.....Bournes............060310K
    2......RES 11K......AVX.................060611K

    Rev 2
    ID.....Desc..........Manufacturer.....MPN
    1......RES 10K.....Bournes............060310K
    2......RES 11K......AVX.................060311K - MPN corrected

    Rev 3
    ID.....Desc..........Manufacturer.....MPN
    1......RES 10K.....Bournes............060310K
    2......RES 11K......AVX.................060611K
    3......CAP 10n......AVX.................0603100n - this part added

  2. #2
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16
    I think the most-practical way is to include the revision-level in the table name and then copy and edit the table to conform to the new revision. But that's no different than what you're now doing with a spreadsheet.

    Another approach would be to add a rev-level field to your table and update each record (component) that is included in the new revision to the new rev level but then you'd have no record of previous revisions. If you place the components in fields and rev-levels in records, your problem is finding a database that can have 1600 fields.

    - Bruce Hyatt
    Last edited by mrthnmn; 06-02-15 at 17:42.

Posting Permissions

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