A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Joins



 
 
Thread Tools Display Modes
  #11  
Old August 29th, 2006, 08:35 AM posted to microsoft.public.access.tablesdbdesign
Rod Plastow
external usenet poster
 
Posts: 195
Default Joins

AG / Jeff,

As I understand it the requirement is not uncommon. Many industries -
aircraft spares, pharmaceuticals , foodstuffs - need to track their
production and distribution by batch. Where this requirement seems a little
different is that there are three production processes that need (?) to be
kept separate.

I went back to the drawing board and came up with four (perhaps five) tables
as follows.

Inventory Item. This table has a PK that is the item id and contains all
common attributes such as description. It is I think akin to Jeff's 'Main'
table.

Production Process (or Type). AG, this is akin to your A, B & C. I would
expect three rows on this table. The Inventory Item table contains a foreign
key to the Production Process if each item is manufactured by one and only
one process. Otherwise you need to resolve the many:many relationship with a
cross-reference table. (Hence the possible fifth table mentioned above.)

Batch. This is a child table of Production Process. If your batch
numbering is unique across all processes then the batch number is sufficient
as a PK, otherwise you need to append the PK of Production Process. The
table attributes include a foreign key for Inventory Item. The table
contains all the production related data.

Inventory. This has the same key(s) as Batch and strictly 3rd Normal Form
suggests both tables should become one. However there is a case for
separating the data as the purpose of the two tables and their life cycles
are completely different. This table also has Inventory Item as a foreign
key. The table contains all the storage/stocking related data, shelf lives,
quantities on hand, etc.

If you follow all the relationships I think there will be no problem in
enforcing relational integrity and specifying cascade update and delete as
appropriate. This proposal does not solve the redundancy problem that will
occur on the Batch table, but I believe the extent of that problem is now
much reduced. If its still bad then sub typing is the way to go.

Hope this helps,

Rod

PS Sorry AG, I cannot follow up you Summation post as the search engine
keeps hanging.

"Jeff Boyce" wrote:

If your main table holds the MainID and ThingType, and that's it, one of
your sub-tables can hold a SubTableID, MainID, and all the attributes
specific to that table's sub-type. Each of the others hold the same.

I don't see how you'd end up with orphans if you first create a MainID for
the Thing, then add a sub-table record with the MainID (as a foreign key),
it's own SubTableID (the primary key), and whatever attributes apply.

The next Thing you add to the Main table might be a different type, so after
adding a MainID in the Main table, you'd have to add the new sub-table
record in the appropriate (different) subtable.

Where are the orphans coming in?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Access Greenhorn" wrote in
message news
Jeff, you possibly give me hope.

I am not familiar with sub-typing in an Access context. I'm assuming that
I
would put the fields common to all the production tables into one table
and
then class each type of item as a subtype and hold the details of the
batches
in the subtype tables.

But how is this all structured such that I would avoid my current problem
of
not being able to reinforce referential integrity due to orphans?

Thanks,
AG




  #12  
Old August 29th, 2006, 11:49 AM posted to microsoft.public.access.tablesdbdesign
Rod Plastow
external usenet poster
 
Posts: 195
Default Joins

Hi again,

The search engine has at last located your 'Summation' post; I copy and
paste the SQL here.

SELECT tblInventoryA.Batchcode, tblProd1A.QtyMade, Sum(tblKitsSent.QtySent)
AS SumOfQtySent, tblInventoryA.LossedorTossed,
[QtyMade]-nz([QtySent])-nz([LossedorTossed]) AS OnHand
FROM tblProd1A LEFT JOIN (tblInventoryA LEFT JOIN tblKitsSent ON
tblInventoryA.Batchcode=tblKitsSent.Batchcode) ON
tblProd1A.Batchcode=tblInventoryA.Batchcode
GROUP BY tblInventoryA.Batchcode, tblProd1A.QtyMade,
tblInventoryA.LossedorTossed, [QtyMade]-nz([QtySent])-nz([LossedorTossed]);

What you are trying to do is obvious. Why you need the NZ function is also
obvious. However I stared long and hard at the code wondering why it worked
at all and why you did not keep getting the aggregate function message.

OK, let me suggest some SQL code that will do what you want provided that
tblInventoryA has zero or one rows for the BatchCode, that is there are not
multiple fields for LossedorTossed for each BatchCode. Oh, by the way, if
you omit the second argument of NZ the function returns a zero-length string
which is not what you want when summing numbers

SELECT tblProd1A.Batchcode, Sum(NZ(tblKitsSent.QtySent,0)) AS TotalSent,
First(NZ(tblInventoryA.LossedorTossed,0)) AS QtyScrapped,
First(NZ(tblProd1A.QtyMade,0)) AS TotalMade,
[TotalMade]-[TotalSent]-[QtyScrapped] AS QtyOnHand
FROM (tblProd1A LEFT JOIN tblInventoryA ON tblProd1A.BatchCode =
tblInventoryA.BatchCode) LEFT JOIN tblKitsSent ON tblInventoryA.BatchCode =
tblKitsSent.Batchcode
GROUP BY tblProd1A.BatchCode;

The use of NZ for the QtyMade aggregate is probably redundant; there will
always be a non null value. You could consider changing the join between
tblProd1A and tblInventoryA to be INNER JOIN if you only want rows in your
result set where an inventory record exists as well as a production record.

Have fun.

Rod

"Rod Plastow" wrote:

AG / Jeff,

As I understand it the requirement is not uncommon. Many industries -
aircraft spares, pharmaceuticals , foodstuffs - need to track their
production and distribution by batch. Where this requirement seems a little
different is that there are three production processes that need (?) to be
kept separate.

I went back to the drawing board and came up with four (perhaps five) tables
as follows.

Inventory Item. This table has a PK that is the item id and contains all
common attributes such as description. It is I think akin to Jeff's 'Main'
table.

Production Process (or Type). AG, this is akin to your A, B & C. I would
expect three rows on this table. The Inventory Item table contains a foreign
key to the Production Process if each item is manufactured by one and only
one process. Otherwise you need to resolve the many:many relationship with a
cross-reference table. (Hence the possible fifth table mentioned above.)

Batch. This is a child table of Production Process. If your batch
numbering is unique across all processes then the batch number is sufficient
as a PK, otherwise you need to append the PK of Production Process. The
table attributes include a foreign key for Inventory Item. The table
contains all the production related data.

Inventory. This has the same key(s) as Batch and strictly 3rd Normal Form
suggests both tables should become one. However there is a case for
separating the data as the purpose of the two tables and their life cycles
are completely different. This table also has Inventory Item as a foreign
key. The table contains all the storage/stocking related data, shelf lives,
quantities on hand, etc.

If you follow all the relationships I think there will be no problem in
enforcing relational integrity and specifying cascade update and delete as
appropriate. This proposal does not solve the redundancy problem that will
occur on the Batch table, but I believe the extent of that problem is now
much reduced. If its still bad then sub typing is the way to go.

Hope this helps,

Rod

PS Sorry AG, I cannot follow up you Summation post as the search engine
keeps hanging.

"Jeff Boyce" wrote:

If your main table holds the MainID and ThingType, and that's it, one of
your sub-tables can hold a SubTableID, MainID, and all the attributes
specific to that table's sub-type. Each of the others hold the same.

I don't see how you'd end up with orphans if you first create a MainID for
the Thing, then add a sub-table record with the MainID (as a foreign key),
it's own SubTableID (the primary key), and whatever attributes apply.

The next Thing you add to the Main table might be a different type, so after
adding a MainID in the Main table, you'd have to add the new sub-table
record in the appropriate (different) subtable.

Where are the orphans coming in?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Access Greenhorn" wrote in
message news
Jeff, you possibly give me hope.

I am not familiar with sub-typing in an Access context. I'm assuming that
I
would put the fields common to all the production tables into one table
and
then class each type of item as a subtype and hold the details of the
batches
in the subtype tables.

But how is this all structured such that I would avoid my current problem
of
not being able to reinforce referential integrity due to orphans?

Thanks,
AG




  #13  
Old August 29th, 2006, 07:49 PM posted to microsoft.public.access.tablesdbdesign
Access Greenhorn
external usenet poster
 
Posts: 26
Default Joins

Rod,

You called it pretty close. I work in Virology and am designing the
database to keep track of reagents to be used in diagnostic tests.

What I ended up doing and if you can check it over for any problems I may be
failing to foresee, I have 6 production tables that hold the details of each
batch of 6 different classes of reagents. They are all joined to a table
called QtyMade that holds all of the common information and, of course, how
much was made. The PK of the QtyMade table is comprised of the PKs of the
production tables and acts as the parent in a left join to the child
production tables.

I then have an Inventory table that holds temporary information like where
the reagents are stored. It also is the child in a left join to the QtyMade
table.

The QtyMade table is also a parent in a left join to the Package Details
table that keeps track of what and how may reagents were sent in a package.

Any problems with this setup?

Thanks all,
AG
  #14  
Old August 29th, 2006, 08:12 PM posted to microsoft.public.access.tablesdbdesign
Access Greenhorn
external usenet poster
 
Posts: 26
Default Joins

Mucho gracias, merci beaucoup and dziękuję bardzo Rod. This query was
driving me nutty; not knowing why it wasn't working. I will definitely
implement your improved code in the database when the time comes.

And you are right on your assumptions, tblInventoryA has one row per
batchcode and there are not multiple fields/(rows) for LossedorTossed for
each batchcode.

AG
  #15  
Old August 30th, 2006, 12:40 PM posted to microsoft.public.access.tablesdbdesign
Rod Plastow
external usenet poster
 
Posts: 195
Default Joins

Hi AG,

I've been thinking about this one all morning, trying to visualise your
design. I think I now have it. The unusual feature is these 6 production
tables but I can understand how you arrived at this if you are worried about
redundancy - and this debate about redundancy is the crux of the whole
matter, but let's leave the debate to one side for the moment.

What are the adverse consequences of having 6 tables rather than one? I
suggest that if in future you need to add a seventh reagent class/table then
this will be more difficult than adding additional columns to an existing
table. Then there is the difficulty and additional complexity you have
already experienced of defining relationships and queries. Apart from these
two points I can see no obvious 'minefields' ahead.

I believe the relationships you describe to be incorrect but before
explaining what I think they ought to be we have to be clear whether we are
talking about database design or query design.

In database design I prefer to use the term relationship. A relationship is
a permanent association between two tables based on key values and usually
describes the real-life nature of of the data interrelationships. You can
define one and only one relationship between two tables. If you wish - and I
can see no point in defining relationships otherwise - the database manager
will ensure referential integrity, et., etc. Database designers will refer
to relationships in terms of one-to-many, one-to-one, etc.

In query design I prefer the term join. Joins are temporary, transient
associations for the purpose of that query and only that query. You may
define as many joins between two tables as you wish. The joins can be
contrary to the database relationships! Access uses the terminology of
LEFT/RIGHT JOIN and INNER JOIN.

I want to talk about your database relationships. Each row on each of your
6 production tables describes a production batch. Each row on your QtyMade
table describes additional common data for each production batch. The
inference is that the total count of rows across all 6 production tables
should equal the count of rows on the QtyMade table; a pair of rows is needed
to completely describe the batch. Each pair of rows should have the same
primary key, probably BatchCode. (I think this is what you are saying but
I'm not too clear about it.) Now, because QtyMade contains a row for every
batch irrespective of its reagent class, the primary key, BatchCode has to be
unique across your application.

When you come to defining your database relationships it makes no sense to
nominate one side as 'parent' and the other as 'child.' What you need is a
one-to-one relationship between the QtyMade table and each of your production
tables. If you get it right you will see a number 1 at each end of the line
joining the tables in the Access Relationships diagram.

If you store a batch of reagent in more than one location then you need a
one:many relationship between your batch and the inventory location. However
if you always store a batch together in one and only one location then why
not simply add the location attributes to the QtyMade table? But if you
insist on a separate table then again it is a onene relationship and I
suggest the primary key of the Inventory table is also BatchCode.

Finally there is the Package that I like to think of as a shipment. The
Package has a many:many relationship with batches so you need to resolve this
with a cross reference/linking table: Package has a one:many relationship
with your linking table and similarly QtyMade has a one:many relationship
with your linking table. You can construct your linking table with a
compound key of BatchCode and PackageId or give it a meaningless primary key
(autonumber) and include both BatchCode and PackageId as foreign keys.

That's enough for now.

Regards,

Rod

"Access Greenhorn" wrote:

Rod,

You called it pretty close. I work in Virology and am designing the
database to keep track of reagents to be used in diagnostic tests.

What I ended up doing and if you can check it over for any problems I may be
failing to foresee, I have 6 production tables that hold the details of each
batch of 6 different classes of reagents. They are all joined to a table
called QtyMade that holds all of the common information and, of course, how
much was made. The PK of the QtyMade table is comprised of the PKs of the
production tables and acts as the parent in a left join to the child
production tables.

I then have an Inventory table that holds temporary information like where
the reagents are stored. It also is the child in a left join to the QtyMade
table.

The QtyMade table is also a parent in a left join to the Package Details
table that keeps track of what and how may reagents were sent in a package.

Any problems with this setup?

Thanks all,
AG

  #16  
Old August 31st, 2006, 11:07 PM posted to microsoft.public.access.tablesdbdesign
Access Greenhorn
external usenet poster
 
Posts: 26
Default Joins

You assumptions are correct up until the last paragraph.

I am talking about relationships and across all 6 tables and the QtyMade
table the rows will be equal. I have set them in a one to one relationships
with a left join type (sorry I know you prefer to reserve the word join to
queries but that is how access describes it). And the PK across the 6 tables
are unique from eachother.

The batches/items are not stored in more than one location but I also don't
need to hand onto that information so it is in a separate table so it can be
purged when we no longer have those items on the shelves. That relationship
is also a left one-to-one.

And I have a one-to-many relationship bxt the QtyMade table and the
PackageDetails table, since the batches will be sent out in increments, so to
speak.

So...I think I'm safe. From what I have gathered from your response.

Thanks,
AG

P.S. Could you look at another posting of mine, I need some assistance on
code. Search "Cascading Combo box and additional parameter". Thanks again.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.