Only calculating totals and not all of records

[jzwp11]

I don't think so I have always done it this way. I simply paste it on the first empty cell and it pastes all of the new days records into the linked excel sheet and the old data is intact.

You will have to be careful because if you run your present transaction/transaction detail append queries, it will bring in all records (not just the new ones). In other words, you need to modify the transaction and transaction detail append queries to only get the new records. You might be able to do this with some criteria in your existing query or you might have to create an intermediate query that does the selection of new records and then reference that query in the append queries and not the linked excel table.



Additionally, if the old data is now stored in Access, why keep the old data in the linked Excel table too--redundant?

Do you still believe that there is a way to make one table directly from the email or did my explanation shed some light on the situation?

I assume that your are referring to an attachment to an e-mail, correct. I've never done that, so I cannot answer your question. But, it sounds like you have a current process that works fine to get the data into Access.

should my generalreport recordsource (qryselectTotals) be changed so that when qryselectTotals grabs from qrySelect it grabs the from the newly created tables instead of tblLinkedList or does it not matter?

All forms and reports should be based on the new tables since they have the appropriate design that leads to better performance. With respect to queries, only the append queries as we have been discussing should refer back to the linked spreadsheet table. Any other queries need to refer to the new tables.

[salisbut]

Ok. I have switched around the queries so only the append queries refer to the tblLinkedTable and agree that I should only put new data into the tblLinkedTable. This makes great sense thanks for clearing this up. One question that I cannot figure out: Why is the frmselection asking for a positiontype when I click the button? I am assuming it is because in the GeneralReport there is a POSITIONTYPE header and I have been trying in vain to switch that to [TransType] header but it will not let me edit it. Any ideas?

[jzwp11]

You will need to rebuild the frmSelection based on the new table structure as well as any other queries/forms/reports that were based on the old linked Excel table.

[salisbut]

I am now working on the form that I sent a couple of posts back(rptWholesale) and was curious to know how I can make all the companies show up on the market sales and market purchases even if their megawatt and cost records are null. Currently it only shows the companies that have purchases or sales for the month. It think it has something to do with a left join but I can't make it work. Also, why does my formula not work for the EWEB commission not work? It should be 1% of the market sales revenue and I have tried setting the control source of the report to qryMarketSales since this is the only calculated field but it hasn't worked. Thanks.

[jzwp11]

The EWEB calculation has to be in the report footer not the page footer to work correctly.

Yes, you will need a left join to show all companies, but to do that you will need to redo your qrySelectWholesale and break it down into intermediate queries otherwise you will get an error referring to ambigous joins.

[salisbut]

Ok, this is almost done, but I can't get my append queries to work. I have set it up so I delete all of the current months data and then upload the daily email. I have my macros set up and the delete queries work fine, but the qryTransactions and qryTransactionDetails are giving me fits. In my table I am uploading the 2230 records for July so when I upload the qryTransactionDetails it should upload 2230 records. Yet it only uploads 1852. The qryTransactions uploads 1090 records which seems to be right but there doesn't seem to be a way to verify this. It is also possible that the qryCompanies or qryCompanyTradeNumbers may not be uploading correctly and this is screwing up the later queries. The order of events is in the macros. Curious to know your thoughts on how to troubleshoot this or what you think might be wrong. The current database is attached. Thanks

[jzwp11]

To check the number of unique transaction records that need to be added you will need to query your imported data

This query will give the details & you can just see how many records are returned or you can do a totals query on the query below to just display the actual count

SELECT DISTINCT tblLinkedTable.COUNTERPARTY, tblLinkedTable.BEGTIME, tblLinkedTable.ENDTIME
FROM tblLinkedTable;


Alternatively, you can just use a nested query like this:

SELECT Count (Counterparty) as CountOfTransRecordsToAdd
From (SELECT DISTINCT tblLinkedTable.COUNTERPARTY, tblLinkedTable.BEGTIME, tblLinkedTable.ENDTIME
FROM tblLinkedTable)

You have to check the new data for any new companies and new trade numbers since there has to be records in those tables that match those in the imported data in order to successfully append all transactions. I could not verify this since you did not include the Excel file in your attachment

BTW, why are you deleting the older data? What happens if your boss wants a summary for the year?

[salisbut]

The reason I am deleting the old data is because it is inaccurate. Each day new records are added to the previous days and certain transactions are altered to adjust to the market or to the amount of power dropped over a load. I have cleared this with my boss. Ok, now, so I ran that query and 1090 records shows up so it appears that my transaction query is working correctly. This means that it is narrowed down to qryCompanyTradeNumbers and qryTransactionDetails. I am not sure which excel document you want so I have attached two. July 12th is the daily email attachment that I received. test is the master spreadsheet with july 12th added to it. July 12th has the same information that is in tblLinkedList right now. How would I check to make sure qryCompanyTradeNumbers is importing correctly? It seems that my intermediate subquery should make it work, just can't put my finger on it...

[jzwp11]

You have 27 new trade numbers that are not in your trade numbers table. Since you don't have them, the corresponding transaction details related to those 27 trade numbers will not append.

Basically you have to check each new dataset for new companies and new trade numbers and append those to their respective tables prior to appending the transaction and transaction details.

To find new trade numbers, create a query based on your linked table that finds unique trade numbers:

query name: qryTradeNumbersFromLinkedTable
SELECT DISTINCT tblLinkedTable.TRADE
FROM tblLinkedTable;

Now create an unmatched query (using the wizard) to compare the data from qryTradeNumbersFromLinkedTable to the data in your table that holds the trade numbers.

query name: qryTradeNumbersFromLinkedTable Without Matching tblCompanyTradeN
SELECT qryTradeNumbersFromLinkedTable.TRADE
FROM qryTradeNumbersFromLinkedTable LEFT JOIN tblCompanyTradeNumbers ON qryTradeNumbersFromLinkedTable.TRADE = tblCompanyTradeNumbers.TradeNumber
WHERE (((tblCompanyTradeNumbers.TradeNumber) Is Null));

This query provides the list of trade numbers that must be appended prior to appending the transaction data. Of course, you will have to determine which companies these trade numbers correspond to and append those companies if they are new. If not new, just append the new trade numbers (of course tied to the correct company). BTW you can use the same approach to find new companies as well.

DB is attached

[salisbut]

Ok! This works perfectly! Thanks! Now going in a different direction, is it possible to convert this to run sql on the back end and somehow grab the attachment from the daily email and import it into the linked table and then run the macros?

[jzwp11]

Getting the attachment and bringing it in as a linked table is beyond my knowledge; you may try a new post on the forum to ask that specific question.

I do know that once the table is linked, the rest can be automated through a module using Visual Basic for Application code. In earlier versions of Access there was a utility that convert macros to VBA code. So if there is a way to get the table in using VBA, you should be able to combine that with the coverted macros into 1 module.

[salisbut]

Ok before we wrap up this thread I just wanted to thank you again for all the help. I definitely was clueless about Access before this project began and you were a great source for help. Thanks for patience. If there is any way I could write up a comment card or something I would

[jzwp11]

You're very welcome.

If there is any way I could write up a comment card or something I would

You can click on the little balance symbol on the upper, right side of this post and provide reputation comments/points, I believe. For more info check out the FAQ-->General Forum Usage-->User Profile Features-->Reputation