Need help with Sql criteria and a Many to Many relationship

[David8]

I'm a beginner and mentally wrestling with a many-to-many relationship.

I have an IT equipment inventory database. The main table is All Equipment, and two lookup tables that it currently references are Manufacturer (where the choices for the Manufacturer field in the main table come from) and Item Type (where the choices for Item Type come from).

When a new piece of equipment is being entered into the database I want the Item Type to narrow down the list of items available in the Manufacturers lookup.

On the Manufacturers table I created a Item Type field which allows multiple choices. When you create a new Manufacturer, you simply tick which Item Types they produce in the Item Type field. The choices for that field come of course from Item Types table.

As a first step I then tried to create a criteria which reduces the options available under Manufacturer when entering a new items of equipment to the database. I started by trying to specify the Item Type myself within the criteria. So I tried to create a criteria that basically said "offer this manufacturer in the lookup list if the Item Types field within the Manufacturers table includes the value "Projectors". Put another way I wanted the criteria to look down the Manufacturers listed in the Manufacturers table and say for each one: 'does this manufactuer include "Projector" in the Item Type field'.

But I ran into a big snag. Obviously within the Manufacturers table, Item Types is a muli-value field. For example Benq produce both Monitors and Projectors, so their entry in the Item Type field is "Monitor, Projector". But Access refuses to run SQL criteria including the WHERE or HAVING operators on a Multivalue field. In fact going into the query builder wizard and double clicking on the Manufacturers table I could only select the Manufacturers ID, or name. The Item Type field wasn't listed presumably because it was Multi Value.

I don't understand why I couldn't build something based on LIKE to treat the multivalue field as a string and effectively say "YES (or TRUE) that string contains the value "Projectors", or "NO (or FALSE) that string does not contain "Projectors". But I couldn't do it because my SQL syntax understanding is absolutely hopeless. Yes I have googled, but the context of people's explanations is wrong, or I don't understand what they are saying, or how to adapt it, because of my lack of SQL. I am very keen to learn more about how to build and use SQL statements, but will need a little help before the learning process takes off.

It has occurred to me that Item Types and Manufacturers have a many-to-many relationship. All Item Types are produced by many Manufacturers, and several Manufacturers produce more than one Item Type. I've read instructions on the internet that tell you to use a linking table with each field referenced via the others foreign key.

However this is a theoretical explanation, and I don't understand it in practice. a) That is the structure of the linking table, but what data would fill it? Edit: the only thing I can think of is that it would contain:

ItemType1 NameofManufacturer1
ItemType1 NameofManufacturer2
ItemType1 NameofManufacturer3
ItemType1 NameofManufacturer4
ItemType2 NameofManufacturer5
ItemType3 NameofManufacturer2
ItemType3 NameofManufacturer4
ItemType4 NameofManufacturer6

But I maybe wrong and anyway what a tedious way of linking data.

b) Having established this many-to-many relationship, how would it help me with my attempt to narrow down Manufacturer based on Item Type? Would it help me at all?

 

[JANR]

Attached is a little demo on how to resolve a many-to-many situation. As for for your question on how to resolve which manufacturer have which items, you use InnerJoins between tblManufacturer, tblItems and the junction table you setup to resolve the many-to-many senario.

Look at the query "qryFindItems" and see how it works.

I'v also included a form "frmSearch" which demonstrate cascading comboboxes you can use to filter out items based on a selection of different manufactures.

Hope this helps

JR

 

[David8]

Before it gets too long I wanted to say thanks for your response, which was brilliant, since your example was about as bang-on-the-nose an answer as could have hoped for.

I recreated the tables and relationships from a blank database start to make sure I could do it myself. In the past I've always relied on using the wizard for a lookup field to set up a relationship, so although very easy, setting up a set of relationships manually was a good exercise.

I would have wanted to say more, but I haven't really had any time to properly digest the inner workings of your query and forms, but I'll be sure to do so as soon as I can.

 

[David8]

I'v also included a form "frmSearch" which demonstrate cascading comboboxes you can use to filter out items based on a selection of different manufactures.

I urgently need help with this, please. I thought I'd understood what you did with frmSearch.

How did you get the Items list to pop open, once a Manufacturer had been chosen? How did you get the Items list to refresh if the Manufacturer choice was changed?

 

[John Big Booty]

In the After Update event of the calling combo put the following;

Me.Cascading ComboName.Dropdown

 

[David8]

In the After Update event of the calling combo put the following;

Me.Cascading ComboName.Dropdown

Thanks for your help, but as I said in the first post, I'm a beginner. So, where is the After Update event please?

EDIT: OK, worked that out, that was easy. I realise now why I spent so long on this. In JANR's example I was focussing on the second combo box. It didn't occur to me that it might be a feature of the first one that I was missing.

 

[David8]

OK, new problem. My database is not happy with the term Me. It says it cannot find the object Me. Is Me some kind of generalising jargon?

 

[John Big Booty]

In the View menu in the menu bar select Properties, or simply press the Alt + Enter keys.

A properties window will appear, put your form into design view and click in the control that you are interested in. Select the Event tab in the properties window and click in the After Update event or which ever event you are interested in. When you click in the row select Event Procedure from the list now click on the button with the three full stops on it (ellipsis) this will now open a VBA code window, in which you can insert the code.

 

[John Big Booty]

Just notice a small error in my code if your control name has a space in it (which should be avoided at all cost), use;

Me.[B][COLOR="Red"][[/COLOR][/B]Cascading ComboName[B][COLOR="Red"]][/COLOR][/B].Dropdown

If you type the code into the event rather than cut and past my example, as soon as you type Me. you should get a drop down list of the available controls on that form.

Also, you may need to first set focus on the combo.

 

[David8]

Thank you very much for your help. Actually when I click on the three dots I have to choose one of three options.

I chose the Code Builder, because it became clear to me that JANR had probably used the Visual Basic editor with the first combo box. I simply copied and edited the code in JANR's example, so it is now:

Option Compare Database
Option Explicit
Private Sub cboBuilding_AfterUpdate()
With Me!cboRoom
    .Requery
    .SetFocus
    .Dropdown
End With
End Sub

I do not understand this properly myself. Some options that look self explanatory are called using With. What Explicit means I have no idea.

Now, I do very much appreciate JANR's help, but when I said I was a beginner, why on earth did they not tell me about this VB and how to understand it? Didn't even mention it, and it was essential to a properly working solution. I hadn't even looked inside the VB editor until tonight.

 

[John Big Booty]

The With function is just the same as;

Me.cboRoom.Requery
Me.cboRoom.SetFocus
Me.cboRoom.Dropdown

 

[David8]

Now I'm having problems binding the second form field. It keeps making a ding noise, and at the bottom of the screen along the grey bar it says:

Control can't be edited, its bound to unknown field ... name of field I gave it. What might I have done wrong now?

 

[John Big Booty]

Wild guess; You've tried to bind the field to a control that does not form part of the Record Source for that form.

 

[David8]

As soon as I changed the name of the second combo box (the one I was binding) to match the name of the field in the table it was supposed to be bound to it started working.

I cannot view my form in Datasheet view (it will not show any data if I do). I really would like to.


EDIT: And just after I post that, it has just started working in Datasheet view. What is going on? Maybe I closed and re-opened the form I can't remember.

 

[David8]

No, it won't work properly in datasheet view. If I change the value for the first (unbound column) then all the records in that column change to match. Does data sheet view not work with unbound columns? I could make it bound (sort of). It does have a relationship with the column next to it. Its complicated. I'm tired and fed up. There is a lot to learn.

Buildings table: Building ID; Building Name
Rooms table: Room ID; Room Name; BuildingFK
All Items table: Item ID; RoomFK; (and loads of others, not relevant right now).

So in my form for All Items I want it to show the location. In the All Items table RoomFK is the most efficient means of storage as this references both the room and building data via the Rooms table.

But how should my Form handle Buildings. I want them shown on the form, but not stored separately from rooms on the table. I want a Building pre-lookup to narrow down the otherwise massive Rooms combo box. So I put in an unbound Building combo as a precursor to the Rooms combo and set up the cascade combo. But then the datasheet view doesn't work, apparently because of the unbound Buildings form field. I don't even know if that is the reason.

 

[David8]

I've realised, what I've got here is a problem of circular logic.

For any given field I want a choice in the Buildings form field to narrow down the choice in the Room form field. So the Room entry depends on the Building entry.

But thereafter, once entered, I want the building field to show the building that corresponds to the room stored in the room field. From this perspective what is shown in Building depends on what is the Room field.

So Room depends on Building, but then Building depends on Room. Impossible.

Is there any way out of this connundrum?

 

[David8]

What I need (I think) is a form that carefully distinguishes between the entry of new data and the display of existing data.

 

[John Big Booty]

Perhaps the sample here will give you some ideas.

 

[David8]

I'm actually in the other hemisphere I think, and need some sleep. So I can't examine the example too closely.

But I did notice that I seem to be able to select the Aberfoyle Park locality whichever state I choose in the first column, so either it isn't working correctly, or more likely I haven't understood it yet.

 

[John Big Booty]

I can't duplicate that. So I'd be rather interested to know how you achieved it