Many to Many Editable Query

[Bill Bisco]

I have 3 tables, tblOptions, tblProcesses, and tblStations. Every Process has 1 Option while 1 Option can belong to many Processes. Each Process belongs to 1 Station while 1 Station can have many Processes.

So essentially there is a many to many relationship between Options and Stations. (The same Option can exist in many Stations while the same Station can have many Options).

So, I'm trying to make a query that displays all the Options that appear in 1 particular station. My SQL code is as follows:

SELECT tblStations.[Station Name], tblStations.Side, tblOptions.OptionID
FROM tblStations, tblOptions
WHERE (((tblStations.[Station Name])=[Forms]![frmWelcomeForm]![cboProcessSelectTogetherLS&RS]) AND ((tblStations.Side)="LS"));

Anyway, this obviously doesn't work because there is no connection between tblStations and tblOptions, it goes ahead and displays the Options that don't belong to that Station.

I don't want to use a Totals Query because I want the results to be editable. Any help is appreciated.

Sincerely,
Bill

 

[wazz]

put all three tables into the query grid and add the fields you need. leave out the criteria to begin with. make sure you see what you will want then start adding criteria bit by bit until you get what you need. it sounds very straightforward, but you'll need all three tables.

 

[Bill Bisco]

Thanks for the Reply Wazz.

Here is what I'd get without any criteria and by displaying all 3 tables

(for those who can't see images, it basically repeats BASE, SNRF, and 6CVT several times, then a # (BASE is always 1, SNRF always .474), and the Station).

This in of itself is annoying but tolerable. However, I cannot edit any of the information. I might want to change SNRF to be .874 and subsequently have every SNRF to be .874.

Anyway, putting criteria on a format that won't allow you to edit data will not display a format that will allow you to edit data.

So it seems to me the conventional way of displaying data won't save me here. Does this make sense? Or do I need to upload my database so that understanding will be easier?

Sincerely,
Bill

 

[WIS]

I have 3 tables, tblOptions, tblProcesses, and tblStations. Every Process has 1 Option while 1 Option can belong to many Processes. Each Process belongs to 1 Station while 1 Station can have many Processes.

So essentially there is a many to many relationship between Options and Stations. (The same Option can exist in many Stations while the same Station can have many Options).

So, I'm trying to make a query that displays all the Options that appear in 1 particular station. My SQL code is as follows:

SELECT tblStations.[Station Name], tblStations.Side, tblOptions.OptionID
FROM tblStations, tblOptions
WHERE (((tblStations.[Station Name])=[Forms]![frmWelcomeForm]![cboProcessSelectTogetherLS&RS]) AND ((tblStations.Side)="LS"));

Anyway, this obviously doesn't work because there is no connection between tblStations and tblOptions, it goes ahead and displays the Options that don't belong to that Station.

I don't want to use a Totals Query because I want the results to be editable. Any help is appreciated.

Sincerely,
Bill

Bill

You have 2 problems here:

1. Many to many - you have to create another table with 2 flds (the join flds in the 2 many to many tbls). Then re-create the qry with this tbl added. This will get rid of the duplicates.

2. For a qry to be updateable, you can only have one join fld set to "Indexed Duplicates Allowed" - all the rest have to be set to "Indexed No Duplicates Allowed"

 

[Bill Bisco]

Thanks for the reply WIS,

All the Primary keys are set to Indexed, no duplicates allowed, and I have tried making another intermediary Table called StationOptions.

I'm still running into the problem of my data repeating itself. For example in this picture:

Station Name and Side are repeated and therefore the OptionID and ProposedMix cannot be changed even though they come from different tables.

I'm uploading a sample database demonstrating what the desired result I want to see (except that it's not editable) and my current attempt at getting the result that I want, which is not working.

As always, any help is appreciated.

Sincerely,
Bill

 

[WIS]

Thanks for the reply WIS,

I'm uploading a sample database demonstrating what the desired result I want to see (except that it's not editable) and my current attempt at getting the result that I want, which is not working.

As always, any help is appreciated.

Sincerely,
Bill

Bill

I've had a quick look @ yr tbls and it seems that there are a no of issues there to do with fld names and db normalisation (eg repeating flds in a tbl).

Here are my suggestions:

1. In general, get rid of all spaces in fld names eg use xxxID instead of xxx ID. VBA doesn't like spaces.

2. In a couple of tbls you have a ? after the fld name - SQL falls over when it sees this. # - used for dates in VBA and qrys.

In particular:

tblProcesses - remove all the Station 1, 2 etc and put in another tbl

eg tblProcessesSubform

ProgressID, Station, Robot, StationComment, RobotComment.

ProgressID links back to tblProcesses and if there are only 2 or 3 or the whole lot, only the required no of records will be produced. This can be put in a subform linked by ProgressID.

tblOptions - not sure about the text for PK - I would think about an autonumber PK for OptionID and another fld name for the OptionID.

tblStationOptions - just needs 2 flds, StationID and OptionID.

tblOptions - Jan, Feb flds - I would be inclined to put them in a separate tbl (as per tblProcesses). If there is only 1 mth you only get 1 record instead of an extra 11 empty spaces).

Goto Tools->Options->General->CompactOnClose and tick. This got your db from 1200kb to about 300kb.

It is imperative that the db is normalised as much as practicable - a lot of time can be spent re-organising a db where care hasn't been taken on this.

I think that you have to resolve these issues before you can move on successfully.

I would be interested in what others on this forum think of my suggestions as I've learnt that there are several ways to achieve a result, all can be valid.