Only calculating totals and not all of records

[jzwp11]

Starting over...

I've gone through your spreadsheet and your posts more closely, and this is what I see as your existing relationships.

1. A company can have many trade numbers.
2. A trade number is tied to a company and is unique, but it does the following
a. defines the product (power or transmission).
b. defines the type of transaction (buy or sell)
c. defines for those specific companies the "name".
3. During a certain beginning and ending time associated with a company's transaction, you can have many individual transactions and each transaction is tied to the trade number.

Based on the above analysis, I see the following structure:

tblCompanies
-pkCompanyID primary key, autonumber
-txtCompanyName
-txtAddress
-txtCity
other fields

tblCompanyTradeNumbers
-pkCoTradeNoID primary key, autonumber
-fkCompanyID foreign key to tblCompanies
-longTradeNumber
-fkProductTypeID foreign key to tblProductTypes
-fkTransTypeID foreign key to tblTransTypes
-txtSpecialName

tblProductTypes (2 records: power and transmission)
-pkProductTypeID primary key, autonumber
-txtProductType

tblTransTypes (2 records: buy and sell)
-pkTransTypeID primary key, autonumber
-txtTransType

tblTransactions
-pkTransID primary key, autonumber
-fkCompanyID foreign key to tblCompanies


-dteTransBegin (date/time field)
-dteTransEnd (date/time field)

tblTransactionDetails
-pkTransDetailID primary key, autonumber
-fkTransID foreign key to tblTransactions
-fkCoTradeNoID foreign key to tblCompanyTradeNumbers
-quantity
-currPrice

[salisbut]

Ok so a couple of questions...1. Should these tables be linked somehow to tblLinkedTable so when I update the excel spreadsheet it updates new database? 2. I am a little confused about tblTransactionDetails, should this include all the quantity and prices including the duplicates? If so, is this the only table that contains the entire excel spreadsheet? 3. I have setup the relationships but it doesn't seem to want to upload the primary keys to the foreign keys. Ok that is all for now I have uploaded the database in case it helps you explain what I am doing wrong.

[jzwp11]

Should these tables be linked somehow to tblLinkedTable

NO

The linked table is not normalized, the table structure I have presented is. The most efficient design in a relational database is a normalized design. Spreadsheets are not normalized.

You will need to migrate data from the spreadsheet into the new design as I indicated earlier here:

. 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

The foreign key fields must be long number datatypes to be equivalent to the autonumber datatype of the corresponding primary keys.

The migration can be automated, but that I'll leave that for a later discussion.

2. I am a little confused about tblTransactionDetails, should this include all the quantity and prices including the duplicates? If so, is this the only table that contains the entire excel spreadsheet?

Duplicates? I'm not sure what you mean.

[salisbut]

Ok so I have created a new database and here was the order I did things in. 1. Imported the Excel database into a new table. 2. Used analyze table to separate into the tables specified. 3. Once this was done, I noticed that lookup tables had been created. 4. Then I added the foreign keys to the tables and made their relationships. After this had been done, I noticed the lookup tables were not there anymore, and also these foreign keys are empty. I tried any or all of the three boxes in the relationship window but none seemed to get the values to show up.
So I guess there are four questions: 1. Is this the right process of doing things? 2. How do I get foreign keys to display values (The data types are the same)? 3. What is the next step after that? 4. Finally, what should my subdatasheets be on the new lookup fields after the foreign keys were added?

[jzwp11]

1. Is this the right process of doing things?

No

When you use the analyzer, it sets up table-level look up fields which is generally not recommended. See this site for more on the evils of lookup fields.

[jzwp11]

I've adjusted the foreign key fields to long numbers and re-established the relationships. Your revised DB is attached.

[salisbut]

So before I go further from here, I need to understand relationships better before I continue making mistakes. I read this article here: http://download.microsoft.com/docume...lationship.htm
and gathered that the relationships we are making create the plus signs (lookup tables I think) on the left side which are references to the other tables. What is weird though is that none of our plus signs show anything. Also, I always thought that a relationship would import the primary key from the other table into the foreign key for the other table. Yet even though we have made these relationships, nothing is imported and these columns are empty. Should append queries also be used here to import the different ID's to the tables? Thanks again for your patience I know these are basic questions, but I have to get the basics down before I can go further.

[jzwp11]

Nothing will show up (either a record or a foreign key) until a record is added in the related table (the many side of the one-to-many relationship). You can add records individually through form/subforms or individually or in bulk with append queries. Regarding the subdatasheets (the +), you have to physically set up each one even if you have already set up your relationships. You can enter data through the subdatasheets and the foreign key will populate. Frankly, I don't waste my time with the subdatasheets. All user interaction with the database should be through forms. Your users should never see your tables.

[salisbut]

Ok this makes a lot of sense and definitely clears some stuff up for me. I now have all my append queries set up in the attached database but am a little nervous to run them for fear of adding the whole database instead of just adding the new days records. Do I need to set a filter on these append queries or go to the property sheet and check unique values only?

[jzwp11]

Make a backup copy of the database before you execute each append query.

Do I need to set a filter on these append queries or go to the property sheet and check unique values only?

You will need to make several joins back to your Excel table and the look up type tables (product types, trans types). The company names in both tblCompanies and the linked spreadsheet must be EXACT for this to work. And yes, you will have to select the distinct records from the linked table otherwise you will end up with duplicates.

Additionally, I looked at your table tblCompanyTradeNumbers and it has a bunch of records, but the companyID foreign key field is Null in all records. If you run your append query, you will add new records in addition to the existing ones thus creating duplicates. You will need to delete all records in tblCompanyTradeNumbers before running the corrected qryCompanyTradeNumbers

Just focusing on your append query: qryCompanyTradeNumbers
This is what it should look like:

INSERT INTO tblCompanyTradeNumbers ( TRADE, SpecialName, CompanyID, ProductTypeID, TransTypeID )
SELECT DISTINCT tblLinkedTable.TRADE, tblLinkedTable.NAME, tblCompanies.CompanyID, tblProductTypes.ProductTypeID, tblTransTypes.TransTypeID
FROM ((tblCompanies INNER JOIN tblLinkedTable ON tblCompanies.CompanyName = tblLinkedTable.COUNTERPARTY) INNER JOIN tblTransTypes ON tblLinkedTable.POSITIONTYPE = tblTransTypes.TransType) INNER JOIN tblProductTypes ON tblLinkedTable.PRODUCT = tblProductTypes.ProductType;

[salisbut]

Nice that was super helpful. I now have the append queries set up and I think the database is more or less normalized. Next question: When the database email comes in tomorrow how will I add the new data? Copy and paste from linked table into the append queries? Also, can we now look back at the forms and reports or there more structuring that should be done? I have attached the new database and the updated excel spreadsheet that includes the last couple of days entries.

[jzwp11]

Make sure you check your data. I found a couple of problems. In the transaction detail table, you have the price field as a long number field, I think you want it to be a single or double precision number. Also in that same table, the number of records <> the number of records in your original Excel file which means there is a problem with the append query.

If I use the following append query, I get the correct number of records, but I didn't actually do a verification of the data.

INSERT INTO tblTransactionDetails ( QUANTITY, CurrPrice, TransID, CoTradeNoID )
SELECT DISTINCT tblLinkedTable.QUANTITY, tblLinkedTable.PRICE, tblTransactions.TransID, tblCompanyTradeNumbers.CoTradeNoID
FROM (tblLinkedTable INNER JOIN tblTransactions ON (tblLinkedTable.ENDTIME = tblTransactions.TransEnd) AND (tblLinkedTable.BEGTIME = tblTransactions.TransBegin)) INNER JOIN tblCompanyTradeNumbers ON (tblCompanyTradeNumbers.CompanyID = tblTransactions.CompanyID) AND (tblLinkedTable.TRADE = tblCompanyTradeNumbers.TradeNumber);


Regarding the next steps, when you get your data, does it only include new data versus old and new data? If only new data, import the table into Access and run the append queries (in the correct order, the append query for the trans details should be run last) to get the data into the correct tables then you can get rid of the import table. You will have to be careful in case new companies are added, you can check and add new ones with another query of course (find ones that exist in the excel import table but not in tblcompanies and then append those). Of course, your product and trans type tables will rarely change.

Once you are confident that the data is correct and that the imports are correct then we can look again at the forms/reports.

[salisbut]

Alright thank you those were two good catches. I guess I was hesitant to change the number field to a single or double precision because it did not match up with the data type for the tblLinkedtable Price field but it worked just fine. Second, the excel table I receive contains the new data plus all the data from earlier in the month. For example, June 14 contains June 1st thru June 14th. The catch is that on the first of the month we receive the previous months data plus that first days data. Then the cycle starts over again (On july 2nd we would receive just July 1st and July 2nd). Here is what I think I should do: 1. Copy and paste the new cells into my linked excel table and data will automatically go to access. 2. Run qryCompany 3. Run qryTransactions 4. Run qrytblCompanyTradeNumbers (I don't think order of 3 and 4 matter) 5. Run qryTransactionDetails. Finally, I have attached a copy of the report that my company wants me to generate (Please ignore the scribbled handwriting.) The stuff at the bottom will have to be hardcoded but I was thinking I would create four subreports to put in for the tables (Market Sales, Market Purchases, NET Position and the Market Summary) and my qrySelectWholesale and frmWholeSale would help grab and get the time period of transactions that I need. Is this the right way to think about it?

[jzwp11]

In your linked table, the price was a double precision number, but in your tblTransactionDetail from yesterday it was a long number which would truncate a price of 0.00001 (like you had) to 0. Now, in today's version of your database you have it set as currency which will now truncate the a value of 0.00001 to 0.00. If the 0.00001 is valid then you must change the datatype to either a single or double precision number. Of course, you will have to delete all of the records in the transaction detail table and reappend again.

Second, the excel table I receive contains the new data plus...

If you are already getting the data in an Excel file why are you moving it to another file (the one linked in Access)?

Here is what I think I should do: 1. Copy and paste the new cells into my linked excel table and data will automatically go to access

Will you be getting rid of existing data before you paste the new data?


You cannot run the following append queries again as currently written as they will create duplicates each time you run them.
qryCompanies
qryCompanyTradeNumbers
qryProductTypes
qryTransTypes

Since the values in the corresponding tables are somewhat stagnant, all you need are NEW companies, NEW trade numbers, NEW product types and NEW trans types that come in with the new data. To do this you will need an unmatched query (the query wizard can create it for you). Once you have that, you can convert it to an append query. The product type, trans type and company append queries would have to be run before the trade numbers since the records in that table relate back to those 3 tables.

The queries qryTransactions and qryTransactionDetails should be OK; qryTransactions must be run before qryTransactionDetails since you need the pk of the tblTransaction for the fk of qryTransactionDetails.

Looking at the pdf file I see the GL#'s, I had forgotten about those! If the same GL#'s are associated with a trans type, they should be included in the transtype table!

The stuff at the bottom will have to be hardcoded

I would not hard code anything. Where is the data coming from and can it be included in the DB?

create four subreports to put in for the tables (Market Sales, Market Purchases, NET Position and the Market Summary) and my qrySelectWholesale and frmWholeSale would help grab and get the time period of transactions that I need.

You might be able to combine purchases and sales in 1 subreport, but how would all of these subreports join to the main report? by date range?

[salisbut]

Yeah, converting to currency is just fine because the .00001 are not valid, they are just put in there because EWEB's (the company we get our excel tables from) program does not accept null values so they just put this number in to fill up the record.

If you are already getting the data in an Excel file why are you moving it to another file (the one linked in Access)?
The data I receive in the Excel file only contains up to one months data. Also, I am sent a new excel file everyday, with a different name, and one more days info. Also, when the new month comes, the new file sent out each day has none of the previous months information. So I think we have to move the data to another file. What I currently do is open the new days file and grab the new data and paste it into the master excel sheet.

Will you be getting rid of existing data before you paste the new data?
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.

I have built the new append queries in the attached database and have deleted the TransType append query and the ProductType append query because I am sure that there is no other types that will ever be entered into these two tables.

I agree that those should not be hardcoded, as soon as I can talk to management I will get this data.

You might be able to combine purchases and sales in 1 subreport, but how would all of these subreports join to the main report? by date range?

Yes this is what I was hoping is that it could be setup similar to our previous report in that you simply type in a date range and it gives us a report. The attached form was from April so it was a monthly report.

Two quick questions: 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? Also, 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?