+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
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. ...
  1. #1
    edd
    edd is offline

    Join Date
    Apr 2009
    Posts
    10
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    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.

    Thanks
    Edd

  2. #2

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    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!

  3. Thanks to MattMitchell from:

    edd (14th May 2009)

  4. #3
    edd
    edd is offline

    Join Date
    Apr 2009
    Posts
    10
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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
    Attached Images Attached Images

  5. #4

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    Make the lookup's data source type a table/query, and have something like this for the source:

    Code:
    SELECT tblConsumables.ConsumableName
    FROM tblConsumables INNER JOIN tblCompatibility
    ON (tblConsumables.ConsumableName = tblCompatibility.ConsumableID)
    WHERE tblConsumables.ConsumableName = <insert control name for printer id on form here>;

  6. #5
    edd
    edd is offline

    Join Date
    Apr 2009
    Posts
    10
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Could you explain how to change the Lookup's data source type to a table/query?

    Thanks
    Edd

  7. #6

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    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

    Code:
    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
    Attached Images Attached Images

  8. #7
    edd
    edd is offline

    Join Date
    Apr 2009
    Posts
    10
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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
    Attached Images Attached Images

  9. #8

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    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...

  10. #9
    Jamo's Avatar
    Join Date
    Jan 2009
    Posts
    1,365
    Thank Post
    66
    Thanked 178 Times in 150 Posts
    Rep Power
    62
    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

  11. #10
    edd
    edd is offline

    Join Date
    Apr 2009
    Posts
    10
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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
    Attached Images Attached Images

  12. #11
    Jamo's Avatar
    Join Date
    Jan 2009
    Posts
    1,365
    Thank Post
    66
    Thanked 178 Times in 150 Posts
    Rep Power
    62
    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

  13. #12
    Jamo's Avatar
    Join Date
    Jan 2009
    Posts
    1,365
    Thank Post
    66
    Thanked 178 Times in 150 Posts
    Rep Power
    62
    Quote Originally Posted by MattMitchell View Post
    Make the lookup's data source type a table/query, and have something like this for the source:

    Code:
    SELECT tblConsumables.ConsumableName
    FROM tblConsumables INNER JOIN tblCompatibility
    ON (tblConsumables.ConsumableName = tblCompatibility.ConsumableID)
    WHERE tblConsumables.ConsumableName LIKE <insert control name for printer id on form here>;
    This could work, but change the = for LIKE on the last line.

  14. #13
    edd
    edd is offline

    Join Date
    Apr 2009
    Posts
    10
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I have a one to many relationship between the printers table and consumables table to the compatiability table.
    Attached Images Attached Images

  15. #14
    Jamo's Avatar
    Join Date
    Jan 2009
    Posts
    1,365
    Thank Post
    66
    Thanked 178 Times in 150 Posts
    Rep Power
    62
    Quote Originally Posted by edd View Post
    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.

  16. #15
    edd
    edd is offline

    Join Date
    Apr 2009
    Posts
    10
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    What I am trying to do is to reduce duplication of printers and consumables. As I have printers that use the same consumables.



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Relationships with local schools
    By FreeWill in forum General Chat
    Replies: 21
    Last Post: 20th May 2008, 03:33 PM
  2. Trust Relationships and DeepFreeze
    By AdamWilden in forum How do you do....it?
    Replies: 6
    Last Post: 4th February 2008, 12:16 PM
  3. Sales: building up relationships
    By contink in forum General Chat
    Replies: 8
    Last Post: 30th January 2008, 11:17 AM
  4. My Docs access problem
    By Ste_Harve in forum Windows
    Replies: 2
    Last Post: 30th August 2007, 01:32 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •