Unanswered: Relationships and querries and subforms HELP Please
I'm having a little problem with a query I'm trying to construct; I have a form with client orders in it in a subform based on the order details query,
The client orders table has the following fields
InternalID, (this is what I'm trying to link to the stock/equipment table)
The stock/equipment table has the following:
InternalID, (this is what I want to be able to join to the order details table)
Sold, (Want this to appear on order details subform)
Date Sold, (Want this to appear on order details subform)
Ref, (Want this to appear on order details subform)
I thought if I created a relationship between the two tables via the internalID's, Then amended my order details query to include the stock/equipment table with the Sold, Date Sold, Ref fields added. I would then be able to enter an internalID on the internalID field on the order details subform and it would allow me to mark that item as sold in the stock/equipment table via that query.
But it didn't, instead I get no order details what so ever, or if i mess around with it i get the results replicated 1000's of time so i end up with 2560 results.
I'm doing something wrong
Any ideas please, my testdb is attached with the problem
I need the db to show me the orders taken by each employee for each individual customer, like it does at the mo, if you click an employee in the top form it displays the order details for that client, say that client comes back again and another employee takes and order all the employee has to do is select there name in the orders subform and then add the relivent order details in the suform below it,
Can this be done, all i need the order details subform to be able to do is display the sold, date sold, ref fields from the stock/equipment table so that i can mark items as sold, along with the date they were sold as well as the cutomer "ref" number.
Thanks hope you can help some more, could you post the amendments to the db as an attachement so i could look at the db.
Thanks for the reply i'll definitly have a look and see what youve done, thanks for the help, when you say "You may want to revisit your table structure" can you explain a little more please as im new to access and would love any assistance you can give.
Cracked it, its not exactly how i wanted it but it works, when i now enter the InternalID in the order details subform and expand the table underneath it shows the details for that item so that i can now mark the item as sold.
If you have any sugesstions on how i can improve it, table layout etc etc it would be greatly appriciated.
Looks good to me. The only suggestion I would make is joining the tables on differant fields.
Typically you would want to join tables from a Primary Key (usually autonumber if possible) to a Foreign Key (type long integer). It makes it a more efficient DB.
You've actually done that in the tbl_OrderDetails but not in tbl_stock/equip
Using your setup, you will be storing duplicate info in 2 tables (like in tblLocation & tblStock/Equip). The duplicate data being potentially 50 Char text strings. So storing long integers as the foreign key field would be much more efficient in tbl_stock/equip. The way you have it may work just fine depending on the scope of your DB.
Hope that helps. Let me know if you need clarification. (Was up all night with a sick dog, so I'm a little groggy today.)
thanks for the advise, im pretty new to access so im not 100% sure what youu going on about but i think i have an idea. is there anyway you point me in the right direction to improving the design.