Help with Design

[DMUM via AccessMonster.com]

Hello

I have a customer who has been using Paradox and they have requested that I
transition them from Paradox to Access. I have no idea about Paradox but it
seems to not follow normal database conventions - at least the way my
customer explains some its processes.

My question is as follows...

Currently in Paradox my customer has about 8 tables that contain identical
fields. Each table is for a different client and therefore only contains
data for that particular client. Each table has a predefined name say 8162,
8163,8164,8165 etc. These names are associated directly with billing codes
that are required to be changed on a yearly bases. Since there is no
association of data in the table (no field in the table that contains "8162"
etc.)with the table name, the only way for my customer to know which table
the data came from is to name the extraction file. These tables are not
linked, so when my customer needs to query all activuty for 2006, he must
extract data from each table.

Originally my customer requested that I keep the same design in Access yet
they also wanted to be able to consolidate the data from each table to allow
the creation of reports without having to first extract data from each table.
My customer also requested that if I were to consolidate the tables into one
that I provide the ability to allow data entry without having to remember
which "Billing code" they were entering data for. To accomdoate these
requests, I consolidated all the tables into one table provided filtered
views of each billing codes data. When data entry is necessary, I have a
hidden text box that is bound to the underlying source table and populates
automaticly with the billing number of the view they are in. The new record
then contains the billing number without the individual user having to input
the billing number for each new record.

Secondly:

Since the customer is required to change the billing number each year, I
created a form to allow the customer to change the menu item on the form to a
new billing number which will then update all the old billing numbers to the
new billing number. i.e. 8162 needs to be changed to 8172. My code queries
all records in the table where 8162 is the current billing number and updates
each to 8172.

My delimma,

The customer does not want to lose the old billing numbers! I don't know a
way around this except to create a historical table and add all the
transaction to the table before updateing the billing number. My customer
doesn't seem to be happy with the idea of having 2 tables and having to
remember which one to go to for information. I know, they are currently
using more then 2. I think it's just that they are use to Paradox. Any
ideas? I can't help but wonder if I am going about this the wrong way. I
think that Paradox doesn't change the record billing number only because the
billing number never gets updated at the record level. I say I think becuase
I haven't actually worked on the Paradox database and I am unfortuneatly
dependent upon my clients descriptions. All I know is that it looks like a
DOS screen - big white letters with an awful blue background.

Any help/insights would be appreciated.

Thanks

 

[Graham Mandeno]

Hi DMUM

It's not Paradox that isn't following the conventions - it is the original
designer of this VERY badly designed database!

Congratulations for convincing your customer to allow you a redesign!

You need to have a Clients table - one record per client. Does your
customer have a way of uniquely identifying clients - a client code or
something (NOT the billing code)? If not, then use an Autonumber ClientID
as the primary key for the clients table.

Next you need a BillingCodes table. It should have the billing code
(primary key) and the client id (foreign key related to the Clients table.
Also, the date that the billing code became current (or, if you prefer, the
year to which it applies).

The billing code field in your data table can then be a foreign key related
to the BillingCodes table, and that table links the data indirectly to the
client record. You never need to change the billing code in the data,
because the entire history of billing codes is in the BillingCodes table.

For data entry, use a form and subform. On the main form, select a client
(perhaps a combo box) and select a related billing code (another cascading
combo box, defaulting to the latest billing code for that client).

Set up the Link Master/Child fields for the subform to be the billing code,
and that way only transactions for that billing code will be visible. Also,
any new records will automatically be assigned to the currently selected
billing code.

I hope this is enough to get you going

 

[vadimkra]

Hi!

I would like to ask for advice about the DB design. My customer is
manufacturing company. They need to store information of the production
process: parts, assemblies, and subassemblies and operations on them.
They have powerfull software MAS 200 that holds the inventory
information. However, for their specific purposes of production they
want to use something simpler. Right now they are using Access DB
developed by one of the engineers. Basically, it consistes of few
tables. One represents the list of all the parts and assemblies, their
parameters and operations on them - ALLPARTS(partnumber, name,
material, vendor, vendornumber, length, weigth, cut, saw, machine,
assembly...). Assembly attribute indicates whether it is part or a
subassembly. Besides, there are tables, that represent the produced
units: UNIT (partnumber, qty, sub1, sub2, sub3, sub4) - the list of
parts, that are used for this unit and which subassembly they are
included. Subs are also partnumber. In this format it is possible to
calculate the total for the part. The special query does that. For
example, if we have following records:
partnumber qty sub1 sub2 sub3 sub3
0001 5 0020 0030 0100
0020 3 0030 0100
0030 2 0100

qty = quantity in the immediate subassembly

The query will give the result:

partnumber qty total sub1 sub2 sub3 sub3
0001 5 30 0020 0030 0100
0020 3 6 0030 0100
0030 2 2 0100

Obviously, the design is not the greatest (to say the least of it
It works so far for them, but there tons of inconveniences. You have to
enter the entire subassembly every time for each unit - no reusability.
In the report, the subassemblies can be grouped by sub field, but I
couldn't find the way to include name field since it won't let use to
fields in the group header.

To make the long story short, i would really like to know - is there
appropriate solution in MS Access to this problem. In fact, this is
probably, classic problem for manufacturing process.

looking forward for your input.

Regards