[ms office - 2007] MS Access 07 - Relationships problem

[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

[MattMitchell]

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!

[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

[MattMitchell]

Make the lookup's data source type a table/query, and have something like this for the source:

 

SELECT tblConsumables.ConsumableName
FROM tblConsumables INNER JOIN tblCompatibility
ON (tblConsumables.ConsumableName = tblCompatibility.ConsumableID)
WHERE tblConsumables.ConsumableName = ;

[edd]

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

 

Thanks

Edd

[MattMitchell]

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

 

WHERE tblConsumables.ConsumableName = Forms![Order More Stuff]![ConsumableCombo]

 

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.

 

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.

 

Good luck,

Matt

[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

[MattMitchell]

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...

[Jamo]

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

 

Tables:

 

Printer

CartridgeType

 

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.

 

CartridgeStock

 

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

[edd]

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

[Jamo]

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

FROM PrintersCarts

WHERE PrinterID = ComboBox1.Text

[Jamo]

Make the lookup's data source type a table/query, and have something like this for the source:

 

SELECT tblConsumables.ConsumableName
FROM tblConsumables INNER JOIN tblCompatibility
ON (tblConsumables.ConsumableName = tblCompatibility.ConsumableID)
WHERE tblConsumables.ConsumableName [b]LIKE [/b];

 

This could work, but change the = for LIKE on the last line.

[edd]

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

[Jamo]

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

 

You do but ConsumableID is listed like this:

 

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.

[edd]

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

[Jamo]

I can understand what you mean there but a one to many relationship is as my example in database terms. Adding the one to many in the relationships box helps preserve things like referential integrity etc

 

The compatability tables only needs to hold consumable IDs and printer IDs so just 2 data fields.

 

The printer table could hold the location table as well as you cant have one physical printer in more than one location.

[edd]

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

[Jamo]

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

 

If then you did the printer key by serial number then add a single location field to that. It would make making queries and reports so much easier in the long run.

[MattMitchell]

I'd still be inclined to have the compatibility records as

 

printer1 cart1

printer1 cart2

printer2 cart1

printer3 cart4

printer3 cart1

printer3 cart3

 

rather than lumping all the values into one field (and having separate printertype and printerinstance type tables as Jamo has).

 

Otherwise I think you'll find you're looking for a way to select just one item from a comma-separated list of cartridge types! Also, to be honest, it'll work a lot better with the allocation side of things.

 

Just how I'd do it anyway!

 

Matt

[deanc]

then you will need a printer_type table between printer and printer_consumable with another one-many.

 

the printer will store the name and location of the printer

 

the printer type will store which carts are compatible

 

A printer can only be of one type and a printer type applies to many printers.

 

Dean

[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