Results 1 to 15 of 15
  1. #1
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23

    Post Blank query when linked to certain table

    Hi everyone, I am encountering a blank query. I know some other people may have posted it too, but my problem doesn't look quite the same. It works fine when I only linked to Table "04_Purchase_Orders" and other tables, it works fine alone too. But it shows completely blank like shown below when I try to link to Table "06_Inventory", which is also shown below. The item "Parts Name" of the two tables are joint, but I don't see how it affects the query. As you can see, all three items of the query are drawn from 04_Purchase_Orders, not even from the problematic table. So, I really do not understand what is going on. I hope there is a solution to this other than deleting and re-creating the entire table 06.



    Does anyone have any idea? Thanks! The SQL of the query is shown below.

    SELECT [04_Purchase_Orders].[Parts Name], [04_Purchase_Orders].Supplier, [04_Purchase_Orders].[Transaction Date]
    FROM 04_Purchase_Orders INNER JOIN 06_Inventory ON [04_Purchase_Orders].[Parts Name] = [06_Inventory].[Parts Name];
    Attached Thumbnails Attached Thumbnails access_2.png  

  2. #2
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    Hi all, I would like to add that a query with (Table_06 alone / Table 06 with other tables) work just fine, the problem seems to only appear when the query has both Table 06 and 04. I tried Compact and repair database, but it does no good. And the screenshot of Table_04 is also attached for your information. Thanks a lot!!
    Attached Thumbnails Attached Thumbnails access_3.png  

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Would need to see the actual data from part name field in both tables- perhaps they are not spelt the same or perhaps the field in the inventory table is a lookup field, in which case the field is poorly named and should be joined to the Part id field in the other table

  4. #4
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Bad design again. The purchase order table should have Parts ID as foreign key and not Parts Name. The tables must be linked on Parts ID.

    If you purchase multiple parts at the same time, you need to split the purchase table into 2 tables.

    As mentioned before, the inventory table is redundant any way.
    Groeten,

    Peter

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think the Parts Name field in 04_Purchase_Orders is a lookup field, it displays the part name but actually stores the PartID. Recommend you remove the lookup; try for now to link to Part ID field in Inventory.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    Hi Vlad and Peter, Thanks for the help! I have unlinked the Parts Name as you suggested. SO, did you mean we shouldn't link the Parts Name at any circumstances, and only link the ID(the field that is a AutoNumber automatically created when the table was created, to the Number field that I manually create on another table)? I had no idea. Why should we only link it that way? I was trying to show the names of the parts in the Purchase Form since employees wouldn't know what they are buying when only looking at the Parts ID. To resolve this issue, is it possible for me to link the tables as the way you suggested and then create a column that shows the corresponding names of the Parts ID (and then perhaps hide the ID column)?

    The query worked with the first 4 fields when the tables are linked by the way you mentioned. But then when I try to add another field "Current Quantity" it shows the error below. I am not sure it still refers to Parts Name, though since the field I add merely shares the same table with Parts Name. On the other hand, the relationship of my database is attached. Do you think I should delete all the joints that aren't linking to IDs?
    Attached Thumbnails Attached Thumbnails access_4.jpg  

  7. #7
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    And yes Vlad and CJ, the Parts Name field in 04_Purchase_Form is a lookup field which limits the input to existing Parts Name in 06_Inventory. Does it make a problem? What did you mean by "displays the part name but actually stores the PartID."? Is it actually possible? I thought the lookup field simply works identically to the one is Excel!

    Thanks.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Ian,

    Please review this article as it is one of the most quoted when advising not to use lookup fields in tables:
    http://access.mvps.org/access/lookup...urs%20to%20you).

    The (record)source of the Purchase form does not need to include the inventory table. Bind a combo box to the PartsID field (notice I use no space or special characters in the field or object names), set its row source to come from the inventory table with two columns, ID and Name, make the first one hidden by setting its width to 0 and you are off to the races, accomplishing what the lookup field in the table does for your users in a FORM without the problems.

    The combo settings: Row Source: "Select [Parts ID],{Parts Name] from 06_Inventory Order By [Parts Name];" (see how I had to type three sets of square brackets because you have spaces in the fields names ??), Column Count:2 , Column Widths:0";2" .

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    Hi Vlad, Thanks for yout advice! I tried to work it myself and it kinda worked. Can you check the screencap and see if that will work? All the relationships between tables are now cancelled since I kinda want to have a fresh start. And, am I correct to say that I should not link tables with relationships that aren't connected to the AutoNumber field?
    Attached Thumbnails Attached Thumbnails access_5.png  

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Ian120 View Post
    ...Can you check the screencap and see if that will work?...
    Not quite what Vlad did advice.
    You must not have any Part Name info in purchases table, even more to have a lookup for it there! In purchases table, the only part info you will have there, will be part id! You work with tables only when you design the database. After that, all interactions mus be through forms or reports. What Vlad described, was using a combo box control in form to display part name instead of part id the combo would be linked to.

    And in case you want to use query (e.g. to use it as source of report), then there you can link part table with purchases table, and display any fields from both tables as result.

  11. #11
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    Hi ArviLaanemets, I see what you mean here? So, should I change the DIsplay Control under Lookup in that column from COmboBox back to textbox or list? I think I get it now. We shouldn't mannually interact with the tables, but only the forms, right?

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, that's correct, have the Lookup set to TextBox and implement the combo setup in the form(s).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    Hi ArviLaanemets, thanks for the advice. Now my tables only contain Part_ID instead of Part Name. But since I will be needing to inspect the tables (since I can view data like 20rows at a time and can export it to Excel), it will be best if I can show the corresponding Parts Name in a column next to the Part ID. Is it good for me to do it in the tables or better to make this is a query?

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    make a query. Tables are just for storing data. Not presentation of any kind - and exporting a view of two or more tables is a form of presentation

  15. #15
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    ok thanks!

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

Similar Threads

  1. Replies: 10
    Last Post: 03-22-2021, 07:59 AM
  2. Generate report with blank linked table fields
    By e-support in forum Reports
    Replies: 14
    Last Post: 01-06-2017, 04:11 PM
  3. Replies: 1
    Last Post: 03-11-2014, 11:30 AM
  4. Replies: 2
    Last Post: 12-12-2013, 02:13 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

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