Table Relationships- tracking item location(s)

[oasuser]

I posted this under the general ACCESS forum, before I caught that this forum for tables and relationships existed. I appreciate any help that can be offered.

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) What is the easiest way to increment the sequence # (my idea is to check for duplicates with an on-hand switch = "y", then increment the sequence #)..


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

 

[willir]

oasuser

Welcome to Tek-Tips.

pretty new Access programmer, but have prior programming experience ... COBOL; FORTRAN etc, vs queries/updates

Although SQL statements may be different, use of the Visual Basic for Access, VBA should be fairly easy for you to catch on to.

When moving to a realtional database, the biggest two issues I have seen people stumble on are Nulls, and Relationships. (For example, a common mistake is to end up with a Cartecian product)

...Moving on
With six sales reps, are you looking at using a Master database and replica databases for each sales rep. The Sales rep update their database and then connect to the head office and update the master database? Or will they dial into a Citrix or Terminal server and update the main database over an Internet connection? Or FAX / send their inventory movement orders by other means and some one else updates the database? The reason I ask is that this will impact the design.

The design will be similar to an Inventory database and will track location. You can track location individually, or by location + location detail. (A building would be a location, a Room would be the localtion detail) With six Sales reps plus Dealers plus Offices, you have some thinking to do. Perhaps track LocationType and Location makes sense.

1) I question my design

Review following on relational design...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer to design and relationships...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

And a really good article on queries
Harnessing the Power of Updatable Queries

Here is something to ponder, as a start...
Note: I had to decide to treat this system as a transaction inventory system or more like an asset management system (eg: computers, printers. etc). I leaned towards more of a transaction system.

tblContact
ContactID - primary key
ContactType - text - SalesRep, Dealer
CompanyCode - foreign key to tblCompany
LastName
FirstName
Address
... plus other properties for a SalesRep

tblPhone
PhoneID - primary key
ID - foreign key to tblContact or tblCompany
PhoneType - text - cell, home, office, fax, email
PhoneEMail - text

tblProduct
ProductCode - primary key
ProductName
Active - boolean (yes/no)
... plus other properties for inventory product

tblCompany
CompanyCode - primary key
CompanyName
Address

tblTrans
TransID - primary key
TransDate - date
ProductCode - foreign key to tblProduct
ProductQty - quantity (numeric - decimal, interger, ??)
ContactID - foreign key to tblContact
Comments - memo

Discussion:
You are probably wondering why I don't have SalesReps, Dealers and Location. Well, it is easier to relate to things using a single reference point. In this case, I could have developed a table or tables for locations and then link locations to Dealers, SalesReps, etc. This approach would work. However, I tried to come up with a simplier approach with the assumption that if you know the Contact person (owner), you know the location.

Next, you may wonder why I do not have a quantity field on the Product table. You can add one, and make sure you update it when ever inventory is added or destroyed. You can calculate total inventory by summing up the transactions.

Why did I use more of a transaction system? Because you do not have an apparent asset management system that would use serial numbers or asset tags to uniquely identify each inventory item. It seems that you are more interested in the quantity in hand.

Note: This transaction system requries two entries for each transaction, a negative transaction to remove inventory from a SalesRep, and a positive transaction to add inventory to the other SalesRep.

Another approach to storing the inventory would be...
tblProductOwner
ProductCode - foreign key to tblProduct
ContactID - foreign key to tblContact
Quantity

Primary key = ProductCode + ContactID

The above is a more simple system. But the advantage of using transactions is that you can review the transaction history. It gives you more detail. The tblProductOwner table only give you a snap shot, but the transaction approach gives you history.

2) What is the easiest way to add records to the master

Regardless of your approach, you should use a centralized update module to perform the updates. Using SQL statement is more effecient, but you can also use ADO or DAO record sets.

In addition to the database schema, you should also look at using a Front End database (forms, reports, queries, static data) and a Back End database (database tables, relationships). The front end is used for data entry where the back end tables are "linked" to the front end. The front end database can be located on the user's desktop or on a server. The back end is almost always located on a server.

BE / FE has several advantages. The two big one's being that it is less likely that the end user will tinker / damage the database design, and you can deploy updates to the forms and reports without worrying about the data.

Replication:
Ideally, by far, the best approach is to use a Citrix or Terminal server. This way, you can have the FE and BE databases located on a server where they will be backed up. And you will not have to worry abour replication.

Alternatively, you can take advantage of the replication feature within Access. Here, replica databases (FE and BE) are distributed to each SalesRep, and the Master is kept on the server. The SalesRep move their inventory / product around, enter the transactions into their replica database, and then update the master database. This system works but requires good management, and you will need to research replication -- not for beginner users. (Hint: backup the master database)

Primary keys:
The primary key uniquely identifies each record in the table. If you have to use replication to update the master database, you will need to think about the primary key for the transaction table. Two approaches are to a) Use the AutoNumber ReplicationID (when defining the table, select DataType Autonumber, and then in the "Field Size" in the Field Properties section, change LongInteger to ReplicaitonID.) The RepliocationID is actually a 16 bit GUID (globally unique identifier). The chances of getting a duplicate ID are astronomical.

Alternately, you can use a two part key -- the workstation number + autonumber (random), or owner initials + autonumber (random). This takes a bit more work, but you can then identify who made the transaction.

If you can use a Citrix / Terminal server solution, this is not as big of an issue.

3) What is the easiest way to increment the sequence

I think I already addressed this problem.

I realize that this approach is a little different than your TX and DB databases, but you may not have been aware of replication.

Richard

 

[oasuser]

Willir -

Thank you for such an in-depth response. After I had posted here, I stumbled thru the forums and found the article by Litwin. It was really good, and answered some questions about design. (Until I get more experience, I know I will continue to question myself on this, however.)

After reading your post, I went on to read some of the other articles- Harnessing the power of queries, in particular. The others I am somewhat familiar with as I am taking classes and am working with the tables and design as I plod forward.

As far as how data is being received, at present, I will receive the transaction files from the reps, and all processing will be done here. Basically, at present my experience doesn't allow me more (I "am" the IT dept, lol). But, yes, having live feeds would be ideal. I just feel there is much more for me to comprehend around access before I get there, this is my first project.

I would love to keep this transaction based processing. There is alot to be accomplished just by reporting the information received back to the field. Unfortunately, it is somewhat critical for us (main office) to know where items are at any given time. And herein continues to lie my problem (and my inexperience).

I keep stumbling over this quantity issue. I think perhaps because I think this is all query processing when in fact I will probably have to use some VBA, and I've no experience in Access at present, tho some in Excel.

If my tables look much like you described:

tblPRODUCT
Upc
Size1
Size2
WSP
other

tblOWNER
Ownerid
Ownername
other

tblTX
Txtype
Ownerid
Txref
UPC
Memo

I guess this is where I am unsure of processing. If I let tblProduct drive the query.. since there are many items(UPC's) per Rep I still need a way to designate if it's on-hand or not. And its a combination of transactions that dictate this. So, it seems I will indeed need a master, by rep, with an on-hand switch. This still boggles me when I look at quantites tho.

tblRepMstr
UPC
qty
on-hand

I am not sure how to explain where I am stuck here. There are 3 types of tx's that would designate an item is on-hand- received; transfered; carried over. There are 4 types that designate it's un-available.

I think I keep seeing some sort of join/query with a ton of tx files (which confuses me) rather than processing one tx file updating the master and being done with it (tx). I think this is what is throwing me off. Keep seeing "programming" vs queries... and cannot tell if I am on the right track.

It feels, as if I need some VBA, versus a straight out query. Can you provide any assistance along these lines?

Processing seems to need to be on the lines of: if Txtype = " " or " " or " " set on-hand = "y". If txtype = " " or " " or " " set on-hand = "n".

If on-hand = "y" and mstrupc = txupc then qty = qty + 1

Can queries handle something like this? With multiple tx files? Seems there is not an easy way to automate this?

Will it be easier for me to process all transactions into one huge master with Rep as part of key and then extract a file by rep to return to them?

Just realized I have not discussed output which may be part of why I keep stumbling.

Ideally at any given time I would want to view a report which shows which reps had product. Like this:

UPC Prod desc REP1 REP2 REP3 REP4 REP5 REP6
123 m jacket xx x x x
345 m gloves x x x x x

with a XX meaning they have 2 pieces.

At given points in time, I would want to report the items no longer on hand- but that would easily come from the tx files. (duplicate pieces again throwing me? - not sure)

Again, thanks for the information you provided, and for whatever help you, or anyone else, choses to further offer.