string value instead of a query (1 Viewer)

[roosn]

1st, i have tried to search, but i am not sure how to title the search

i know what i want to do, but dont know the full correct access terminology

hence please bear with me

i have a table of 120,000 records. each record is applicable to at least 1 part, therefore i placed a FK in this table and created another related table i will have in access of 140,000 records which has a PK

this allows me to query the records table by the part/s it is applicable to

is it possible, rather than have that additional related 140,000 record table, i could add a text field to the record table with the values in a string format, such as 2, 11, 14, 16 etc, where 2, 11, 14, 16 etc are the ID's of the parts

i could then query the part value with an 'IN' statement from the text field

this will have benefits on the size of the db, because i lose a table of 140,000+ plus records, but is it acceptable practice and will it effect performance

thanks in advance

 

[arnelgp]

yes you can, you can then join them using single table.

 

[Galaxiom]

is it possible, rather than have that additional related 140,000 record table, i could add a text field to the record table with the values in a string format, such as 2, 11, 14, 16 etc, where 2, 11, 14, 16 etc are the ID's of the parts

It could be done but it would be slower. A foreign key can be indexed making queries very fast. Using the string will require every record to be processed when searching for specific values.

I would strongly advise you not to do it.

 

[roosn]

It could be done but it would be slower. A foreign key can be indexed making queries very fast. Using the string will require every record to be processed when searching for specific values.

I would strongly advise you not to do it.

Thank you for the advice, its been taken

 

[arnelgp]

you can always index on the new field, and why not use long integer since you are saving numbers in the field anyway?

 

[Galaxiom]

you can always index on the new field, and why not use long integer since you are saving numbers in the field anyway?

I think you have misunderstood the original question. They were asking about using comma separated values recorded as a string in one field as an alternative to a junction table.