Results 1 to 10 of 10
  1. #1
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114

    Query returns problem

    Hi there,



    I'm having a problem with the return a query gives me when it is run. This query is set to list select parts by Manufacturer name and our part number; followed by the part description and the manufacturer's part number.

    Here are the results:
    Click image for larger version. 

Name:	Query results.png 
Views:	21 
Size:	219.0 KB 
ID:	26154
    There are blanks where company names should be and there are a few repeats missing (same part number, different manufacturers).

    This query is run off of three tables. Here's the set up:
    Click image for larger version. 

Name:	Query Setup.png 
Views:	21 
Size:	31.5 KB 
ID:	26155

    I've checked and rechecked the three tables for errors.

    For example, in the Component Status - Active table, there are the appropriate 3 entries for the part number QS10004; the Component Description table is accurate and the Supplier Information is also accurate. The New ABBR in the SI Table is the same abbreviation that is listed in the MFG field in the CSA table.

    So my questions are:

    1. Why are the multiples not showing up (again; same part number, different manufacturers), and,
    2. Why are the company names missing when they are listed (joined) by the same abbreviations?
    Attached Thumbnails Attached Thumbnails Query results.jpg  

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can you attach your database?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks to me that your table relationships/table structures need work.
    The PK field of the left hand table should link to a FK field in the middle table.
    The PK field of the right hand table should link to a FK field in the middle table.
    (This is a many-to-many relationship - the middle table is a junction table.)


    In your query:
    You don't need Like "*" as criteria for field Company Name.


    The criteria under the field KGS PN doesn't understand what DR is, so access added the brackets.
    I would expect the criteria to be
    Like "*DR*"
    Like "*DZ*"

    etc




    "Type" is a reserved word in Access and shouldn't be used as an object name.
    Shouldn't use spaces in object names.
    Using "ID" as the name of the PK field in every table is poor programming practice.

  4. #4
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Quote Originally Posted by aytee111 View Post
    Can you attach your database?
    Sorry, but the boss says no.

  5. #5
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Hi Steve,

    Thank you for the feed back, I'll have to work on the table junctions at a later time.

    In response to the end comments:
    1. I'll be glad to rename "Type" when I can come up with another word for this and/or column. Type is what we use to distinguish if a supplier is the direct manufacturer (M) or a distributor (D) of the part products we use.

    2. "ID" is the name that access gave the column as the primary key fields.

    I'll let you know how it goes next week when I can work on the Test db.

    Thank you so much for the help,

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You are not using outer joins here, so the screenshot of the query results suggests to me that there is at least one record in your SI table that has data in the [New Abbr] field, but with a blank Company Name. I don't see any other way you could get those results. To check this, add the fields MFG from the CSA table, and [New Abbr] from the SI table to the query, and run the query again. That will tell you which record in the SI table is giving you problems.

    Another possibility is that the [Company Name] field has multi-line data in it, and you are seeing records with a blank first line.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    John_G brought up some good points. Do you have any calculated fields, Look up fields or multi-value fields?

    I still believe that the table structures/relationships need work.


    ------------------------------------------------

    1. I'll be glad to rename "Type" when I can come up with another word for this and/or column. Type is what we use to distinguish if a supplier is the direct manufacturer (M) or a distributor (D) of the part products we use.
    I would use "SupplierType" as the field name.


    2. "ID" is the name that access gave the column as the primary key fields.
    Access also names queries "Query1", "Query2", ...
    and combo box controls "Combo1", "Combo2", ...

    I would use
    "SupplierInfoID_PK" for the "SupplierInformation" table primary field name,
    "CompDescID_PK" for the "ComponentDescription" table primary field name and
    "CompStatueID_PK" for the "ComponentStatus" (can't read the full name) table primary field name.

    I use a suffix of "_PK" for primary key fields, "_FK" for foreign key fields and ID if the PK field type is a number.


    Good luck with your project......... I'll be watching

  8. #8
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Steve,

    If you live in So Cali, I'll be glad to learn at your feet.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you live in So Cali,
    I wish!
    I just had the first dusting of snow last night at my house.
    It melted.... but I'm not ready for winter yet!!!

  10. #10
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Possibly skyping?

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

Similar Threads

  1. Problem with specific query returns
    By rebfein in forum Queries
    Replies: 4
    Last Post: 08-04-2016, 12:50 PM
  2. Query returns nothing
    By jtmott in forum Access
    Replies: 12
    Last Post: 11-04-2015, 09:00 AM
  3. Replies: 5
    Last Post: 12-15-2014, 03:34 AM
  4. CrossTab Query Max X Returns
    By systems013 in forum Access
    Replies: 5
    Last Post: 11-04-2011, 01:12 PM
  5. Query returns duplicates
    By RobRay in forum Queries
    Replies: 3
    Last Post: 10-26-2010, 01:38 PM

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