Comma separated numbers in one form field

[mrnorman]

Hello,

I've current got two tables, one with Products and the other with Suppliers. The products table has a column in which I may indicate Supplier IDs that carry the product. I'd like to make a report that groups the Products by the Supplier that carries them and I'm having some trouble. The way I see this working is to relate the Supplier ID found in the Product table with the auto generated Supplier ID in the supplier table.

My trouble is this, when the field is set to text I'm able to enter values separated by commas no problem but I can't report on them because I'd be relating a number to a string. How may I overcome this?

Help is greatly appreciated

 

[missinglinq]

...My trouble is this, when the field is set to text I'm able to enter values separated by commas no problem but I can't report on them because I'd be relating a number to a string...

Putting aside the inappropriateness of entering multiple pieces of data into a single Field (and then, presumably, having to parse it back out to individual components) the way to get the Access Gnomes to treat Text as Numbers (assuming that it 'looks' like a Number) is to use the Val() Function:

Where FauxNumberControl is defined as Text:

If Val(Me.FauxNumberControl) > 100 Then

Linq ;0)>

 

[mrnorman]

While I certainly appreciate the response, I feel as though it's beyond my understanding. Perhaps a more appropriate question would be:

What is the proper way to set this up? If I have a need to display a report which groups Products by Vendor and some products MUST be sold by multiple vendors. How would I build this?

Thanks!

 

[jdraw]

I may not be following your description, but as I understand, I think you need another table.

You have

Supplier
SupplierId PK
SupplierName
SupplierAddress
SupplierCity
SupplierState
.. other SupplierSpecific info

And you have
Product
ProductId PK
ProductName
..other Product specific info

The missing table as I understand the set up is

SuppliesProduct
SuppliesProductID PK
SupplierId FK (to Supplier table) long integer
ProductID FK (to Product table) long integer
..other info specific to this Supplier and this Product

I would make the SupplierId and ProductID in this third table, a unique compound index. That will prevent duplicates.

For data entry on a form, you could have a
combobox of Suppliers, and a list box of Products.

Select a Supplier, then in the list box, Select all (each of ) the Products held by that Supplier.

You would need code to pick up each of the ProductIDs from the list box, and the Supplier from the combo.

Loop though each of the selected listbox items and Insert a record into the third table.
The autonumber PK will be handled automatically by Access. You need to supply the SupplierID and the ProductID in your insert statement.

Good luck.

OOOops: I see you and linq have been interacting while I was typing.

 

[mrnorman]

Here's the current setup:

Supplier:
ID
Vendor Name
Contact Name
Phone
Email

Product:
Brand
Description
Vendor ID


I need to adhere to that structure, but generate the previously mentioned report. If I'm not mistaken, in the combo box scenario I'd still have multiple values in a single field. I'm really just trying to make sense of all this.

Thank you guys very much for the responses, it means a lot to me

 

[jdraw]

How do you uniquely identify a Product?

I'd still have multiple values in a single field.

No. You would have a separate record for each Product * VendorId.

Perhaps you should research Normalization.

see http://www.phlonx.com/resources/nf3/

As an aside, why do you have a table called Supplier and yet name the field Vendor??
I would recommend you DO NOT USE a naming convention with spaces (or special characters) in field or object names.

 

[mrnorman]

Unfortunately, I didn't set the requirements for the DB. I'm simply following instructions as part of an exercise. The more I think about it, the more I think the intention was for me to create separate (but identical) records for products so that they may have unique Supplier IDs for me to relate.

I agree with your comments regarding the naming convention but I didn't set the rules. I'm really just trying to make the best of a weak situation.

 

[jdraw]

Can you show us the instructions? Is this a homework exercise?