Do I need to redesign project tables?
-
Using Visual Studio 2008 and Access 2007 I have tried several different ways to track Material Activity. Table MatActivity has a field for MoveFromGUID and MoveFromType as well as Move2GUID and Move2Type. The possible combinations are: From Well to Well; from Well to StorageYard; from StrorageYard to Well and from StorageYard to StorageYard. I tried it with two tables the Well table and a separate Location table but no luck. There are 4 types of wells so I have tried to add the storageyards into the well table and just use a different code for the welltype to indicate it as a storageyard. The following code gets me the sample data:
SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, w.WellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON w.WellID =m.MoveFromGUID;
The following code trying to set another relationship from the well table fails to pull any data:
SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, wWellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON (w.WellID = MatActivity.Move2GUID) AND (w.WellID = m.MoveFromGUID);
Do I need to change the table MatActivity? It basically is to document from and to locations and the units of each material being moved. There can be anywhere from several hundred units of one type of material to 30-40 types of material with only a couple of units each. It is related to the Tasks table which provides info on the crew to move the material and the dates and time assigned and actually moved. The Material table provides the description of the material to be moved. If I need to rethink the process and change the entire table structure to be able to show the actual to and from locations and remainder of data now is the time before I get much further into the project. Any suggestions and assistance greatly appreciated
-
Using Visual Studio 2008 and Access 2007 I have tried several different ways to track Material Activity. Table MatActivity has a field for MoveFromGUID and MoveFromType as well as Move2GUID and Move2Type. The possible combinations are: From Well to Well; from Well to StorageYard; from StrorageYard to Well and from StorageYard to StorageYard. I tried it with two tables the Well table and a separate Location table but no luck. There are 4 types of wells so I have tried to add the storageyards into the well table and just use a different code for the welltype to indicate it as a storageyard. The following code gets me the sample data:
SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, w.WellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON w.WellID =m.MoveFromGUID;
The following code trying to set another relationship from the well table fails to pull any data:
SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, wWellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON (w.WellID = MatActivity.Move2GUID) AND (w.WellID = m.MoveFromGUID);
Do I need to change the table MatActivity? It basically is to document from and to locations and the units of each material being moved. There can be anywhere from several hundred units of one type of material to 30-40 types of material with only a couple of units each. It is related to the Tasks table which provides info on the crew to move the material and the dates and time assigned and actually moved. The Material table provides the description of the material to be moved. If I need to rethink the process and change the entire table structure to be able to show the actual to and from locations and remainder of data now is the time before I get much further into the project. Any suggestions and assistance greatly appreciated
that looks like a database question, not a VB/VB.NET question. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use < PRE > tags for code snippets, it preserves indentation, and improves readability.
-
Using Visual Studio 2008 and Access 2007 I have tried several different ways to track Material Activity. Table MatActivity has a field for MoveFromGUID and MoveFromType as well as Move2GUID and Move2Type. The possible combinations are: From Well to Well; from Well to StorageYard; from StrorageYard to Well and from StorageYard to StorageYard. I tried it with two tables the Well table and a separate Location table but no luck. There are 4 types of wells so I have tried to add the storageyards into the well table and just use a different code for the welltype to indicate it as a storageyard. The following code gets me the sample data:
SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, w.WellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON w.WellID =m.MoveFromGUID;
The following code trying to set another relationship from the well table fails to pull any data:
SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, wWellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON (w.WellID = MatActivity.Move2GUID) AND (w.WellID = m.MoveFromGUID);
Do I need to change the table MatActivity? It basically is to document from and to locations and the units of each material being moved. There can be anywhere from several hundred units of one type of material to 30-40 types of material with only a couple of units each. It is related to the Tasks table which provides info on the crew to move the material and the dates and time assigned and actually moved. The Material table provides the description of the material to be moved. If I need to rethink the process and change the entire table structure to be able to show the actual to and from locations and remainder of data now is the time before I get much further into the project. Any suggestions and assistance greatly appreciated
Hmmm ... interesting problem. Let me offer another view of the situation. If you create a simple table with: DateTimeStamp, Status, FromLoc, FromType, ToLoc, ToType (You can use the status to be: Requested, In-Transit, Complete) Then you can create 2 other tables: Locations: (LocID, Description) MatTypes: (MatID, Description) With this design, you should be able to answer queries like: "Which material needs to move from Storage to Well #1 today ?" "What materials have arrived in Storage over the past week ?" "What are all the materials that are currently in transit ?" I'm sure I haven't considered everything, but you should get the idea. Hope this helps. David
-
Hmmm ... interesting problem. Let me offer another view of the situation. If you create a simple table with: DateTimeStamp, Status, FromLoc, FromType, ToLoc, ToType (You can use the status to be: Requested, In-Transit, Complete) Then you can create 2 other tables: Locations: (LocID, Description) MatTypes: (MatID, Description) With this design, you should be able to answer queries like: "Which material needs to move from Storage to Well #1 today ?" "What materials have arrived in Storage over the past week ?" "What are all the materials that are currently in transit ?" I'm sure I haven't considered everything, but you should get the idea. Hope this helps. David
That is essentially what I already have with the exisitng tables. Of course there is a lot more information in each table, the well table contains cost center codes, subdivision, longitute and lattitude and is a many to one relationship with the Lease table. The Locations table which I switched and tried to add to the well table still has a cost center and the address of the Location I have placed in the longitude/lattitude fields with the name of the storage yard in the Well field When I had both tables no problem pulling out the well (which is actually the well number in the particular lease generally coded like: 12-34, 9-1, or 7-50- this relates to the location on a acre gridlines which each square mile of oil field leases are divided into) or the location from the location table. It always seemed to succeed when I went from well to storage or storage to well. But well to well or storage to storage I never got any data - though I have manually created the data so I know I have records for testing purposes. I was thinking I needed to add something like:
w.well WHERE w.WellID = m.MovefromGUID AND w.well WHERE w.WellID = m.Move2GUID
somewhere in the INNER JOIN's so I am experimenting with that right now. Any other suggestions are certainly helpfull. Some of the questions we need to ask are: What materails were moved from storage to wells What materials moved from wells to storage What crews moved the materials Total quantity of each material moved by day by week, etc. So yeah there is a lot that needs to be gathered and displayed which is why I'm beginning to question my initial database design and wondering if I need to change it dramatically now to save more headaces later. Years ago with Access 2 and then Access 2000 I finally remember doing something like what I wanted. Using the hlp for Access 2007 is nigh on useless - it is more oriented toward promoting its features than offerring help. I found an old copy of Office 2000 and installed the help and went into it for Access and what I THINK I need is some form of UNION query. That should allow me to keep the seperate tables for Wells and Locations and still be able to pull out either data in wither field. If I run into problems I'll upload a sample of the code later. Larry
modified on Friday, June 25, 2010 10:07 PM