I have one table (tblOrders). The records in the table are primarily organized and sorted by a field called txtOrderID. There can be multiple records for an OrderID. Under OrderID, there is another sorting (grouping) level called txtProductID. There can be multiple ProductID's for each OrderID. There is also a third field in the table called txtProductIDCt which is used for counting purposes in other queries, etc... I am only inputting data and using data from the table directly...no forms or reports are being used.

Example:

txtOrderID = 11 (all the records below are associated with this ID)
txtProductID(1) = apple
txtProductID(2) = apple
txtProductID(3) = orange
txtProductID(4) = orange
txtProductID(5) = orange
txtProductID(6) = banana

1. First, I need to be able to determine how many txtProductID(s) I have per txtOrderID.
2. In the example above, for txtProductID = apple, I would want the txtProductIDCt field to be filled with a "1" for just one of the "apple" records and a "0" for all of the others. The placement of the "1" can be on any of the apple field records...it doesn't matter. As indicated previously, this field is used for counting at a later date (other queries, etc...). So, in summary, for txtOrderID 11, I would have one apple record where the count field is marked "1" and one apple record where the count field would be marked "0". In the case of txtOrderID 11 = Orange, I would have one orange record where the count field would be marked "1" and two orange records where the count field would be marked "0". In case of txtOrderID 11 = banana, I would only have one record where the count field would be marked "1" (since, I only have 1 banana record).

I realize that I could do a count query easily to determine the number of apple, orange and banana records individually, but the problem is that I need the actual count field flagged permanantly in my master table. If there is a way to do this "automatically" (via programming), obviously I would prefer that over having to go thru the table one-by-one to enter the flags individually. The table is rather large...it has about 600,000 records. If anyone has any suggestions for how I could do this, I would appreciate the help. Thanks in advance for your assistance.


Melissa

Hi

First, I think most would agree that's definitely not the way to go. Tables are the objects that store your data and shouldn't be available to add/delete/amend data directly - that's what forms are for. Forms are like a 'window' to your data and allow full control over additions/deletions/amendments and reports are for viewing/printing (generally) results of queries from your data

Second, and more importantly you have a structure problem. What you have at the moment is what's known as a flat file, and whilst Access can handle flat files very well, you'll run into alot of problems using this approach. The whole point of creating/using a DBMS is to create a set of related data where you only need to store a single piece of data once and reference it whenever/wherever you need to. I'd suggest reading the following articles (in no particular order of importance) to have a rethink on what you're doing...............

Good Luck on your project

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics

Hi Melikka,

To add some to what "Access Addict" has stated, your structure appears to be "committing spreadsheet" with Access! Take a look at the database design documents here:

http://home.bendbroadband.com/conradsystem...tabaseDesign101

A properly normalized database will allow you to count records in a variety of ways, usually with very efficient queries. An improper design can result in a very slow application, that is difficult to maintain.