edd

I creating a Consumables Database for Printer Toners to book in and out and amend stock. The tables I have are: Consumables Printers Location Orders Compatibility Purchased Consumables I am trying to create a query where the Purchased Consumables form is taking the stock number from the Consumables table and places this into the field "lblCurrentStock". I have written this in SQL: SELECT tblConsumables.QuantityStock FROM tblPurchasedConsumables INNER JOIN tblConsumables ON tblPurchasedConsumables.luCurrentStock=tblConsumables.QuantityStock WHERE ((([Forms].[frmPurchasedConsumables].[cmbConsumableName].[Value])=[tblConsumables].[ConsumableName])); But it does not return anything. Thanks Edd

The information being held in the Locations Table will now be stored in the Printers Table. Also the Consumables Tables will now have one consumable to the printer, like Jamo and Matt has mentioned above, as I got the combo box to only show the compatiable cartridges to the selected printers. Thanks guys for your help. Edd

We have more than one model of the same printer onsite and so it can be in many locations.

What I am trying to do is to reduce duplication of printers and consumables. As I have printers that use the same consumables.

I have a one to many relationship between the printers table and consumables table to the compatiability table.

What i am trying to do is to have it that the combo box in the order form to only show compatiable consumables from the Printer Name in another combo box, attached is a picture of the order form. In the Consumable Table there are single entries of every consumable. Shown in diagram. In the Compatiability Form you select the consumables from a multiple choice combo box. The Consumable Name field shows every consumable that has been chosen in combo box. There will only be single selections that the user will be selecting from a list of consumables that are compatiable with the printer. The user will be making multiple transactions, the single transaction will only have 1 consumable recorded. Thanks Edd

I have been able to get the query to work on single value in a single field, but I would like it to be able to display the multiple values in the single field, for example the Consumable Name has these values (Q6430A, Q6431A, Q6432A and Q6433A) inside the one field. I would like the query to display those 4 toners in the combo box to be selected for ordering. The Compatiablility Table is shown in the diagram. The Consumable Name is a multiple selection combo box. Does anyone know how to do this? Thanks Edd

Could you explain how to change the Lookup's data source type to a table/query? Thanks Edd

I have hit another problem creating a query where I would like to reduce the choices in the Consumables drop down menu, so that it only shows the compatiable consumables to the printer selected in a drop down menu in the form. The compatiable consumables are held in another table. There are lookups on the consumables and printers in the Compatiability table. Thanks Edd

I have a dilema where I am trying to create a consumables database, where we book in and out toners. Where I am having trouble is with the relationships between 2 tables (printers and consumables) which a many to many relationship, which is not possible. The first relationship is from the Printers table to the Consumables table, where a printer can have many consumables (Colour printer). The second relationship is between the Consumbles table to the Printers table, where the consumable can be used by more than one printer (one cartridge works with many printers). Has anyone got a database similar to this or can shed some light on this. Thanks Edd