Office Software Thread, MS Access 07 - Relationships problem in Technical; I have a dilema where I am trying to create a consumables database, where we book in and out toners.
13th May 2009, 02:55 PM #1
- Rep Power
MS Access 07 - Relationships problem
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.
IDG Tech News
13th May 2009, 03:53 PM #2
You need to have a third table, called something thrilling like printers_consumables_compat or whatever, with two columns, printerid and consumid. Each should be indexed as non-unique.
Then your printers table should have a 1-to-many rel with this extra table, and so should the consumables one.
For lookups and the like in forms (or in datasheet view if you're doing things that way), Access will let you create a query-based list for a combo box to give a human-readable lookup.
Hope this helps - gimme a shout if you're still stuck!
Thanks to MattMitchell from:
14th May 2009, 01:43 PM #3
- Rep Power
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.
14th May 2009, 03:12 PM #4
Make the lookup's data source type a table/query, and have something like this for the source:
FROM tblConsumables INNER JOIN tblCompatibility
ON (tblConsumables.ConsumableName = tblCompatibility.ConsumableID)
WHERE tblConsumables.ConsumableName = <insert control name for printer id on form here>;
14th May 2009, 04:46 PM #5
- Rep Power
Could you explain how to change the Lookup's data source type to a table/query?
14th May 2009, 06:29 PM #6
Open the form in design view, and select the combo box in question.
If it's not already visible, pull up the control's property sheet by pressing Alt-Enter, and click on the "Data" tab.
You should see something a bit like the first picture. Note the name of the control near the top of the sheet (Combo7 in this picture) - I'll assume it's called ConsumableCombo but it's probably something different on your form. You also need to know your form name - I'll assume it's Order More Stuff.
"Row source type" should be "Table/Query" (it probably is already in your database.
"Row source" is the query that pulls out the list of compatible consumables. To limit it to show only the entries you want, you need to add a WHERE clause to the SQL, in your example something like
If you'd rather do this in Query Builder, you need to add a criteria (sic) constraint by adding the printerid field to the query and specifying "Forms![Order More Stuff]![ConsumableCombo]" in the Criteria field.
WHERE tblConsumables.ConsumableName = Forms![Order More Stuff]![ConsumableCombo]
So far so good! However, the only catch is that Access is a bit stupid, and you have to tell it to change the combo contents when you view a different printer on the form. To do this, select the form itself by clicking in the very top left corner of the form (where the two rulers meet).
On the properties for the form, click on the "Event" tab, then click in the entry for "OnCurrent" and use the macro builder (click on the "..." icon) to add a macro. The macro is Requery and in the bottom, where it says "Control Name" enter the name of your control (ConsumableCombo or Combo7 in my mockup or whatever it is that you've used).
That's it! You should be good to go now.
15th May 2009, 01:21 PM #7
- Rep Power
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?
15th May 2009, 01:38 PM #8
What are you trying to do with it - do you have a separate record for each consumable type in the compatibility table? Do you want people to make multiple or single selections from the form? Do you want to enter multiple orders or create one order record with a field containing multiple comma-separated items? It all seems a bit unclear...
15th May 2009, 01:46 PM #9
I have created an access 2003 database which handles all the things you are talking about its a bit messy at the moment but it works.
I have it set up as follows
Both of these tables contain "profiles" of the printers or cartridges, that is just a printer identified by Serial No as primary key and cartridges identified by cartridge long number ie Q5949X for a 49X HP toner.
This table contains individual stock items and has a one to many relationship with the cartridgetype table. Ie One cartridgetype can have many of its type in stock.
Once used the cartridges get put in the UsedCartridges table where all the information is collated together so there is an element of redundancy if the cartridge profile is ever deleted.
Now that sounds like a load of confusing waffle here so if anyone wants I can post a blank copy up for people to look at lol
15th May 2009, 02:44 PM #10
- Rep Power
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.
15th May 2009, 03:46 PM #11
Will be difficult to formulate a query for that. Your table design is difficult because you don't have a one to many relationship with the compatables to the printers, they are listed in one field. It would be easier if printers had a separate table and the compatablilty to the other table was produced by linking the primary key of the printer table to the primary key of the cartridge table.
In my database I had Printers, Cartridges then a table called PrintersCarts (Compatability would be a much better name now I see ) basically that table just has the primary key of a printer then its cartridges, its much easier then to filter the results you require as you can pull a query from the PrintersCarts table to produce what you want.
SELECT PrinterID, CartridgeID
WHERE PrinterID = ComboBox1.Text
15th May 2009, 03:51 PM #12
This could work, but change the = for LIKE on the last line.
Originally Posted by MattMitchell
15th May 2009, 03:54 PM #13
- Rep Power
I have a one to many relationship between the printers table and consumables table to the compatiability table.
15th May 2009, 04:00 PM #14
You do but ConsumableID is listed like this:
Originally Posted by edd
PRINTER1 ¦¦ Q1234D, C1234D, Q2345D etc etc ¦¦
If it was one to many it should be listed like:
PRINTER ID CARTRIDGE ID
PRINTER1 ¦¦ Q1234D
PRINTER1 ¦¦ C1234D
PRINTER1 ¦¦ Q2345D
PRINTER1 ¦¦ etc
PRINTER2 ¦¦ etc
Therefore one printer ID can have MANY cartridge ID's.
15th May 2009, 04:03 PM #15
- Rep Power
What I am trying to do is to reduce duplication of printers and consumables. As I have printers that use the same consumables.
By FreeWill in forum General Chat
Last Post: 20th May 2008, 03:33 PM
By AdamWilden in forum How do you do....it?
Last Post: 4th February 2008, 12:16 PM
By contink in forum General Chat
Last Post: 30th January 2008, 11:17 AM
By Ste_Harve in forum Windows
Last Post: 30th August 2007, 01:32 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)