Design question- re duplicate keys not allowed

[oasuser]

I am a pretty new Access programmer, but have prior programming experience. One thing I observe is that I still think in terms of COBOL; FORTRAN etc, vs queries/updates etc.

I am designing an inventory DB system, but it is not for retail users perse. It is more of an item tracking system- recording the location of moreso than the sale of product.

Situation: 6 sales reps have samples of merchandise. I need to track whether that rep has transferred any of his inventory to another rep/ a dealer/ or home office.

Complications: It is possible for one rep to have more than one of the same item. (duplicates).

My design so far: 6 Master DBs- one for each rep. Key would be UPC, yet because of duplicates being allowed, I would need to add a sequence # (or some unique identifier).

DB master would have these fields: UPC/SEQ/ONHAND

The transaction files which dictate status (received/carryover/transfered/sold) would turn a switch on/off on the rep master DB.

TX files would have these fields: UPC/SEQ/STATUS/ID

PROCESSING: UPDATE query for TX to flip MSTR onhand switch off, but for adding new records(inventory) I need to increase the seq # if the UPC already exists.

REPORTING: would come from TX files as well as MSTR - probably thru a join/query process

PROBLEM: 1) I question my design
2) What is the easiest way to add records to the master
3) Is access really capable of what I want to do?

Appreciate any help. Hope this is the right place to ask, and appropriate to ask.

 

[hermanlaksko]

1. The design.
I would ha ve 4 tbls to handle this
tbl 1. Reps
tbl 2. Reps/goods
tbl 3. Goods
tbl 4. Locations(stocnames)
The tbls 1.3.4. are obvious, tbl 2 would be holding the goods "assignd" to the rep and could hold as many goods as you like, and as many of each as you like.

2. If the record exists
Update RepsGoods set NoOfGoods = NoOfGoods+" & YrNumber else
YrRe.Addnew
YrRe!ItemId = YrItemNo
YrRe!Rep= YrRep
YrRe!ItemDescription=Yr description
YrRe!OnOfGoods=NrOfGoods
YrRe.update

Something like that.

3. Yes defenately



Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[oasuser]

Herman-

I posted a reply this morning in which, apparently, I did not hit the final 'post' button.

I have been pondering your recommendations and may be implementing them into my design. Still questioning some of the logistics.

Also, being this is my first ACCESS program/DB, having to learn (ACCESS) macros feels intimidating, but seems a must. I've worked with Excel Macros, but am quite a novice at it.

I think I need the REP/PROD table so I can flag which items are active and not. It also gets me around the duplicate keys issue by using auto gen id or item number for a key.

My concerns lie in updating which of the duplicate items. and the most efficient means of doing so.

At any given point in time I'd like to be print a report such as this:

UPC DESCRIPTION REP1 REP2 REP3 REP4 REP5 REP6
123 M's Jacket XX X X X
234 M's Glove X X X X

where x's represent items on hand. xx meaning more than 2 etc.

this is the complexity to this situation. the reporting of transactions and a final snap shot at an end point dont feel as hard to design processing for.

maybe I'm just being intimidated by my lack of experience. Seems a query can bring the 6 DB's together. And your code will help with the duplicates (i think). Somehow this doesn't feel quite resolved. When i can articulate my concern. I shall.

I appreciate your time and design. thank you.

 

[hermanlaksko]

1. Do not feel "a lack of brains" when using access thats why you and the rest of the gang here at Tek-Tips are here. Also we all run into probs that we simply can not figure out and cry HELP :-D

2. When refering to macros I expect that you meen VBA and not the actual macros in access, as theise, in my opinion anyway, are only there to automate the start of the access front-end and automation the use of F-keys.

3. The mentioned report that you want could be accomplished by using a crosstab query. You could play with the build-in wizard to obtain experience and a working result before creating a crosstab manually.

4. ALLWAYS when creating a new table create a primary key field and make this key an autonumber. This autoID can then be used for creating relations etc. never use this AutoID as anything else.
I.e. an item table could hold theise fields:
ID = AutoID and primary key
ItemNo = the itemnumber (could be a text field for easy creation of items - users like to use descriptive itemnumbers)
ItemName = Description of the item
etc. various informaition such as location (new table and relation), Type (new table), .... I could go on and on ;-)

If you use this simple method you shopuld never run into dub-problems

Herman

They say that crime doesn't pay... does that mean my job is a crime?