Multiple field unique information db?

[smile]

Hi, I'm crating this db:

Two tables, in one there are Items and services, in the other purchases.

1st table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text

2ndt table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text
Client - Text
Puchase Date - Date

How can I enter data to my 2nd table using unique fields (barcode, Product name actually ID in 1st table?) Problem Is Access alows only 1 unique key field in table and that is undertandable. Is there any way out in my situation.

barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically.

Please Attach your DB if you have time to look into this issue.

 

[smile]

Hi, I'm crating this db:

Two tables, in one there are Items and services, in the other purchases.

1st table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text

2ndt table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text
Client - Text
Puchase Date - Date

How can I enter data to my 2nd table using unique fields (barcode, Product name actually ID in 1st table?) Problem Is Access alows only 1 unique key field in table and that is undertandable. Is there any way out in my situation.

barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically.

Please Attach your DB if you have time to look into this issue.

I have found out how tomake multiple unique field by selecting them with Ctrl presed down, but still howto: "barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically."

 

[The_Doc_Man]

First, do not confuse "unique" with "primary" - you can have more than one unique key field.

Second, read about "NORMALIZATION" via Access Help and Google Search. For the latter, stick to college-based articles.

Your design is very much not good because it is not normalized. You are wasting space and your time maintaining multiple fields that you don't have to maintain.

Using a unique BARCODE situation is one of the cases where, if you wish, you can do away with the AUTONUMBER field in the thing that equates a barcode to text.

Now, for back-filling the descriptive text, if you normalize everything, you won't have to do this. To build an invoice/purchase list, you can use a JOIN query (on BARCODE) to get any descriptive data for your reports and for display on your forms. The Form wizard for Combo Boxes will help you.

By the way, read up on COMBO BOXes in access help for another view on how to pick up that text field. But you STILL don't want to store it in the custmore purchase tables.

 

[smile]

First, do not confuse "unique" with "primary" - you can have more than one unique key field.

Second, read about "NORMALIZATION" via Access Help and Google Search. For the latter, stick to college-based articles.

Your design is very much not good because it is not normalized. You are wasting space and your time maintaining multiple fields that you don't have to maintain.

Using a unique BARCODE situation is one of the cases where, if you wish, you can do away with the AUTONUMBER field in the thing that equates a barcode to text.

Now, for back-filling the descriptive text, if you normalize everything, you won't have to do this. To build an invoice/purchase list, you can use a JOIN query (on BARCODE) to get any descriptive data for your reports and for display on your forms. The Form wizard for Combo Boxes will help you.

By the way, read up on COMBO BOXes in access help for another view on how to pick up that text field. But you STILL don't want to store it in the custmore purchase tables.

I read this article here:
http://72.14.221.104/search?q=cache...ID=88+NORMALIZATION+access&hl=en&ct=clnk&cd=4

But my database doe not contain repeated rows or colums, I do not understand what dis you mean?

My problem is I wan't to enter items in "sell" table using simple method by selecting items with dropdown box and then when possible barcode scanner (as barcode scanner must be purchased to use it). This has nothing to do with normalization. Sure I can remove the ID from "warehause" table and set "barcode" to be primary key, but what is barcodes will not be used at all? The database must still work.

 

[The_Doc_Man]

1st table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text

2ndt table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text
Client - Text
Puchase Date - Date

OK, looking at it another way, perhaps I was hasty. Still, if the barcode is truly unique, do away with the autonumber in the translation table (Your table 1) and use the barcode in table 2 as a foreign key to table 1.

Once you see this as a JOIN-able relationship, you won't worry about lookups. The other thing to know is that you are using tables where a JOIN query between table 1 and table 2 over BARCODE would give you everything you wanted in a single query, period.

If you are serious that some things don't have barcodes, you have a problem with your underlying business model and the barcode stuff is a smokescreen. It is your model but it is confusing the issue. Rethink your business rules when you rethink your table.

Another way of saying this is that if you design your business well, it would be trivial to implement an Access database because all of your answers would be where you needed them already.

And that leads me to the old programmer's rule: If you can't do it on paper you will never get Access to do it any better. Faster but not better.

Yeah, I know that sounds critical, but I'm not trying to be mean. I'm trying to concisely say that I smell a problem in a mismatch between data flows in reality vs Access and that usually devolves to a business model not being fully understood on one side or the other of the reality/Access interface.

 

[smile]

OK, looking at it another way, perhaps I was hasty. Still, if the barcode is truly unique, do away with the autonumber in the translation table (Your table 1) and use the barcode in table 2 as a foreign key to table 1.

Once you see this as a JOIN-able relationship, you won't worry about lookups. The other thing to know is that you are using tables where a JOIN query between table 1 and table 2 over BARCODE would give you everything you wanted in a single query, period.

If you are serious that some things don't have barcodes, you have a problem with your underlying business model and the barcode stuff is a smokescreen. It is your model but it is confusing the issue. Rethink your business rules when you rethink your table.

Another way of saying this is that if you design your business well, it would be trivial to implement an Access database because all of your answers would be where you needed them already.

And that leads me to the old programmer's rule: If you can't do it on paper you will never get Access to do it any better. Faster but not better.

Yeah, I know that sounds critical, but I'm not trying to be mean. I'm trying to concisely say that I smell a problem in a mismatch between data flows in reality vs Access and that usually devolves to a business model not being fully understood on one side or the other of the reality/Access interface.

I'm making this db for a friend's bussiness.
Thanks for your reply. My idea was that if and item is sold it contains a barcode like verthing does these days, but if a service is sold it does not. Therefore you would need to scan a barcode to enter some service. At least that how I've seen it implemented in supermarkets and stores.

Yes my idea was that an db operator would just select service name via form dropdown box filter and barcode field could still contain a barcode but it is faster to filter down to "Services" -> "PC diagnostics" in a long list than on paper and then scan the barcode to enter that service.

Or perhaps I could implement this barcode paper thing on monitor but then I need to generate codes store them like pictures, and every monitors resolution is different - I think barcode scanner would not scan barcodes on monitor anyway.

Have you any ideas on my situation, I actually implemented the barcode only version of my DB and it works fine, however I still kept Primary key field just because barcodes come in al sorts of styles like (internal, code 128 etc.) and to make access autogenerate them like with autonumber is tricky. I made barcode unique field - period. and connected warehause ID to Sales barcode fields.

 

[The_Doc_Man]

As to services, if they are predefined, do what our local supermarket does. When someone wants a particular service that is not localized to a single product, give them a bar-coded receipt that identifies the service and the rate charged for same, then have the cashier enter the dollar value as either an override - or as a computation based on, say, 12 minutes at 30 cents per minute or something like that.