Results 1 to 12 of 12
  1. #1
    Hallway903 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    6

    Please Point Me in Right Direction on Database Design

    I am by no means an Access Expert. I have designed one database that I use to keep track of my calibration equipment. I bought several books and can't tell you how many on-line sites I went to. It was alot of trial and error.I have been asked to design a database that would allow 6 or 7 departments to send me their Daily Test Logs. This information would then be complied into a Daily Log Report. An Excel Spreadsheet can log up to 40-50 products daily and keeps track of 6 types of test .. There is a sheet for each product. Could somebody please help me?

    Date /Product Type/

    5 Type Test - Tests 1 & 2- test Batches

    Test 1: Test 2: Test 3: PreProduction Test 4: X-RayDept Test5ept5
    HowManyBatchesPass HowManyBatchesPass, HowManyBatchesPass, NumberUnitsPasses Units Tested HowManyBatchesFails Test A Batches HowManyBatchesFail NumberUnitsFails
    Person 1 Test B Batches Person 1 TotalUnits
    Test Type Test C Batches Total Units


    Department Test D Batches
    Test/Retest TotalsInBatch
    TotalInBatch TestTwoTotal
    TotalUnitsTested

    There are 40 Products need to keep track of all batch numbers

    Batch Sample Result Acc/Fail

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Do you have an example of the test logs (excel files) or is part of your development process to design that excel spreadsheet as well?

    Is there a reason your facility couldn't do their data entry directly to avoid the duplication of data?

    Someone had a question similar to yours not too long ago on this site but I would not recommend trying to do this through importing excel files unless you control the 'template' they fill out and you refuse to process any tests without the proper version of your excel file. It's going to be very painful for you to do imports without total control of that file.

    Test 1: Test 2: Test 3: PreProduction Test 4: X-RayDept Test5ept5
    HowManyBatchesPass HowManyBatchesPass, HowManyBatchesPass, NumberUnitsPasses Units Tested HowManyBatchesFails Test A Batches HowManyBatchesFail NumberUnitsFails
    Person 1 Test B Batches Person 1 TotalUnits
    Test Type Test C Batches Total Units
    Department Test D Batches
    Test/Retest TotalsInBatch
    TotalInBatch TestTwoTotal
    TotalUnitsTested
    I haven't got the slightest idea what you're trying to say here, there's no formatting or anything

  3. #3
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    First, an FYI: if you put the word CODE in square brackets [] before your words and /CODE in square brackets after, then the forum won't squish all your words and layout together.

    Second, please list for us the information that is on ONE line of the excel spreadsheet. If it's a really long line, then do a paste-transpose into a spreadsheet and give it to us at one column per line, so it's easier to read.

    Third, don't worry about how the information is being passed to you. Think about it in concrete chunks. What is the chunk of information that you receive about EACH ONE OF THE TESTS? You'll want to SAVE the data that way, not the way you receive it.

    Fourth, just in case I read this wrong - were you planning on receiving spreadsheets and translating them into your database, or were you planning on creating input forms and saving them the data entry work. Do they each have a computer that they can enter from?

  4. #4
    Hallway903 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    6
    I would like to create input forms for each department to fill out on their computer. Then I would like all the information to be compiled into a daily report. I just don't know where to begin. I would like each Deparments to enter Date, Product and Shift on top of form then: I would like them to be able to enter Batch Total amount inwhich a form would open with a blank row for each Batch.
    Thats where I get confused. How can I design a form that would open with amount of blank rows so I can enter BatchNumber/Total Grains/and the batch result. I would also like to keep collective daily totals for each product.

    Accepts:
    Batch Number/Total Grains/Results

    Supervisor:
    Batch Number/Total Grains/Results

    Rejects:
    Batch Number/Total Grains/Results

    Special Test or R&D Test
    Batch Number/Total Grains/ Results

    No Test or Retest
    Batch Number/Total Grains/ Results

    Then For each Type Total Grains I Need a Daily total for all put together.
    Could someone please point me in the right direction.



  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you're going to want to keep this all in one table. It's the exact same data for each 'group'

    so you'd have :

    Code:
    tblGroup
    GroupID  GroupName
    1        Accept
    2        Supervisor
    3        Reject
    4        Special Test or R&D Test
    5        No Test or Re-Test
    
    tblPeople
    PersonID  PersonLN  PersonFN ------>  other people related information (this would be your employees if necessary to track who created what batch)
    1         Mouse     Mickey
    2         Mouse     Minnie
    
    tblBatches
    BatchID  BatchNumber  BatchDate  PersonID ----> other batch related information
    1        AAA-001      1/1/2013   1
    2        AAA-002      1/2/2013   1
    3        AAA-003      1/3/2013   2 
    
    tblResults
    ResultID  BatchID  Grains  Result  ResultDate  GroupID
    1         1        5000    Pass    1/2/2013    1
    2         1        2500    Fail     1/3/2013    2
    This is just a quick guess because I don't know what your RESULTS means. It could be one test that's performed on everything you do or it could be a series of possible tests depending on the batch/item. But your tblResults would store ALL of your results and part of your data entry would be which 'group' the testing results were recorded from. Then you could summarize it however you wanted, operator (by linking to the batch table then to the employees table), group, date, etc.

  6. #6
    Hallway903 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    6
    I'm sorry I couldn't get back before now to thank you. Ihave really been busy but I just wanted to tell you I really appreciate yourhelp. My problem is each department sends a spreadsheet. Dept#1: Product, TypeTest, Lot, Batch #(s), Batch Amount, Total good units, Total Bad Units, Howmany of those units are: Bent, Scratched, Broken, or Other. If “Other: thanlist detail Defective problem and which unit number of Batch Amount. (SupposeBatch #4545 had 50 units and unit#30 was defective, we need a way to listdefect reason in detail) Dept#2: Product, Type Test, Date, Department, BatchNumber, Batch Amount, Passes or Fails. Dept#3: Product, Date, Batch Number,Sample A Result, Sample B Result, Sample C Result, Sample D result, Operator,P.Q. Date, Date required, Date tested, Tested By, Total Quantity, Sample Size.,My problem: Dept#1 sheet has 20 rows same with all departments. How would Icollect all this information? Would I use Excel and import into an AccessDatabase? And how would I go about doing this? Can it even be done in Access? I will have the same problem with eachdepartment. Right now all this information is being transferred into aspreadsheet, (Which take’s several hours each morning) then compiled before forthe morning meeting. Attached is a Copy of Spreadsheet. Note batch informationis listed in upper right corner. Then each program has a sheet at bottomlisted. I know this can be done another way. Please point me in some direction. I can't fiqure out how to make a form that a department could fill out daily with several lines of information or would information be collected another way? Please help?

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There's no attachment. If you do upload one please put it in a version of access prior to 2010.

  8. #8
    Hallway903 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    6
    I tried to download my excell sheet after I altered the form for obvious reasons but I was not able to download onto your site. But I did rpeare's sample and made the tables as advised. I have over 30 programs that will use these 4 groups. How would I add theses tables to 30 Programs. I am pulling out my hair...

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    without an example of what you're currently storing telling you where to go with your project is fruitless.

    Try zipping up a copy of one of your spreadsheets then uploading it through the ADVANCED function (look for the GO ADVANCED button at the bottom right)

  10. #10
    Hallway903 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    6

    My zip file is on attachment

    I have a downloaded a copy of my file into a zip folder. Then I dragged it into the attachment window. Its in the middle window but don't know how to send it. Now I feel stupid. I just noticed it was attached at bottom of this form.

    I would like to design a database that would be able to collect several departments test results, combine them and generate a report each morning. What is happening now is 5 departments are sending test results. It takes 4 or 5 hours putting them on an excell spreadsheet. Can I collect this information and process it in Access. If so, Please tell me how i should go about it. I would just like to know if it can be done. I tried, and tried. Do I have to make a form with the 5 department results for all 40+ programs or is there a simplier way? Would appreciate any guidance.
    Attached Files Attached Files

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok..... I don't know how the hell you're using this spreadsheet

    Let's take an example.

    On the sheet labeled TEST LOG I'm looking at the row with the label SAMPLE W.

    There are only two sheets with a W in the name

    SAMPLE W TEST A
    SAMPLE 2 XRAY

    On the TEST LOG sheet you have the number of batches as 4

    Does that represent the number of rows of data on the sheet SAMPLE W TEST A or is it something else?
    Where does the 1 b REJ come from in the REJ column of the TEST LOG sheet?
    Where does the '25 pieces tested' come from in the AMOUNT PER BATCH TOTAL PIECES TESTED column?

    I don't see how any of the data in the two sheets with a W in them relate to anything on your test log.

    When you're designing your database you have to think about HOW items relate to one another and build the structure of your database around those requirements.

    For instance if you perform the same tests on everything you produce, that's fairly easy.

    If, however, you perform different tests on different items that's more complex and your table structure might be more complex as well depending on how different the tests are and what data is required to be collected. My suggestion for a data structure was a very basic and not necessarily complete picture of how your tables should be constructed. What you want to ultimately be able to DO with your database determines your design as well. For instance, do you want someone to be able to look at your database and be able to determine which tests they have to do on a specific item, then record the results against a set of tolerances? Or do you just want to record the results and you don't care about being able to compare to an acceptable value/tolerance because your tolerances/tests required are part of another system?

  12. #12
    Hallway903 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    6
    I am so sorry for making thisspreadsheet so complicated that you couldn’t follow information collected. WhatI tried to do was omit and change info for obvious reasons but I just madethings worse. I will try to explain.
    The Test log is collecting somuch information- I can hardly believe how much is collected.
    Test “A” part-technicians fillout a lengthy form. At the bottom of form they fill out a chart. Enter batchnumber (next number in sequence from day before), sample amounts and collectionof an over-all average. The total number of batches is then written in testlog, along with samples size. On Program E there were 5 Accepted batches. 1 batchhad 3 samples and the other 16. No test/retest had 2 batches with 6 samples total.Altogether25. What we now do is list all batch information in the commentsection of each cell where needed. Look at red arrow in upper right corner.This is really a tedious and monotonous project to accomplish each day beforethe morning production meeting. Keeping track of all units that are x-rayed andlogged down is also mind boggling. I think some of test results will have to be set against some tolerences depending on the test. Would I have to set up test against each product. I have over 30. I've tried several ways already.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  2. Can anyone help point me in the right direction?
    By bhamhawker in forum Access
    Replies: 2
    Last Post: 11-04-2012, 08:06 AM
  3. Beginner needs a point in the right direction!
    By Gapco1 in forum Database Design
    Replies: 7
    Last Post: 07-30-2012, 07:13 PM
  4. Replies: 1
    Last Post: 05-15-2012, 06:47 AM
  5. Replies: 86
    Last Post: 10-07-2011, 06:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums