Results 1 to 10 of 10
  1. #1
    ncrewson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5

    Question creating a multi-table with one column needing to draw data from one or another table

    Hello,



    Thank you for taking the time to read this. I'm trying to create a query where my header breeding group needs to pull data from either the sire table or dam table. Both the sire and dam table have a fishID and breeding group which is common to both tables and my query search for a fishID that corresponds to either the sire or dam ID and pull out the breeding group information.


    I've tired to create a union query but I have an error in the breeding field. The error message is "syntax error (missing operator) in query expression 'Breeding Group'. This is the SQL code I am using.

    SELECT [MYPed-MS].PITtag, [MYPed-MS].FishID, [sireINFO-MS].[Breeding Group], [MYPed-MS].FamID, [MYPed-MS].Status, [MYPed-MS].[Status Date], [cryo-MS].[Milt sample Code], [cryo-MS].[N cryopack], [cryo-MS].[N cryovials (1,8ml)], [cryo-MS].[Freezing date], [cryo-MS].[N thawed cryopacks]+[N thawed cryovials] AS [N thawed samples], [cryo-MS].[Sample color code], [cryo-MS].[Canister/Rack Number]
    FROM ([MYPed-MS] LEFT JOIN [cryo-MS] ON [MYPed-MS].FishID = [cryo-MS].FishID) LEFT JOIN [sireINFO-MS] ON [MYPed-MS].SireID = [sireINFO-MS].FishID
    WHERE ((([MYPed-MS].FamID)=[Forms]![searchKeyEntry].[famID]))
    union
    SELECT [MYPed-MS].PITtag, [MYPed-MS].FishID, [damINFO-MS].[Breeding Group], [MYPed-MS].FamID, [MYPed-MS].Status, [MYPed-MS].[Status Date], [cryo-MS].[Milt sample Code], [cryo-MS].[N cryopack], [cryo-MS].[N cryovials (1,8ml)], [cryo-MS].[Freezing date], [cryo-MS].[N thawed cryopacks]+[N thawed cryovials] AS [N thawed samples], [cryo-MS].[Sample color code], [cryo-MS].[Canister/Rack Number]
    FROM ([MYPed-MS] LEFT JOIN [cryo-MS] ON [MYPed-MS].FishID = [cryo-MS].FishID) LEFT JOIN [damINFO-MS] ON [MYPed-MS].DamID = [damINFO-MS].FishID
    WHERE ((([MYPed-MS].FamID)=[Forms]![searchKeyEntry].[famID]));
    Attached Thumbnails Attached Thumbnails query.PNG   query.PNG  

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    One of the main rules of normalization in table design is to make separate tables each containing one set of data. It seems that you have two tables containing the same data, the only difference being whether it is a sire or a dam. Put all of the same data into one table, you can then use sire or dam as criteria when needed.

  3. #3
    ncrewson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    What I did is create two queries with the same data except one draws from dam and the other from sire. The actually dam and sire tables have completely different data within in them except for the first two headers which is fishID and breeding group. I'm pretty sure I'm going about this all wrong. I just can't seem to find the correct approach.

    The sire table 2
    FishID, Breeding group, stripping date1, milt volume1, milt concentration1,...stripping date12, milt volume12, milt concentration12

    The dam table 3
    FishID, Breeding group, stripping date, weight at stripping, date weight at stripping, eggs taken, eggs/l, %eggs, fecundity

    The query I want
    PITtag, FishID, Breeding group, famID, status.....
    table1 table1, table2 or table3, table1, table1

    I think that better explains what I'm trying to do

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Really, get your table design right. No wonder you are having trouble trying to display the data.

    table 1
    BreedingID (?) - autonumber
    FishID
    Breeding group
    sire or dam

    table 2
    SireID - autonumber
    BreedingID
    other fields as table 3 above

    table 3
    DamID - autonumber
    BreedingID
    StrippingDate
    MiltVolume
    MiltConcentration

    Go to the Relationships window and join all the tables with matching field names (keys). Once this structure has been done you will find that data is much easier to gather.

  5. #5
    ncrewson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    I've tried my best to create a good design for all the tables. I've avoided the use of primary keys because the odd time it won't be present and I don't want that to affect the usage of the data. I just don't know how or if its possible to take data two one or another table for one header in my query.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can you post your database with some sample data, to give us a better idea of how to assist you?

  7. #7
    ncrewson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    Click image for larger version. 

Name:	querytables.png 
Views:	10 
Size:	105.7 KB 
ID:	29098

    OK that gives you the four tables I am drawing the data from and how I'm setting up my query on the bottom. You can see that Breeding Group I choose sireINFO-MS and it run fine but I need to set the table to sireINFO-MS or damINFO-MS. The criteria I'm using for this query is searching by FamID.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas on design by reading through this recent thread. It deals with dogs (dams and sires) but may have some relevance.

    I've avoided the use of primary keys because the odd time it won't be present and I don't want that to affect the usage of the data
    Do you have a specification for what you are trying to do? If so, can you share it with readers?

    I agree with aytee111 that normalization is the place to start.
    Last edited by orange; 06-14-2017 at 06:34 PM.

  9. #9
    ncrewson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    Ok I'm aware of the concept of normalization and I've applied where I could. What I need to do is create a query that my boss designed which includes the headers he needs to see. He would like to see the following headers.
    PITtag, FishID, Breeding Group, FamID, Status, Status Date, Milt sample Code, N cryopack

    now all these headers draw from different groups except breeding group. The breeding group can come from either the sire table or dam table if a fishID is matches. My problem is I can only choose sire table or dam table I cannot do both. Now is there a way to pick both tables for one header or not?

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your use of header is a bit confusing (to me).

    My suggestion is for you to take some sample data and draw (pencil and paper) a rough picture of what
    your boss wants/has designed in his/her head.

    Good luck.

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

Similar Threads

  1. Replies: 39
    Last Post: 03-15-2017, 07:34 PM
  2. Replies: 5
    Last Post: 08-25-2015, 08:39 AM
  3. Replies: 3
    Last Post: 10-31-2012, 05:04 PM
  4. Replies: 11
    Last Post: 08-22-2012, 06:34 AM
  5. Creating reports for table column from form
    By ryanwales in forum Reports
    Replies: 14
    Last Post: 10-13-2011, 12:48 PM

Tags for this Thread

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