Blank query when linked to certain table

[Ian120]

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];

[Ian120]

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!!

[CJ_London]

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

[xps35]

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.

[Gicu]

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,

[Ian120]

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?

[Ian120]

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.

[Gicu]

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,

[Ian120]

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?

[ArviLaanemets]

...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.

[Ian120]

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?

[Gicu]

Yes, that's correct, have the Lookup set to TextBox and implement the combo setup in the form(s).

Cheers,

[Ian120]

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?

[CJ_London]

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

[Ian120]

ok thanks!