Only calculating totals and not all of records

[salisbut]

I am trying to create a database which keeps track of all the buying and selling of electrical power that our company does with other companies. I have created 2 queries for each company, one for the buying we do with that company, and one for the selling we do with that company. Now I want to create a form that gives them their bill for every month. I am curious to know if I can grab info from two queries into one form? I am not sure because the record source only accepts one. Also, in this form I want to grab the totals from the buy and sell queries with respects to the Megawatt totals (quantity) and the cost(value). So, for example, one of the text boxes I type =(Sum[qryCalpineSell]![QUANTITY]) which should return the total megawatts but it returns an error. Am I using the form the right way? How do I find out what this error is so I can fix it? Thanks for the help in advance.

[jzwp11]

Welcome to the Forum!

As you already indicated, a form can have only 1 record source. One way is to combine the two individual queries into a UNION query. A UNION query can only be constructed in SQL View. The number of fields of the individual queries must be the same and generally the datatypes as well (but not absolutely necessary, if I remember correctly). Alternatively, you could have a main form (based on your customer table) with two subforms each based on one of your queries.

Out of curiosity, how are you recording the transactions? I would think that 1 query could handle it given a table structure like this:

tblTransactions
-pkTransID primary key, autonumber
-fkCustomerID
-dteTrans(transaction date)
-TransQuantity (+ for product sold, - for product purchased note: your business rules may be different)


If the price is set, then it would be in a different table. If each transaction has its own price, then it would go into this table.

[salisbut]

Ok so I found out that I think what I am trying to make is a report because I am trying to make a bill to send out to our industrial customers at the end of the month. Here is how I have my database setup...I have one table that is linked to an excel spreadsheet. This is because each day I receive an email which contains an excel spreadsheet that contains our transacations from that day. I then copy and paste the new cells from that email and into the master excel spreadsheet and it updates my database. In my database I initially had 25 or so queries which took the information for each company and put it into a query. The reason I made twice as many queries was to seperate each company to buy or sell because I couldn't figure out how to grab the information when I got to the report. The table has 10 fields: 1. Counterparty(Company we work with) 2. Product (Power or transmission) 3. Trade (trading number for certain types of transactions) 4. Positiontype (Buy or Sell) 5. Quantity (Number of megawatts) 6. Price 7. Value (Quantity*Price) 8.Begtime(when the transaction started) 9. Endtime(when the transaction ended) 10. Name (many null values, extra designator for different power supplies). I agree that 1 query would be better for each company but how do I just grab the sum of a companies buying into a report or the sum of a companies selling into a report. Something like =(Sum[qryCalpine]![QUANTITY]) where postiontype = Buy ?!?!?! Not sure on this syntax kind of learning access on the fly. I have uploaded the database. Thanks

[jzwp11]

I can't open your database since I don't have Access 2007 here at work; so I'll have to take a look at it from home tonight unless you can save and post it in an earlier Access format (2003 would work)?

[salisbut]

hopefully this works for you...

[salisbut]

Also, if this opens for you, I am curious to know why my buy totals and sell totals in the general report that I am creating print out tons of times when I just want them to print out once.

[jzwp11]

The database opens OK, but I will need the linked spreadsheet as well if it is not too much trouble.

[salisbut]

Oops sorry about that. Here is the excel sheet that corresponds to the database. Also, I am creating the report for the Calpine buy and sell, as I am hoping to copy that template and use it for my other ones after it has been created.

[jzwp11]

You don't need all of those queries by company. I have reworked your database; it is attached. I created a form where you can select the company, the dates of interest and then just click the button to view the report. I think this is close to what you were after.

Additionally, I would recommend taking your imported data and migrating it into an appropriate relational database design. This will be much more efficient when the number of records start to grow. I can help you with the design, but it's getting late, so perhaps tomorrow...

[salisbut]

I cannot thank you enough for your help I have been pounding my head on this for two weeks straight now and could not find the most efficient design. Can you enlighten me more on how I would go about putting it into a more relational database design? Also, is it possible to automate it so that with an sql server running on the back end I could grab the excel attachment from the the daily email and import it automatically? Curious to know if Access is capable of this...
Also, looking at the database that you have created, there are three things I am trying to fix but it is hard for me to completely follow your design. 1. I need the energy purchases to be on the top and the energy sales to be below it. Also, the numbers are exactly backwards. To explain, the sell should be their purchases from Clatskanie and the buy should be the energy sales to Clatskanie. 2. The GL number for the purchases is 447.110 (STWP) and the sales is 555.000 (PURPOW). 3. Finally, if I want to make a total payment due text field on the bottom of the report, how do I grab the total purchases and subtract the total sales from it?

[jzwp11]

Also, looking at the database that you have created, there are three things I am trying to fix but it is hard for me to completely follow your design. 1. I need the energy purchases to be on the top and the energy sales to be below it. Also, the numbers are exactly backwards. To explain, the sell should be their purchases from Clatskanie and the buy should be the energy sales to Clatskanie. 2. The GL number for the purchases is 447.110 (STWP) and the sales is 555.000 (PURPOW). 3. Finally, if I want to make a total payment due text field on the bottom of the report, how do I grab the total purchases and subtract the total sales from it?

Regarding the values and text; I had my IIF() function in the text box testing for "BUY" when it should have been "SELL". I fixed that now. Regarding the GL; I assume that is a general ledger designation? Since it is tied to the positiontype it cannot go in the detail section, so I moved it up position type header section. With respect to the total, you just need to sum the sumofvalue control in the report footer (won't work in the page footer section). The revised DB & Excel file are attached.

Also, is it possible to automate it so that with an sql server running on the back end I could grab the excel attachment from the the daily email and import it automatically? Curious to know if Access is capable of this...

Access and SQL Server are two different animals. You can build the structure in Access and then upsize it to SQL Server fairly easily. This will essentially split the database into front end (forms, queries, reports) and back end (tables). THe tables will then be in SQL Server not Access, but they will appear as linked tables in your Access front end. Regarding your Excel data, you can import or link to the Excel file in the Access front and and then use append queries to get the data into the appropriately designed tables.

I have some other things to attend to until this evening, so I'll have to follow up later tonight with my thoughts on an appropriate structure. In the mean time, could you give a brief explanation of the fields in your current table? I understand most of them but there are some, I don't know what purpose they serve.

[salisbut]

Ok thanks again. Here are the ten fields that are given in the email. I will go in depth to make sure we are on the same page. 1. Counterparty(This is the company we buy or sell from. We seem to add one or two companies every month. Some months we deal with a company a lot and sometimes we never deal with them. etc. 2. Product (Power or transmission) Most of the product we buy is power, but on the rare occasion it is transmission. 3. Trade (trading number for certain types of transactions. For example, with BPA we have four different trading numbers which correspond to four different type of power we buy or sell from them. These correpond directly to the name field which is only used for BPA and Clatskanie PUD. For example, for BPA you have BPA Block, BPA Slice, and BPALossRtn and one other that does not have a name but we refer to as BPAMarket. Clatskanie PUD has 2 different trading numbers which refers to the two different power loads that we own that we draw from. It looks to me like every other company has 2 different trading numbers one for buy and one for sell. 4. Positiontype (Buy or Sell. I think this one is self explanatory.) 5. Quantity (Number of megawatts that are either bought or sold from each company.) 6. Price (This is the price for each megawatt that was bought at that transaction. The market flucuates, so these are bought at different prices on different times and different days. 7. Value (Quantity*Price. In other words, the total money that was used during the transaction, whether bought or sold.) 8.Begtime(when the transaction started) 9. Endtime(when the transaction ended) 10. Name (many null values, extra designator for different power supplies. The only companies that this is used for is BPA, Clatskanie PUD, and Idaho Power. All the buying we do from Idaho Power is through the ArrowRock generator. CLATS Halsey refers to the load in a town called halsey and CLATS Load is a load here). Hopefully this helps you out.

In the meantime, I am currently working on a report that will show wholesale power & transmission for a month which means it wants all the companies totals for the month. I will try to post that today before I leave the office and maybe bounce some ideas off. Also, is there a command which can make the values in appear positive but not screw up the formulas we have set up? For example, the accounting department wants the QUANTITY on the report to always appear positive whether it is for purchases or sales and they want the cost for the sales to appear positive and the total payment due to be either positive or negative depending on what is greater. Thanks

[salisbut]

Ok, so here is an updated database, I have created a market sales query and market purchases query and have tried to make them subreports to stick in a wholesale report so I can receive the data between a certain value of time (still gotta work on that). My question is, why do these things repeat themselves over and over on the report? Also, I want to create a NET position query but for some reason I cannot retrieve the VALUE sum of the market sales for whatever reason (Maybe because it is negative!?!?).

[jzwp11]

Before we go any further with queries, forms and reports, the table structure has to be cleaned up.

First we need a table to hold the names and address of the companies you deal with

tblCompanies
-pkCompanyID primary key, autonumber
-txtCompanyName
-txtAddress
other fields related to the company

You have trade numbers that are associated with each company. A company can have many trade numbers

tblCompanyTradeNumbers
-pkCoTradeNoID primary key, autonumber
-fkCompanyID foreign key to tblCompanies
-TradeNo
-action (buy or sell?)

Since the trade number tells what position type you are dealing with (i.e. buying or selling); there is no need to have the position type in the transaction table since it is governed by the trade number


tblTradeTransactions
-pkTradeTransID primary key, autonumber
-dteTrans (transaction date)
-fkCoTradeNoID foreign key to tblCompanyTradeNumbers
-Quantity
-currPrice

In most cases, a calculated value such as your VALUE field is not stored in a table. The value is just calculated on the fly when need (in queries, forms or reports). You store only the core data (i.e. that used to do the calcs).

Oops, one question I forgot to ask. Can multiple lots of megawatts be bought or sold between the beginning and ending time of the transactions? That appears to be the case from your data. If so, the above structure will not be correct.

I'm really not clear on the #10 field (name). Are the names subsets of the company? Different locations?

[salisbut]

Ok so with regard to the multiple lots of power per hour: I have scanned the table a decent ways and it seems that only BPA and Clatskanie PUD buy mutiple lots of power at the same time. BPA and Clatskanie PUD are very different animals than the rest of companies as I will explain a little ways down. The names are kind of different for each case. For Idaho Power, if the name reads ArrowRock, that is a dam that generates power for us that we buy from. BPA is a more complicated story. CLSK Block, CLSK Slice, and CLSKLossRtn refer to the types of power that we receive from them. Block refers to a set chunk of power that is given to us each month by our agreement with BPA and the federal company. Slice refers to our percentage of what BPA produces each month. We get a certain cut of whatever is produced by the Bonneville Dam. Loss return is the power we sell back to Bonneville that we don't need. BPA is a special company because the only thing we need to keep track of for the billing statements is the market trading with them, which are the BPA entries that do not have a NAME. THe Block, Slice, and Loss Rtn could possible be stored in a completely different table. For Clatskanie PUD, we also do not need to keep track of this for billing statements because this just keeps track of when we use each of these substations. CLSK Load is the power we get from the substation in our town. CLSK Halsey is the power we get from a substation we own in Halsey. So these could possibly have there own table to keep them out of the billing statements although like BPA they must be kept track of. Also, the numbers in the price field for the BPA's are just fillers that the company gives to us because we pay them a set amount (except for the market trading with them.) So, knowing this, should I proceed with using the design below, or does this change our approach?