I have done a few databases in MS Access 2007 and I normally do OK when coming up with a structure and relationships. etc.
A friend of mine has also done one for his new venture, but is having trouble and asked me if I could help him out. Like a fool I said I would have a look at it. :)
He uses "part number" to seach for stock and not description or category, if that part number is not in stock he thinks he has none. but if the exact same item is sourced from a different supplier, it has a different part number and therefor he has stock of the item, just not the same part number.
If I make a form bound to "tblItems" (see attached showing table design), I can put an unbound combobox on it based on part numbers and when a part number is selected, it opens that particular item record, and I can get the form to show me what stock I have, but how can I get the form to show me what stock I have of the same item which has a different part number? In other words, If I have no stock of a certain part number how can I view all other part numbers that relate to the exact same item?
I'm probably missing something simple, but I just can't seem to grasp it.
I have attached a picture of my table design so far. I think I may need another table just for part numbers, but I am unsure of how to link it to the items table.. (all primary keys are autonumber)