Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2005
    Posts
    4

    Unanswered: Change table a query is Using

    Is there an easy way to change the table that a query is using? I am going to have several tables (Bill of Materials) for different Jobs, and wanted to keep the jobs in ine database, will each job being its own table. Then I could run my queries and generate an organized bill of materials, and will be able to look back later and see what parts are common in each job (table).

    Basically what I am looking to do is have the user prompted to select which table to use to run the queries.

    Thanks for any help!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Joshm

    Welcome to the forum

    It is possible but not trivial. Before you settle on this design take a deep breath, read the link, count to 3 and be certain you want to go down this route. Under most circumstances - this would be considered bad design.

    http://r937.com/relational.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ... and that has got to be the record date-of-joining-to-first-post difference ever
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Snarky: This has (pardon my French) cluster-f**k written all over it ... Bad design? Hell yes. Nobody designs a DB to have each job be in its own table ... You can analyze material commonalities within a single table ... It's called reports.

    Hey Poots, doesn't Access still have a limit on the # of physical tables allowed? I know it was 255 as of A2K ...

    So this design MAY blow up in you face is short order ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes to Pootle Flump! Usually you want to keep fields that collect the same information in 1 table and "group" them by another field similar to a "category" type field. It makes it easier in the long run on design and reporting.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Mar 2005
    Posts
    4
    Yeah, it been awhile. I originally joined to fix another problem with another table and ended up corresponding away from the forum.

    Any suggestions on another way to approach this situation?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Snarky: This has (pardon my French) cluster-f**k written all over it ... Bad design? Hell yes. Nobody designs a DB to have each job be in its own table ... You can analyze material commonalities within a single table ... It's called reports.
    Ah - the world is back in order

    Quote Originally Posted by M Owen
    Hey Poots, doesn't Access still have a limit on the # of physical tables allowed? I know it was 255 as of A2K ...
    Dunno - never got close to 255 in Access quite frankly.

    joshm - what was the design of the original Jobs tables?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2005
    Posts
    4
    I am uning Inventor to create 3d Drawings... I was hoping to export my bill of materials to Access, keep some kind of order between jobs (drawings), and be able to generate an organized bill of materials and be able to search the different jobs for common parts (for example all jobs that use a certain model pump)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That last statement is another good reason why a nice, relational design is best.

    By design of jobs table I meant like
    Table:
    Jobs
    Fields:
    Job_ID Integer
    Job_Name Text(30)

    etc etc
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2005
    Posts
    4
    Table: (Job Name) for which ever job

    Fields:
    Item
    Part Number
    Description
    Qty
    Cost
    FloodedWeight
    Weight
    Labor
    PL (pipe length)

Posting Permissions

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