Block Duplicate records in Table (1 Viewer)

I am building an inventory and order system that will need to pull product information from a list of 200+ different products/product types.

I have built a master product list which details the different products (Flowers).
In the Master Product Table I have five fields.
"Species", "Variety", "Size", "Supplier", and "Base Price"

Naturally there is a lot of overlap between the fields. i.e. there are 13 types of Begonias all of which are a different combination of Size and Variety.

So the table could look something like:

Species Variety Size Supplier Base Price

Begonia Fancy leaf 4" Ball $20.00
Begonia Dragon Pink 4" Ball $20.00
Begonia Fancy Leaf 6.5" Ball $25.00
Coleus French Quarter 4" Proven Winners $20.00

_____

Is there a way to ensure that duplicate entries are not entered in this situation?

I want to ensure that 4" Fancy Leaf Begonias can only be entered once.

Thanks in advance for your advice.

Cheers,

John

I realized that the text representation is uneasy on the eyes so here's a screenshot of the table.

Thanks again.

Hi. Not sure you have normalized the table structure well enough; but to prevent duplicates on your current structure, you could create a multi-field unique index on all four fields. To create one, you open the Indexes window by clicking the Ribbon button and create a new index by entering a name on the first column and then select all the fields, one at a time, on the second column. You then highlight the index name again and set the Unique Values to Yes. Hope it helps...

Can try compound index setting.

You can either create a unique index (in the table design view) or unique primary key that involves several fields. In my version "indexes" is on the design tab. For some reason, many advocate the former but not the latter. If I was ever told why, I can't recall.

At Table design, Open Indexes (from top menu). Enter a name for the index, then next column across, select the field. Next line down, skip the name part, select the next field and so on. Go back to the first field in the index, where the index name appears, you will see information below, click on Unique.

I would also have another field, FlowerID, Autonumber, as the primary key.

Hope this helps.

Edit: Damn, have to be quick, when I started typing my response, no other replies were there. Must learn to type faster.

From your own sample, you may be setting yourself up for a problem.

What happens when you have a run on 4" Fancy Leaf Begonias and need to source from more than one supplier?

Mark_ said:

From your own sample, you may be setting yourself up for a problem.

What happens when you have a run on 4" Fancy Leaf Begonias and need to source from more than one supplier?

Click to expand...

The supplier is "multiple" as shown? It's a field in the index? Have to agree though, the tables aren't normalized. Price and supplier don't belong in the table shown.

Mark_ said:

From your own sample, you may be setting yourself up for a problem.

What happens when you have a run on 4" Fancy Leaf Begonias and need to source from more than one supplier?

Click to expand...

Hmm. I'm not sure I see how that could cause a problem. 4" Fancy Leaf Begonia supplied by Ball would be a unique record from 4" Fancy Leaf Begonia supplied by Proven Winners.

Although you have me thinking and it may not be necessary to keep have supplier as a field in the product list.

Micron said:

The supplier is "multiple" as shown? It's a field in the index? Have to agree though, the tables aren't normalized. Price and supplier don't belong in the table shown.

Click to expand...

Correct, if something is sourced from more than once supplier I have it indexed under "Multiple"
I definitely recognize this isn't best practice though.

As for price though, how would you suggest I store that data? For the most part products of the same size are the same price. This isn't always true though...

essaytee said:

At Table design, Open Indexes (from top menu). Enter a name for the index, then next column across, select the field. Next line down, skip the name part, select the next field and so on. Go back to the first field in the index, where the index name appears, you will see information below, click on Unique.

I would also have another field, FlowerID, Autonumber, as the primary key.

Hope this helps.

Edit: Damn, have to be quick, when I started typing my response, no other replies were there. Must learn to type faster.

Click to expand...

Added Flower ID as a primary key. Followed your steps and it worked! Thank you!

I will surely be posting many more questions. As I'm sure you can all tell I a have very little experience and I'm basically building this from the ground up.

jw195 said:

Added Flower ID as a primary key. Followed your steps and it worked! Thank you!

I will surely be posting many more questions. As I'm sure you can all tell I a have very little experience and I'm basically building this from the ground up.

Click to expand...

Thanks. I think by now you realise we addressed and fixed your immediate problem but I concur with the others as regards normalisation. The tables really need to be split up. I haven't got a link at the moment though I know one of the regular contributors here has a link in his signature regarding normalisation. It's a dry topic but if you can come to grips with it, it will make life easier.

EDIT: Found the link, courtesy of @Minty's signature:

Correct, if something is sourced from more than once supplier I have it indexed under "Multiple". I definitely recognize this isn't best practice though.

Click to expand...

For sure - it's not really useful is it? Don't just focus on whatever is being said that suits your mindset. You can continue in this fashion OR you could learn normalization and avoid issues like this one as well as others that WILL crop up as a result of not doing so. IMHO, you need to learn the Entity/Attribute relationship concept and apply it to your business. In your case, is Product the only such entity with attributes of Species, Variety and Size to which you link to suppliers? Or are Species, Variety and Size their own Entities? What I'm sure about is that Suppliers is not an attribute of product, which is why it definitely does not belong there. There is no certain answer to your pricing question without knowing what the product entity looks like. One possibility is that Product is Species|Variety|Size, to which you have a junction table between Suppliers (an entity of its own) so that the junction allows for many to many (many suppliers providing many products). This subject is a whole book chapter, so I'm gonna stop there. Here's the links I usually provide in situations like this, including naming conventions, because I think you could benefit from them

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/2008/12/what-is-normalization-part-i.html and/or http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-in.html

Important for success: One source about how to name things - http://access.mvps.org/access/general/gen0012.htm

What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers - http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

About calculated table fields - http://allenbrowne.com/casu-14.html

As you are dealing with flowers, I am guessing the following are also applicable?
Price varies based on season (in season flowers being less expensive than other times)
Supplier for a given flower varies based on season.
Spoilage affects pricing.

This would normally mean you would have a set of prices for a given product, depending on when it is purchased as locally sourced will generally be less expensive for you and stay fresh longer.

For myself, I'd keep prices in a separate table that links a product with a supplier AND has a date that the price is valid until. Depending on how tightly you control inventory this may also work into an inventory management piece.