Need help querying into text field

[tonidavid5]

Hi!

I’m currently building an Access 2K database to store components and one of several fields is the [MSN] or assembly serial (there can be as many as 9999 different assemblies). Each single component can be included in one, various and/or a range of assemblies (see sample table attached below).

As I didn’t want to create 9999 different fields to cover each possible assembly, I decided to store them in a [MSN] text field as a string with a similar format to that used when choosing pages to print from a range in MS Word (using colons and dashes; i.e. 1,3,10-40).

I want to build a query where the client selects the MSN serial for which he wants its full list of components.

Using VBA to build three custom functions, I’m able to extract the ‘tokens’ between colons and dashes, and tag the ones to the left and right of a dash as ‘range start’ and ‘range end’. But I’m afraid I’m stuck as to how to build the query to get all records whose [MSN] field include the desired assembly (i.e. a record whose [MSN]=”1,3,10-40” should be listed when querying for assembly “20”). I don’t find a way to compare a query parameter (i.e. “20”) against pieces of the [MSN] field rather than the whole field.

Please, any ideas as to how to design this query? There might even be other (and better) ways to design the [MSN] field so that queries are easier to build.

All help is welcome.

Regards,

Sample table:
COMPONENT MSN Assy
A 0-9999
B 1
C 2,5-10
D 10,13-20,70
E 20-9999

 

[ecniv]

Sorry to post this as you probably won't like it... well at least initially.

tblComponents
ComponentID - auto - pk
Compotent details (how ever many fields)

tblAssemblies
AssID - auto - pk
AssCode - text - 20
AssDescription - memo
AssActive - Yes/No - default yes 

tblAssemblyComponents
AssCompID - auto - pk
AssID - number - long
ComponentID - number - long

The Component table holds all the data about each component (Assuming singular)
The Assemblies table holds the code and name of the assembly
The Assembly components table holds all the components required for a particular assembly.

The AssemblyActive is to let you know whether the assembly is still used... New assemblies could be similiar yet different and replace the old ones, but the old ones may need be kept for reference purposes...

The problem being that 9999 or 10k records need to be added for assembly types... plus all the components. But this table layout should allow you to query what you want efficiently. Also allows for more than 10k assemblies

Just a suggestion for a way to complete the task.


Vince

 

[smercer]

I am thinking you may need a Many to many relation ship

one to store the components, one to store the assembly names, and the table that joins both tables should be the one that has the components to make the assembly.

Each component would get a new record, except if you had the same component more then once, which you would need to use a quantity field.

Am I on the right track here?

 

[tonidavid5]

The thing is I'm try to relate components to assy serial numbers (might be one, might be a certain range, might be a mix of the previous two).

I don't think a many-to-many relationship is necessary here, for I'm not trying to quantify the number of assys a same component goes to.

The one-to-many between tblComponents and tblAssys looks fine to me ... with the problem being the 10k number of records.

What I keep on seeing as troublesome is how to present the list of assy choices on a the form the client uses to enter the component information, 10k lines are far too many for a multiselect listbox. The tblAssyComponents may have as many as 10k cells for each component record (or line).

I'll keep on thinking about it. Let me know if you have mre suggestions, and thanks again chaps!

 

[smercer]

I think it is possible to do this with a bit of coding. You may need a few dlookups and "&" to complete this.

Supply us with a sample database and We'll be able to help you more.