Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    60

    Unanswered: Need help designing tables.

    I am trying to design some new tables for my database to move some data from excel into access. my spreadsheet looks something like this...
    Code:
    	24/08/2009	23/08/2009	22/08/2009	21/08/2009
    Drg1				1				0
    Drg2						1		0
    Drg3		3		2		1		0
    Drg4		2				1		0
    Drg5		1				0		A
    I have drawings as the 'row headers' and dates i received as the 'column headers'. the numbers in the middle are the revision number i received. as i receive new revisions, i add in extra columns (and add rows if new drawings come in as well).
    i also need additional information to be entered for both the rows and columns. for the dates(columns), i need to store who it came from (fyi, these are transmittals). for the drawings(rows) i need to store the drawing titles etc. and at the end of the day, i want to be able to select a drawing and have access tell me the latest rev i have received and who i received it from etc (so i click Drg4 and it tells me rev 2 etc).

    So i am unsure what the best method is to achieve my goals, plus keep it efficient (because the database holds many jobs, which receive many drawings on many transmittals). i think i need a table to store the transmittals in, which will hold the dates, who from etc and link them to the job. it's how to store the drawings that really stumps me. should i store a list of drawings in 1 table and then the revisions in another, or just have 1 table that has the drawing and link to transmittal. i believe this would be 'inefficient' because the drawing title would be stored multiple times (ie Drg3 would have 4 records, each with the title field).
    anyone care to make some suggestions.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have a table which identifies the drawing and anything unique or distinctive about the drawing. such as the part name or whatever

    you have a sub table which identifies the revision number, with say other data, such as release date, who drew it,m who signed off or others such as why there is a revised version and possibly a further sub table which identifies what assemblies the revision is specific to. if your design team allows you could store the applies to version as part of the the revision definition.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2006
    Posts
    60
    thanks for that, i made the tables and it seems a pretty good way to go. but now i am having a bit of trouble entering new revision records (which will be on forms). im thinking the drawings would have to be pre-entered into their table. then, a new transmittal could be made, and a new form opened 'inside' the transmittal. on this form you would need to choose a drawing and then enter the revision information and a new record would be created. while this would work, its not what i was after. is there a way to get a spreadsheet looking thing that can be edited? the best thing i could imagine would be that when you open the form, there is a small table with all the drawings for the job listed. then you can fill in the rev boxes, which will add a record. it would look something like this
    Code:
    	Rev	Description
    Drg1				
    Drg2				
    Drg3	 3	Changed
    Drg4	 2	Changed
    Drg5	 1	New
    so this would be the entry table/form for the transmittal 24-08-09 listed above. is this possible? i can get all list of the drawings easy with a query. i even created a crosstab query which looked fairly promising apart from the fact it wouldnt let me edit the data. only other way i could imagine doing it, would be to use the list of drawings, and then have additional blank boxes that would update via sql statements in vba, as a box is updated. when the form is opened, all the boxes would have to run a dlookup to find if a rev already exists.

    ps-this 'system' is actually for incoming drawings, and all this revision entry is manual! i get the drawings many different way (paper, email, fax etc), sometimes with a transmittal summary and sometimes without, so there is no way to set up a system to import the data. i just have to write it in. which is why i would like to aim for the spreadsheet style, rather than the pick a drawing style. although, another idea could be for the database to export an excel file, where the revisions could be entered, and then import this file back in to the database. it would only add a small step to my 'procedure'. and then any additional revisions that need tweaking/adding later can be done so with the standard method i mentioned first.

  4. #4
    Join Date
    Feb 2006
    Posts
    60
    well i have been testing for ages, cant get a spread sheet style to work, even with dlookups and what-not. so i am just proceeding with the 'normal' data entry method, but if anyone can share some info to create a spread sheet that would be great.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    im not surprised you cant get a spreadsheet style to work.
    if you are using Access use an Access design style, bot a spreadsheet style

    consider using a subform detailing the revision history as part of the part design parent table
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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