Results 1 to 8 of 8
  1. #1
    Champin4 is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    4

    Relational Database/query help

    Hey, this is my first time posting and it really kind of is a "hey, kinda joining because i need help"
    I have three tables so far that i've compiled
    table 1 has soil sample info (with the client, project name, and project number included)
    table 2 has the info about the tests that were done on the soil. However there is 15 tests done per project, so each project name/number has to be listed 15 times so each test can be listed (279 samples* 15 tests)
    and then table three has the client name/project info along with some other information about the samples (stress levels etc)

    now i cannot for the life of me figure out how to relate them..
    like i want to be able to query this client name and project number from table 1, with test #13 from table 2, and the stress level from table 3.



    http://www.megaupload.com/?d=H5QAHDV8
    (that's the database, it was like 1.8 megs so i couldn't upload it here, feel free to scan or w.e first)

    I'm willing to do anything that's required to get this to work (a restructuring or w/e) it just has to become functional..

    also, i'm willing to pay someone a few bucks paypal if i get this resolved..it's buggin me that much!

    thanks in advance! (and i'm going on a trip tomorrow but will be back soon so sorry if i'm not absolutely prompt in my responses!)

  2. #2
    Join Date
    May 2010
    Posts
    339
    Please convert it to 2000 format and then place it in a zipfile. I am not looking for money just currious. If I can help I will.

  3. #3
    Champin4 is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    4
    http://www.megaupload.com/?d=ZODOR450

    there it is, and thanks

  4. #4
    Join Date
    May 2010
    Posts
    339
    Champin4,

    I did a rough restructure of your tables. The idea is to group similar fields in one table with a primary key. Since I have no way of knowing what your fields are, I took a guess on how the first grouping might look. Now you need to look at what I have done and see if it makes any sense. I suspect you might need to still make more moves to get your tables in a more normal structure. I left off making any attempt on relationships until you settled on a data model. I also removed all duplicate fields, and removed all spaces from your field names.

    Ps Check the field names spelling I may have mangled a few..


    Richard

  5. #5
    Champin4 is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    4
    kay, I see how my older one could be cleaned up a bit.
    And is not having spaces in the titles important? (just curious for future reference).

    I understand what you're saying about having primary keys for each table and grouping similar fields. But i'm beginning to wonder if my data is really like this at all..
    because basically it's like this:
    Say i have 7 clients. Each client has 2,3,4..etc projects that i did for him. Each of those projects has a specific name and number associated with them (so i guess those are 'similar fields').
    Now here's the hard part (in my book..): each project has a set of data..say data1, data2, data3 and data4.
    How do i set it up so that data1 and data2 are in one table, and data3 and data4 are in another, yet be able to create a query that gives me a table with project numbers, data1 only, and data 4 only (all from client 1)
    That's the question that's been baffling me..

  6. #6
    Join Date
    May 2010
    Posts
    339
    I see that peek your attention. Think of it like a fine sauce, tables have to "boiled down" to their finale stage.

    And is not having spaces in the titles important? (just curious for future reference).
    Those are field names not titles, it can be a real pain to code all the spaces. Each field name will have its own "label" that will have spaces. Example: DateEnter might have a label Date or Date Enter. Here is what you need to think about, you have basically three choices in your design.

    one-to-one relationship
    one-to-many relationship
    many-to-many relationship

    Here is a link that will explain what this means.
    http://www.databaseprimer.com/relationship_1to1.html

    After you read this post back.

  7. #7
    Champin4 is offline Novice
    Windows 7 Access 2003
    Join Date
    Jul 2010
    Posts
    4
    That helped, and it finally clicked for me, thanks
    i just made a new field, project id..it'l go from 1 to however many total tests were done (like 2,000 or so..) and that was the primary key in two of the tables, with a 1:1 relationship, and then i made it a foreign key in the third table (where each project has 15 data points) and made it a 1:many relationship

  8. #8
    Join Date
    May 2010
    Posts
    339
    Hey Champin4,

    Glad to hear you are making some progress in your design. Another concept in Relational Database design is, never save the results of a calculation in your table. Rather save only raw data, then when you need the results of a calculation you do it on the fly. Second point I wanted to make is a few of your fields could be represented in the form of combo boxes. For instance you have fields K1 K2 K5 and so on. Those could be choices from pull down combo's. Material type could be another. When you make some further progress post your DB I would like to see it, good luck!


    Richard

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

Similar Threads

  1. Running a query from another database
    By manicamaniac in forum Access
    Replies: 1
    Last Post: 06-11-2010, 04:54 PM
  2. Relating 2 Tables - Relational Problem
    By jeng in forum Database Design
    Replies: 5
    Last Post: 04-01-2010, 09:25 PM
  3. Report from database or query
    By finlain in forum Reports
    Replies: 3
    Last Post: 04-01-2010, 09:45 AM
  4. Replies: 3
    Last Post: 04-05-2006, 04:17 PM
  5. Northwind sample database query
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 02-28-2006, 07:34 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