How to have a combobox to show ID's of stock that haven't been allocated

[MCCDOM]

Hi there,



I am looking to improve my form by getting the combobox containing StockID to filter out any values that have a AllocationID associated to them.

Currently I have an unbound combobox 'cboStockID' retrieving the values, to display, from a query that is linked to tblStock (see attachment). This shows all the StockID's, which I am concious of might cause double allocated data if I'm not careful and select an already allocated stock item.
There are two tables one tblStock and tblAllocated. These are linked in a relationship and each AllocationID has a StockID in the same row as it in the tblAllocated.

What would I be looking to enter into the query to apply this sort of filtering?

[John_G]

What you want is a list of all records in tblStock that do not have a corresponding record in tblAllocated.

Create a query that has tblStock and tblAllocated, linked on StockID. You need to make the join an OUTER JOIN, so right-click the join between the two tables, select Join Properties, then select the option that says "Include ALL records from tblStock...."

Include all the fields (or just the ones you need) from tblStock, and Stock_ID from tblAllocated. Set the criteria for the tblAllocated occurance of Stock_ID to Null.

Save the query, and use it as the rowsource for the combo box.

What the query does is retrieve all records from tblStock where there is NOT a corresponding (related) record in tblAllocated.

[rpeare]

There's something wrong with your data structure

Why are you tracking make and model in both tblAllocated and tblStock. That information should not change so you would only need the stockID in your table tblAllocated.
Similarly, you are carrying the first, last and office name of your staff in your tblAllocated, those pieces of information come from tblStaff and do not need to be repeated in tblAllocated, you just need the StaffID
The same goes for carrying the first name, last name and email address in the table tblPasswords.

Secondly you are claiming there is a one to many relationship between tblStock and tblAllocated, but I do not see anything in tblAllocated indicating an END to where that time is allocated. In other words if you give a keyboard to Person A on 1/1/2015 you should also have a termination date because if you enter a new record reassigning the same piece of equipment to Person B I would assume you'd want to know when that happened. Particularly if the piece of equipment does not go to another person but goes back into your inventory.

Without a way to tell when an allocation ends I don't know if you could create a query that would show only 'non assigned' items with any degree of accuracy.

[MCCDOM]

Thank you John_G for your reply. I have created the query like you said (see attachment) but all I get is a blank combobox now for StockID. The other columns show fine (see attachment). What might be causing this. Have I not created the query correctly?

[MCCDOM]

Hi rpeare, thanks for your advice on my database construction. I have deleted any unnecessary duplicates from the tables. With regards to end dates I haven't really given that process much thought. Would of just deleted the allocation when the item returned. I will keep your ideas in mind when I get round to that stage of the database.

[orange]

I recommend you get your table structures designed to meet your requirements before you start dealing with queries and forms.
Removing duplicates is not the issue. Preventing duplicates by means of design is. rpeare has provided very constructive info and it isn't something you keep in mind till you get there -- it's design.

If you want to review/work through a tutorial on design, see this one.
Good luck.

[MCCDOM]

Thank you orange for your valuable advise. I will look through that word document and have a good sort through my database.

[John_G]

Hi -

In your query, take the AllocationID field out.

[MCCDOM]

I've done that but it still only shows a blank combobox although it's a really short one now as though there is nothing to be displayed. I think I might have to follow Orange and Rpeare's advise on designing my database better before I continue with forms and queries. I appreciate your help and might call upon you later on when everything is in ship shape condition.

Many thanks

[rpeare]

Add the STOCKID from your tblSTOCK to your query, move the STOCKID from tblALLOCATED to the last field of your query and leave it as is.

However this relies on the following to work:
1. 1 and only 1 record for in tblALLOCATED for each record in tblSTOCK
2. DELETION of ALLOCATED records when the allocation is 'finished'

This is not good design, in fact it's horrible, because you will lose all your historical data if you delete an allocation record every time you move the resource.