table structure

[Patti]

I don't know if I should be asking this question in the
table structure section or forms design, but I'll start
here. I am in the process of creating a database for
energy sales and purchases. I currently have four tables
created:

TblCompany
CompanyID PK
CompanyName


TblScheduleType
SchedID PK
SchedName


TblTransaction
TransID PK
TransHourEnding (0100-2400 military time )
MW (# of megawatts per hour)
Price (price/mw)
Losses (MW loss per hour)


TblMasterTransaction
MtransID PK
MtransDate
MtransTag# (an identifier assigned to both
the purchasing and selling entities)
CompanyID FK
SchedID FK
TransID FK


My question(s) are regarding the 'Hour-ending' field.

1. I need to keep track of the total MW sold for
each hour, the company and the schedule type. The Load
Scheduler making the transaction is not going to want to
enter every hour for every transaction he makes each
day. It would be too time consuming. I know I'm jumping
ahead, but I'm visualizing a form that will have all the
hours listed for the day and the scheduler can just
cursor down to the hour(s) in question and enter the MW
and $$. Is this achievable, possibly through VB code?
2. If a transaction (or tag#) covers several hours
would it be necessary to have two fields instead of one:
beginning time and ending time? (All transactions are
for a full hour. no partial hours, so if 20 MW is sold
for hour-ending 02 through 04, the beginning time would
be 0200 and ending time would be 0400). However, the MW
or the price may not be the same for each of the hours in
question. Then each hour would have to be entered
separately, causing a duplication of transactionID??
Would I possibly need two primary keys, i.e. TransID and
hour??
3. with this design, will I be able to query for
each hour, for example hour-ending 0300.? Again, I need
to keep data separate for each hour, whether it be in a
table or via Visual Basic or whatever.
4. Do I need 2 separate transaction tables for sales and
one for purchases?

I have many other questions, but thought I'd start with
these. I first need to get the tables structured
properly. I hope I explained myself clearly. If not,
let me know. Thanks for any help you can give.

Patti

 

[rpw]

Hi Patti,

Table design is a good forum for your question – you should always have a good, sound table design before developing forms. And you have done a nice job of stating your questions too.

Moving on now….

For questions 1-3, with a different table setup all of this could be easy, but I wouldn’t want to suggest that you do something that broke your business rules. I will offer a table structure, then you must decide if it will work or not. Keep your company and schedule tables the same, but only have one transaction table.

tblTransaction
TransID PK
SellingCompanyID FK
PurchasingCompanyID FK
ScheduleID FK
TransDate
TransHourEnding
MW
Price
Losses 'see comment below about this....

I may be wrong, but it looks like one company can transact (sell or purchase) with any other company, thereby creating a many-to-many relationship. This new transaction table serves as the junction table for the M:M relationship. (To create this, in the Relationships window you would show the company table twice and drag the first version’s CompanyID to the SellingCompanyID field and the second version’s CompanyID to the PurchasingComapanyID field.)

1. Base a form on the above table and your scheduler can enter an entire day’s worth of transactions in one sitting.

2. By keeping each transaction separate, you can use a query to group them together by company, schedule, date, etc. This table layout doen’t allow for a Tag#, but if there are no rules for the creation of a “TagNumberâ€, you could just combine the TransDate with the two CompanyID’s in a text field on a report or form. You wouldn’t need to store it in a table because it would be the same every time you opened the report/form.

3. With this design you can query for each hour, yes.

4. With this setup, you would not need separate tables for sales and purchases, you could use queries to view who sold what to whom and when (or alternatively, who bought what from whom and when).

"Losses" - If this is a calculated field, then consider not storing it in a table. Typically, a calculated field will always arrive at the same calculation so you don't need to store it. You just put a textbox on a form or report to display the calculation.

Hope this helps.

 

[Patti]

Thanks. That was helpful . It got me "thinking outside
my box!!", however, I realize I didn't give enough
information.

Here's some more background information:

An estimated load (MW) available for each hour is
determined ahead of time (day before or several days
before) based on a number of factors. That load changes
on an hourly basis due to weather, plant capabilities,
etc. so the trader needs to know, on an hourly basis,
how much load he is working with so he knows how much
additional MW he needs to buy or sell for the next (or
several) hours. So a 2nd dynamic form may need to be
displayed at all times showing the "balance" of MW
available to buy or sell? That was going to be one of my
other "many, many more questions" that I referred to in
my first post. I was afraid of bombarding you with too
much information "overload" so didn't mention that the
first time!!! I think I need to deal with the basics
first. (Just trying to write this post to make it easy
for someone else to understand is challenging!!!).

As far as the table structures, each transaction can
cover many hours. Also, a company can have more than one
transaction in a day. I'm not sure at this point if its
critical or not, but off the top of my head, it seems
like it would make more sense to keep the sales and
purchases separate so the hourly load can be calculated
easier? Maybe not in the table structure, but in the
data entry form. Without rambling on too much, I think
my main question right now is:

If I have a form that looks something like this (hours
vertical or horizontal on the form isn't important) so
the trader can cursor to the hours in question, and just
enter MW and $$:


Date:
Company:
ScheduleType:

HourEnding MW Price
HE01
HE02
HE03

 

[rpw]

Hi Patti,

I built a little sample solution just to make sure my idea would actually work. There are a lot of elements to getting this to go, but it ends up in a result that I think you will appreciate. In the following, the transaction table has the following structure.

tblTransaction
TransID
TransDate
TransTime
SellID
BuyID
MW
Price

1. Create a table named tblDates. Have one field "theDate" and designate it as the PK.

2. Create another table named tblTime. Have one field "theTime" and designate it as the PK.

3. Create a module and add the following (watch out for word-wrap here):

Option Compare Database
Option Explicit

'This function will populate a table with dates as specified in
'the "For..." line of the code.
'There must first be a table (tblDates) with a field name (theDate)
'for the function to work.
Dim db As DAO.Database
Set db = CurrentDb
Dim datDate As Date
For datDate = #6/1/2004# To #6/30/2004#
db.Execute "Insert into tblDates (theDate) Values (#" & datDate & "#)", dbFailOnError
Next
'This function and the 'addTime' function should both only be used once to
'populate a table.
End Function
Function addTimes()

'This function will populate a table with times as specified in
'the "For..." line of the code.
'There must first be a table (tblTime) with a field name (theTime)
'for the function to work.
Dim db As DAO.Database
Set db = CurrentDb
Dim datTime As Date
Dim intLoop As Integer
'create 60 minute intervals
For intLoop = 0 To 23
db.Execute "Insert into tblTime(theTime) " & _
"Values (#" & DateAdd("n", intLoop * 60, 0) & "#)", _
dbFailOnError
Next
End Function

4. Run each function ONCE, then disable the functions by marking each line as a comment.

5. Create a new query. Add tblCompany twice, tblDates, and tblTime. Drag theDate, theTime, CompanyID, and CompanyID_1 to the grid. Change the query to an append query. Append the fields to the appropriate fields in tblTransaction.

6. Create your form based on tblTransaction. Unbound the TransDate, SellID, and BuyID. Change the SellID and BuyID textboxes to combos based on tblCompany. Add a listbox based on tblTime and size it so that all 24 times are displayed. Modify the Record Source of the form so that it is a query based on tblTransaction. Set the criteria for TransDate to match the form's matching textbox. Set the criteria for SellID and BuyID to match the form's matching combos. Set the criteria for TransTime to match the listbox.

7. In the After_Update event of each pertinent control (TransDate,Listbox,SellID,BuyID) requery the form.

The cartesian query creates records for each possible date / time / company1 / company2 combination. The form filters the record source down to the one record that matches the input criteria.

With this setup, the scheduler can enter a date, select a selling company, select a buying company, then click on the appropriate time in the listbox and add the MW and Price to that record.

I understand that there are a huge amount of records that will have nulls in the MW and Price fields, but this does serve your input requirements and some of the other needs that you'd posted (I think).

Please post back and let me know how this worked for you.

 

[rpw]

As I re-read your problem I think that there may be an alternative way of thinking about it.

It seems like the transaction portion of the db is parallel to the classic Order entry - your 'MasterTransaction' is the equivilent of an Order and 'Transaction' is the equivilent of OrderDetails. Your 'Products' are hours and each hour can have a different quantity (MW) and Price which are stored in the OrderDetails table. However, with this type of app, generally the "Products" are displayed in a combo and you want to 'see' all 24 hours at once....

On to you next problem, though.... Let me see if I understand this correctly.

The "Scheduler" is assigned an inventory of MW that is replenished on a regular basis. It is up to him to 'sell' those MW or they are lost. If he over-sells what his inventory allows, then it is up to him to 'purchase' more so that all of his 'sales' are covered. You want to know what the balance is in this situation?

Or is it that you want to know what the current estimated amount of MW available is? (with all of those outside factors?)

And I'm still not clear on companies - can each company both buy and sell or is there a separate list of buyers and a separate list of sellers?

 

[rpw]

A couple more things...

Can you explain "Schedule Type"? It seems to be an important issue with your structure, but I can't surmise what it's for from the information posted so far.

Also, please explain "Tag#" - is this something that the industry requires?

Plus, the more I study your needs, the less I like my cartesian query solution. Maybe you should put that to the side for now...

I'm going to put more thought into this over the weekend. Hopefully, before I leave work (and internet access) you'll have answered some of my other questions.

I'll recap my understanding of your needs in a simplified manner (and please correct me if I'm wrong):

1. You are selling one product - MegaWatts.

2. You have to purchase them, then sell them, all the while keeping track of the amount in "inventory".

3. Because WHEN is a very important criteria of the product/purchase/sale, it needs to be tracked.

4. It is the duty of the Scheduler to assign the delivery of the product to a particular hour (WHEN). Plus, you want the data entry form to be easy for the Scheduler to punch in MW quantities and prices for any individual delivery hour and still view all of the day's hours without having to navigate through combos or type in the hour.

5. You want to be able to query the deliveries for a particular hour or for a span of hours.

6. You want to query a companies purchases for a date or date range and see all of the individual deliveries within that date range.

7. You want to view 'hours' in military time.

8. You want to track "Losses" which are the result of purchasing more MW than you could sell in a given Hour.

9. The "inventory" of MW is added to automatically at the beginning of each hour (pre-purchased based on other estimated criteria) and it is lost at the end of each hour.

10. The "inventory" purchased for Hour 1600 on 6/18/04 cannot be sold to any other time/date.

Is all of the above correct?

 

[Patti]

Hi,
I'm sorry I didn't get back to you before the weekend.
We must be in different time zones (central standard
time). Also, I wanted to review this information with my
supervisor to make sure we're all on the same thought
process.

In reply to your questions:

Yes, the db is parallel to the classic Order entry (or
that is how I envisioned it, unless there is a better
solution.)

As far as companies: The same list of companies can be
used for both sales and purchases. However, would it be
possible to do something like an "IF" statements so that,
in the case of a sales transaction, when the trader
enters the purchasing company name, the selling company
will automatically insert our company name, and vice
versa? I see too many chances of error occurring if the
trader has to enter our company name for every
transaction. They may erroneously enter it in the wrong
field. That was the reason I asked about having 2
separate forms or tables for sales and purchases????

After thinking about it, and without going into a lot of
detail, I don't believe "Schedule Type" or "Tag#" are
necessary fields for the table structure.
The various service schedules (schedule types) were
created by the Mid-Continent Area Power Pool (MAPP) to be
used by all the member systems based on the type of
energy they are purchasing (firm, non-firm power, etc).
We do need to query the different types for budgetary
purposes, but since there is a unique "Transaction ID"
associated with it, it really can just be
considered "another field".
The same is true for the "Tag#". The tag is assigned to
both parties (seller/buyer) by MAPP once the transaction
has been approved through the power grid. Since this is
a unique number, I had thought it could be used as the
primary key. However, the tag is not always assigned
right away, so it may be left blank temporarily while the
trader goes onto another sale or purchase. Therefore, I
think it, too, is just another field.

As far as your questions #1 - 10 in your last post, you
are correct for the most part:

#8: Actually, another field (or calculated field??)
called "Long/Short" can be used for this. The "Losses" I
was referring to are the MWs that are lost during
delivery via the power grid. The losses, if any, are
determined by a matrix designed by MAPP and is listed on
the Tag# when assigned. The trader will then enter that
number in the "Losses" field. The total of "MW"
and "Losses" together determine the total amount of MW
bought or sold for that transaction.

#9: I'm not sure if I understand this statement, but the
trader will enter the hourly estimates, or "inventory",
for an entire day, either the day before or a few days
ahead of time. This number can be changed up until the
hour before.

I hope I've answered all your questions. I really
appreciate your help on this matter.
Thanks!

Patti

-----Original Message-----
A couple more things...

Can you explain "Schedule Type"? It seems to be an

important issue with your structure, but I can't surmise
what it's for from the information posted so far.

Also, please explain "Tag#" - is this something that the industry requires?

Plus, the more I study your needs, the less I like my

cartesian query solution. Maybe you should put that to
the side for now...

I'm going to put more thought into this over the

weekend. Hopefully, before I leave work (and internet
access) you'll have answered some of my other questions.

I'll recap my understanding of your needs in a

simplified manner (and please correct me if I'm wrong):

1. You are selling one product - MegaWatts.

2. You have to purchase them, then sell them, all the

while keeping track of the amount in "inventory".

3. Because WHEN is a very important criteria of the

product/purchase/sale, it needs to be tracked.

4. It is the duty of the Scheduler to assign the

delivery of the product to a particular hour (WHEN).
Plus, you want the data entry form to be easy for the
Scheduler to punch in MW quantities and prices for any
individual delivery hour and still view all of the day's
hours without having to navigate through combos or type
in the hour.

5. You want to be able to query the deliveries for a

particular hour or for a span of hours.

6. You want to query a companies purchases for a date or

date range and see all of the individual deliveries
within that date range.

7. You want to view 'hours' in military time.

8. You want to track "Losses" which are the result of

purchasing more MW than you could sell in a given Hour.

9. The "inventory" of MW is added to automatically at

the beginning of each hour (pre-purchased based on other
estimated criteria) and it is lost at the end of each
hour.

10. The "inventory" purchased for Hour 1600 on 6/18/04

cannot be sold to any other time/date.

 

[rpw]

Hi Patti,

Just wondering... Did this work out for you?

 

[Patti]

-----Original Message-----
Hi Patti,

Just wondering... Did this work out for you?
--
rpw


Hi,

Sorry for not getting back to you sooner. I keep getting
pulled off this project to work on other things.
aaarrrrrrggghhhh!!!!

I'm struggling with this one. I set the tables up as in
your last discussion thread. I'm not understanding your
code for the Cartesian query so haven't got that figured
out yet. Also, with the new table structure you sent on
the last thread, it doesn't seem to go along with it.
Its been a year since I've worked with any VB, so am a
little rusty with that. I did manually enter date/time
into the "tblMegaWatts" table for one day just so I could
experiment with a form design. That doesn't seem to be
working, either. I'm not sure if its my relationships or
what. I'm not able to enter a company name in the form.
It automatically enters them alphabetically by record,
which is not what I want. I would like to use the
tblCompany table as a listbox to choose the company I'm
dealing with.

A couple other things:

1. In our industry, all our times are "hour-ending"
time. We don't use 00:00 as the first hour. 01:00 (or
HE01) is the first hour of each day and 24:00 (HE24) is
the last hour of the day. I'm not sure that it matters
in this situation if I change the field to either a
number (100 - 2400) or text field (HE01 - HE24) since I'm
not actually doing any calculations between the times???
Unless there's some code to change the format, but it
seems like it would be easier to reformat the field.
2. In your last post you mentioned ". and, for
displaying all 24 hours on the form, I sorta like my
listbox idea.". I'm not finding any reference to what
listbox you're talking about.
3. When I designed the PurchOrders and SalesOrders
tables I formatted the autonumber primary keys to "\P#"
and "\S#" to distinguish between a purchase and a sale
and it is possible they could end up with the same
ordered in the InventoryTransactions table. If this
works OK, would the OrderID foreign key in the
InventoryTransactions table still be a number field to
match with the autonumber, or should it be a text field
because of the P & S?

I'm not sure why I'm strugging so much with this. I've
designed a number of databases, and some have been
somewhat complex. Maybe I'm trying to make it more
difficult that it is, and forgetting about the basics,
like table structures, relationships, etc.
Anyway, I really appreciate your help and patience with
this.

Patti

 

[Guest]

Thanks, that would be great. I'm
at "NOSPAM"Ppeterson"NOSPAM"@Minnkota.com"NOSPAM"

-----Original Message-----
Do you want me te email you the test db's I've built?

That way you can see how the actual stuff I'm describing
looks/works. Then, if you have questions about a certain
feature/function, you can call me at work for
clarification. Rick (310) 715-1000 ext. 220. Don't post
your real email here - modify it by inserting "NOSPAM"
into it in a couple of places before you hit send. That
way only people can determine what the real address is
(not the spam machines).

 

[rpw]

Hi Patti,

I didn't have much time to work on this at work - I'll do some more over the weekend. So far, I have a Purchase form that lists current inventory by hour (by date), plus allows entry of hourly transactions. I'll add a few VBA goodies to it and make a Sales order form that is similar.

I'll send it to you when I've finished it (probably sometime monday). By the way - did you get the other mdb by email - did it come through the second time?