none
Query Returns Incomplete Results RRS feed

  • Question

  • I am attempting to build a database for a warehouse operation. I am essentially working with 3 tables.

    Table A (Daily transactions) has fields A1, A2, A3

    likewise Table B (Supplier Info.) has fields B1,B2,B3 and the same applies for table Table C (Item Info.)

    Table A is the most active table as it records daily transactions. Table B and C mostly have fixed information that is drawn upon to populate records in Table A, when users enter data via a form (let's call it Form A) which is bound to Table A.

    Form A has two combo box objects, which are both drop down lists drawing data from Table B and Table C. and storing it in fields in Table A. To illustrate, a user may want to record a transaction whereby a specific supplier listed in say Field B2 - (Supplier name) has delivered a specific item also listed in say field C2 - (Item description). The user is able to select these two values from drop down combo boxes in Form A and store the values in fields A2 and A3 in table A.

    Here are my issues:

    1. after the user saves the record via the form, it is not the literal "Supplier name" from Field B2 or "Item   description" from field C2 that get stored in field A2 and A3 in table A, but rather their corresponding ID numbers from Table B and Table C. I did some searches around and found this may not necessarily be unusual.

    2. My biggest problem is that when I run a query to show me, for each date (lets say this is field A1 in table A), what the item description and supplier name was, I only get results showing records in table A for which there is a supplier. If there was no supplier indicated in the Table A record, the result is not displayed. If it was the literal text and not IDs being stored in Table A, i wouldn't even need to include Tables B and C in the query as all the data would be displayed in table A. please help.

    Thursday, July 19, 2012 1:30 PM

Answers

  • 1. This is entirely normal, and much more efficient than storing the names/descriptions in table A. An ID number only takes up only 4 bytes, while a name or description can easily be 30, 40 or more characters long.

    2. In the query based on tables A, B and C, double-click the join line between table A and table B. Select the option to include ALL records from table A and only those records from table B where the join field is equal, then click OK. Similar for the join line between tables A and C.


    Regards, Hans Vogelaar

    • Marked as answer by mwanjearthur Thursday, July 19, 2012 2:50 PM
    Thursday, July 19, 2012 2:23 PM

All replies

  • 1. This is entirely normal, and much more efficient than storing the names/descriptions in table A. An ID number only takes up only 4 bytes, while a name or description can easily be 30, 40 or more characters long.

    2. In the query based on tables A, B and C, double-click the join line between table A and table B. Select the option to include ALL records from table A and only those records from table B where the join field is equal, then click OK. Similar for the join line between tables A and C.


    Regards, Hans Vogelaar

    • Marked as answer by mwanjearthur Thursday, July 19, 2012 2:50 PM
    Thursday, July 19, 2012 2:23 PM
  • Brilliant. it worked. thanks for taking the time out to help a 'newbie'.
    Thursday, July 19, 2012 2:51 PM