Solved Combobox with multicolumn

[Atthe]

Hi All

I have a combobox with 3 columns (Bound Column 1) from tblAssemblies, the issue I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3. The user selects an entry from the drop down, but if they select an entry of which the first two fields are the same then when the user runs the report the query pulls data for both entries rather than the unique entry.

Row Source for the combobox

SELECT tblAssemblies.AssemblyMaterialNumber, tblAssemblies.AssemblyNumberSpec, tblAssemblies.Line FROM tblAssemblies ORDER BY tblAssemblies.AssemblyNumberSpec, tblAssemblies.AssemblyMaterialNumber;

I am aware this may be a data structure issue but I can't think of a way to deal with the problem

Thanks

 

[CJ_London]

you need a tblAssemblies.PK in your select code and bind the control to that

 

[Atthe]

you need a tblAssemblies.PK in your select code and bind the control to that

Yes that sounds like a good option but I already have PK set as AssemblyNumberSpec and one autonumber field in the table, Is there any way around this?

 

[MajP]

I would think you would want cascading combos to allow you to pick a line then the assembly from another.
But if not your where condition to open the report is likely

"tblAssemblies.AssemblyMaterialNumber = " & me.cmboName.colum(0) & " AND tblAssemblies.AssemblyNumberSpec = " & me.cmboName.colum(1) & " AND tblAssemblies.Line = " & me.cmboName.column(2)

Remember columns are zero indexed. Also my guess some of those are not numbers but strings and would need to add single quotes around them
tblAssemblies.AssemblyMaterialNumber = '" & me.cmboName.colum(0) & "' AND

"

 

[moke123]

I have a combobox with 3 columns (Bound Column 1) from tblAssemblies,

PK set as AssemblyNumberSpec

Just checking that you are aware that a combobox is zero based while the bound column is 1 based, so if your bound column is 1 then the data used from your combo is column 0. PK set as AssemblyNumberSpec bound column would be 2.

 

[CJ_London]

I already have PK set as AssemblyNumberSpec

in that case this can't be true

I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3

But you have an autonumber field, use that as your PK

 

[MajP]

I assume this relates to

Creating multiple records from one form

Hi All, I have two tables in my database [tbltools] which has 10 fields (tools1 to tools10) and [tbltoolgroups] which has 7 fields (toolgroups1 to toolgroups7) I am in the process of creating a form that the end user can use to define a tool group (will be stored in [tbltoolgroups]) The basic...

www.access-programmers.co.uk

So I would expect a junction table as I described. Your junction table would have a PK to uniquely define the group
(assembly, line)
This does not look like you used a junction table.

 

[Atthe]

I assume this relates to

Creating multiple records from one form

Hi All, I have two tables in my database [tbltools] which has 10 fields (tools1 to tools10) and [tbltoolgroups] which has 7 fields (toolgroups1 to toolgroups7) I am in the process of creating a form that the end user can use to define a tool group (will be stored in [tbltoolgroups]) The basic...

www.access-programmers.co.uk

So I would expect a junction table as I described. Your junction table would have a PK to uniquely define the group
(assembly, line)
This does not look like you used a junction table.

Yes it does, I have just checked and I have used a junction table as you advised although I named it tblToolsForAssemblies with the PK on AssemblyToolGroupNumber however I didn't use it for the combobox.

As the PK is different for every tool within the assembly how would I avoid duplicates in the combobox (I only want to see every assembly along with the corresponding Line and Material Number)?

Please see attached photo

Thanks

 

[MajP]

Maybe there is a 2nd junction table.

I think an assembly has some unique properties but describes a generic assembly
Material number and spec

An assembly can be put on to many lines if I understand correctly.

tblAssemblies, the issue I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3

A generic assembly on a specific line is unique

That would be like I have a list of different generic circuit boards (manufacturer, type, power ...) with generic information about that kind of circuit board.
If have another table where I assign it to an assembly and provide the specific SN then in the other table it is no longer a generic circuit board, but represents one and only one specific.

So you would then have a junction table tblAssemblyLine. It would have an assembly and line ID from the assembly table and the Line table. And this would have a PK.
So assembly 123 on line 1 is different from assembly 123 on line 2.

Then if tools are associated to a specific Assembly on a specific line the next junction table would have a part ID and the fK from tblAssemblyLine in order to assign parts to a specific assembly on a specific line.

Now if there really is no related line information besides just the line number then you do not need another table for a Line. If the only repeated information in the assembly table is the Material number and Spec number then you could skip the junction table.

But that means that in tblAssembly an assembly is uniquely described by it Material number as well as the line number.

AssemblyNumberSpec and one autonumber field in the table

You can create a unique composite key made up of two fields, but this is a PITA.
It would be AssemblyNumberSpec and LineNumber which uniquely describe a specific assembly.
The only PK you need is the autonumber. Call that field Assembly_ID.
Now you can do a unique index to ensure you do not get duplicates. You cannot have the same assemblynumber and line number so the combination must be unique.
Now in your combo you include Assembly_ID and in your report as well. You can hide it on both but when you pick a value from the combl
Assembly123 on Line 5 will have a Unique Assembly_ID. You should open the report to that Assembly_ID.

NO PK can be repeated so AssemblyNumberSpec by itself cannot be a PK. Having AssemblyNumberSpec and an Autonumber as a PK makes no sense, because only a unique autonumber is needed.

 

[Atthe]

Maybe there is a 2nd junction table.

I think an assembly has some unique properties but describes a generic assembly
Material number and spec

An assembly can be put on to many lines if I understand correctly.

A generic assembly on a specific line is unique

That would be like I have a list of different generic circuit boards (manufacturer, type, power ...) with generic information about that kind of circuit board.
If have another table where I assign it to an assembly and provide the specific SN then in the other table it is no longer a generic circuit board, but represents one and only one specific.

So you would then have a junction table tblAssemblyLine. It would have an assembly and line ID from the assembly table and the Line table. And this would have a PK.
So assembly 123 on line 1 is different from assembly 123 on line 2.

Then if tools are associated to a specific Assembly on a specific line the next junction table would have a part ID and the fK from tblAssemblyLine in order to assign parts to a specific assembly on a specific line.

Now if there really is no related line information besides just the line number then you do not need another table for a Line. If the only repeated information in the assembly table is the Material number and Spec number then you could skip the junction table.

But that means that in tblAssembly an assembly is uniquely described by it Material number as well as the line number.


You can create a unique composite key made up of two fields, but this is a PITA.
It would be AssemblyNumberSpec and LineNumber which uniquely describe a specific assembly.
The only PK you need is the autonumber. Call that field Assembly_ID.
Now you can do a unique index to ensure you do not get duplicates. You cannot have the same assemblynumber and line number so the combination must be unique.
Now in your combo you include Assembly_ID and in your report as well. You can hide it on both but when you pick a value from the combl
Assembly123 on Line 5 will have a Unique Assembly_ID. You should open the report to that Assembly_ID.

NO PK can be repeated so AssemblyNumberSpec by itself cannot be a PK. Having AssemblyNumberSpec and an Autonumber as a PK makes no sense, because only a unique autonumber is needed.

So I am looking at my relationship window and It seems like I haven't followed the correct approach.

what fields and PK should I have in tblToolsForAssemblies?

Thanks

 

[oleronesoftwares]

So I am looking at my relationship window and It seems like I haven't followed the correct approach.

Can you share the image?

what fields and PK should I have in tblToolsForAssemblies?

It depends on the tables that tblToolsForAssemblies will relate with and the type of relationship.

Basically a Pkey can have
1. One to many relationships with another Pkey(AKA Foreign key)
2. One to One relationship with another Pkey(AKA Foreign key)

In rare instances there can be a many to many relationships(but don't bother yourself with this)

 

[Atthe]

Can you share the image?


It depends on the tables that tblToolsForAssemblies will relate with and the type of relationship.

Basically a Pkey can have
1. One to many relationships with another Pkey(AKA Foreign key)
2. One to One relationship with another Pkey(AKA Foreign key)

In rare instances there can be a many to many relationships(but don't bother yourself with this)

 

[oleronesoftwares]

the issue I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3. The user selects an entry from the drop down, but if they select an entry of which the first two fields are the same then when the user runs the report the query pulls data for both entries rather than the unique entry.

Your relationships seems ok , in your select query which fields are allowed to have duplicate values and which is not allowed to?

 

[MajP]

Going back to the original problem

SELECT tblAssemblies.AssemblyMaterialNumber, tblAssemblies.AssemblyNumberSpec, tblAssemblies.Line FROM tblAssemblies ORDER BY tblAssemblies.AssemblyNumberSpec, tblAssemblies.AssemblyMaterialNumber;

Your PK is assemblynumberSpec. You need to bind the combo to the 2nd column. Then you open the report to
"assemblynumberspec = '" & me.cmboboxName & "'"
or if real number no single quotes.
I assume you are opening the report to assemblyMaterialNumber which is not unique.

 

[Atthe]

Going back to the original problem

SELECT tblAssemblies.AssemblyMaterialNumber, tblAssemblies.AssemblyNumberSpec, tblAssemblies.Line FROM tblAssemblies ORDER BY tblAssemblies.AssemblyNumberSpec, tblAssemblies.AssemblyMaterialNumber;

Your PK is assemblynumberSpec. You need to bind the combo to the 2nd column. Then you open the report to
"assemblynumberspec = '" & me.cmboboxName & "'"
or if real number no single quotes.
I assume you are opening the report to assemblyMaterialNumber which is not unique.

Yes thanks a lot for your time all solved now