Help with duplicate fields please!

[Lol Owen]

Hi, doing my project at college, decided to do a EPOS simulator for my sisters business based around barcodes etc and tied to a database. i've just knocked up something after normalising my data to prototype it to see if it is viable. I'm having problems with duplicate field entries. My db consists of essentially 4 tables at this point:

User : user ID, User Details

Product: Product ID, Barcode, Description, Cost Price, Sell Price

Transaction: Transaction Number, Date, User ID, Transaction Total

Transaction/Product - a compound key which is to manage the many to many that exists between Product and Transaction tables.

I am wanting to allow multiple entries for the same item, i.e. multiple scans, not a single sacn followed by a quantity. I keep getting an error message though that syas I am duplicating fields. I have tried altering the Index property for the fields in the Product/Transaction table but htis has had little effect.

Any thoughts please as to how to do this? I have attached the db for examination.

many thanks, Lol

 

[Mile-O]

Add a quantity field to your join table.

I'd recommend you don't even make any form yet until you get your tables sorted out. While the design is sound enough there are a few things you should think about.

Table Name
You have given them generic titles. Transaction Product etc. What happens when you get queries and you name them Transaction and Product. If you later refer to them Access doesn't know which one you are referring to. That's why it is best to use a prefix. ie. tblTransactions, tblProducts

Special Characters And Spaces
It's a better practice not to use special characters ($£*?/@ etc) in the naming of anything. In your junction table: tblProductsToTransactions would be better. The same goes for spaces in field/table/object names. Rather than User ID, use UserID. This means you can eliminate the need to remember Access' other rules where, for example, you have to surround names with square brackets. You need these with special characters too.

Reserved Words
Don't use reserved words to name anything in Access. You have a field called Date; this is reserved as it is the name of a function. Reserved words are typicall object names, functions, and methods used within Access.

Binding Tables to Forms
Always use a query to bind to a form. Tables can contain a lot of records and, in this instance, you'll get a huge number of transactions. When a table is bound to a form every record is loaded in which, as time goes by, will slow the form down. Use a query to select only the appropriate record(s) to load into the form as this is faster and best practice.

Make your queries from your tables before creating your forms.

 

[Lol Owen]

Thanks fore the advice. I apprecciate the need for proper naming etc on the tables and fields. This is far from the finished product, it was just at this point a feasibility study, and to see if my normalising skills are adequate!

Cheers, Lol

 

[Mile-O]

I am wanting to allow multiple entries for the same item, i.e. multiple scans, not a single sacn followed by a quantity. I keep getting an error message though that syas I am duplicating fields.

As you have set the two fields as a composite primary key you will never be able to have multiples of the same info. That's why you need a quantity - maybe you could write a little bit of VBA that, if the same thing is entered again, adds one to the quantity.

 

[BarryMK]

Hi Lol

If you're fed up with Access being inconsistent you're implying that it is consistent

 

[Lol Owen]

Access get's me where I don't know what day it is , never mind anything else!

I really like the characteristics of networked Access, every days a new adventure

Cheers, Lol