Make Table Query and Relationships Design thoughts 1

[Valeriya]

Hi Guys,
I've got a request to optimize our local Inventory Tracking Database which is an absolute monster. I mean, I was asked to establish the relationships between tables, add indexes, change data types, basically change everything.
But here the problem I have.
Almost every table in this database is built out of make-table query, which in turn uses linked tables from the server (dbo_tables, read-only).Linked tables are updating every night with our ERP transactional data.

1. Creating the relationship, should the linked tables be included in a design?
2 .Since there is a business need, that all the tables should be constantly updated (previously they've been running a macro for all make-table queries), thinking how to update those tables without deleting them? (an update query wasn't working,” too complex parameters")

Please advise,
I've read all the articles and understand the concept, but I don't how to deal to deal with this particular case.

Thanks in advance!
Valeriya.

 

[belovedcej]

If you are using SQL Server backend, I would recommend that you consider changing to an adp file. This will allow more flexibility and you wouldn't need essentially two copies of the data and you wouldn't need to update any linkes.

In an adp files, Access contains no tables or queries at all - everything is on the server. You could simply adjust permissions - base forms off of views, etc.

Check out places on the web that talk about using Access Project - there's a forum here for it, too.

 

[fneily]

It seems you're hinting that they're first deleting the old tables then using make table queries to recreate them. Your database must be HUGE! Open the database, click on File on the menu bar, click on Database Properties and look at the General tab. You'll see the size of the database. If you then go to Tools - Database Utilities and Compact and Repair it, I'll bet you'll decrease the size by 50%. When you delete tables, it's like fragmenting a drive.
I would first check the tables normalization.
The link tables seem to intermediate tables. I don't know why you can't do update and append queries.

 

[Valeriya]

Hi belovedcej,
Thanks for the quick response.
Unfortunately, there is nothing I can really change, except the design. Our database is in .mdb file format (maybe it should be saved as .MDE after finish with the design?) and located on network shared drive.
And the database was originally created by the non IT guy who was knowledgeable in business, but not in a database design.
So, basically I need completely redesign the database and save the logic behind it.
Here the one example that is actually applies to every table in a database:
We provide materials for the consignment and store them on a client's premises. However we remain to be the legal owner of the material until the client physically consume/withdraw materials from the consignment stores. Only then does the client requires payment.

1.So to keep track our virtual inventory I' using dbo_ItmMst and dbo_ItmLoc tables to create a central Bin_Detail table for our local database.
2. Second I'm using dbo_MstrClient Table to create our local Cust_Tbl
3. Then I need a table to show what Cust assign to what bin, Bin with Owner_Table and so on...

To establish the relations between the tables is not a problem at all. But don't have a clue how to update those tables without deleting them? (make-table query)
Should the linked tables be a part of Entity Relationship Design?

Thanks a lot!

Valeriya

 

[PHV]

Instead of a make-table query you may consider a delete query followed by an append query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

Something awry this way comes?

but first a few concrete answers:

Always inclde the data sources in your relationships. Even if these are foregin sources such as linked tables or spreadsheets.

The " ... too complex parameters ... " simply means the update queries are poorly implemented - this (in turn) may imply the tables they were intended to update are also poorly designed, so a careful study of the overall study of the data structures needs to be an early (first?) step.

The only sensible difference between the .MDB and .MDE issues iw thre access to the objects designs. Saving as a .MDE only increases the opportunity for future frustration (and possible carrear altering confrontations).

By implication (this is where I depart from the "concrete") you are tracking inventory in a batch mode and hoping / expecting to convert this to an interactive or real time mode. If this is correct, you omit a centrial and crucial itemm: when/where/who/how does the actual (e.g. local to the product location) does the inventory get updated and what access do you have to this information?is this real time, on line continious; occassional as in event triggered? occassional as in when either you or the local system chooses?

More implication - there is some place that the individual inventories are maintained, and you are charged wigh the consolidation, but have only read access to these "sub stores". This would seen to imply you do not need to actually have "tables" for that data at all, as properly designed union queries can easily produce the data. Even if you "need" to actually consolidate the (sub) inventories, the (propsely designed) union queries can easily provide the recordsource for the delete/update/append queries to populate any local tables.

Inventory processing is -in my opinion- one of the more challenging aspects of database work. I have resigned myself to the torture of maintaining these three times and struggled with different aspects on each occassion. Particularly in 'retail', there are numerous pitfalls mostly in regard to the nature of human foibles. "Product" gets lost -and occassionally found. Pricing will vary by situation, location and other factors sometimes within a very short time span. There -and many more variables- need to be tracked to come 'close' to a real inventory. To do this has gotten me to desire and implement the transaction log which records each change to the database, including who changed what (at the table/field level) and whenthey changed it. Ms. A. (Jet db) does not have record triggers, so there is an extra challenge here requiring a "secured" database which permits access to data ONLY through forms (use of the dirty flag to detect changes). It would seem like you don't have this level of control over the actual inventory, so your task will be somewhat more complicated, but you can at least consider the concept and perhaps discuss it with those who do control the point of sale system(s).

Central to the issue of inventory is the concept of tracking. When item(s) are lost, there is a "hit" on the financial side (cost of goods sold does not increase, and the cost of inventory does does not decrease, so margin goes down -which is a BAD thing!!!).

Destroying/REcreating the inventory may omit some of the details which the financial side need to track, so be careful of doing it this way, and if you do go down this path, make sure the finance group is aware of what you are doing and prepared to audit the results and (formally) agree to the data preperation form their perspective during the transition.

You mention that your inventory tracking database is " ... an absoloute monster ... ". "Monster" generically implies a lack of familarity. You ask some 'generically' vague questions and supply few details. You do not mention the specific monster attributes. It is ... large? complex? divese geographically? require tracking at some extereme level of detail? just plain "ugly"? etc. How many persistient recordsets are involved in the current implementation? Does the overall database include historical (transaction) data? Is the databse split? How many objects (according to their type) are there? What is the physical size (bytes) of the overall database? Of the part you are working with? ...







MichaelRed

 

[Valeriya]

Hi Michael! I truely appreciate your advice! Before I answer any of your questions, let me go into my database and figure out the relationships between the central data sources. I'll most likely have more questions for you, but I want to be more concrete. Thanks again for you kind help!

Valeriya.