Search results

Thank you very much for your help! btw, I forgot to say that I didn’t know about the space and speed effects of composite keys; good to know!

Thank you, I think I understand you now. To be excruciatingly redundant, you suggest: Keep the InvestmentInstance keys the same as in the original screenshot and the no-duplicates property on “Id” as in the screenshot, too. In MarketValue, make “Id” the (only) primary key. Relate the two tables...

I’m sorry, but do you mean to keep the same composite key in InvestmentInstance (“AccountId” and “FundId”) and leave “Id” a non-primary key, add “Id” to the composite key, or something different? Can I un-key “AccountId” and “FundId” but still have them be the unique identifiers of a record and...

Hello, Does anyone know of a way to link to a file (a Word “object”) but make it so the path to the file is a relative path instead of an absolute path? I know there is a way to change the base for a hyperlink path, but have not been able to find any information about a similar thing for...

I agree that using only ID fields as the primary keys would be easier to set-up, but wouldn’t that allow unwanted duplicate records in some cases? For example, if “Id” was the primary key in InvestmentInstance, and “AccountId” and “FundId” were no longer part of the key, then it would be...

Hello, I have attached a screenshot of the relationships in (a section of) a database I am working on. Would anyone please be willing to view it and offer any suggestions as to whether is fully normalized or not? Here is the description: An account [table, Account] consists of some money...

I’m no expert, but I'll give it a shot: Your Person table probably has stuff like a name and address—does it also have an “ID” field? This field would be of autonumber data type and also the primary key of the table. In your DrawingNumber-type table, you then need to add a field that will...

I found a good page to check reserved words for several different database types: What are reserved Access, ODBC and SQL Server keywords? (I try to check it if I think of it in case I ever need to move out of Access.) About the fundprice table (using your names), I like the idea of keeping an...

Thank you for all your input! I chose the short Id name for the primary key fields to avoid redundancy within their own table, but have renamed them like FundId when they are used as foreign keys. I did that with the consideration that any SQL I need to write would be cleaner, like Fund.Id...

Hi everyone, I have/had a situation very similar to another recent thread, “Table Setup Help...”, but I read the thread and had a go at normalizing my analogous table. My old table, Fund, looked like this: ________________ |Fund | |================| |* Id | |...

For anyone who is interested, I am going to try to use dollar values/prices instead of percentage values. It seems that percentages are usually the reported in end-of-period reports, but running performance is given in a price based on a hypothetical value invested since the fund started; that...

Hello, Is it possible to calculate the compound annual growth rate (CAGR) for a period with only the returns by percentage instead of the actual values? As an example, I have attached a workbook with quarterly returns for a five-year period. The way I used to calculate the CAGR was to create...

I think I am going to go with the method described in my last post/attachment. This should be pretty good since the products chosen for the display sheet won't change very often. It also avoids the problem of having to pre-format the query/data (sorted by product category) so that the dynamic...

Oh, sorry for not being more specific: The "Display (revised)" worksheet/tab is more-or-less how I would like the data displayed. I have included the ProductCategoryID and ProductID numbers in columns A and B; I don't want them to show, so those two columns can be hidden. The ID numbers...

Your workbook really helped me a lot; it was so much easier to understand with an example. Thank you! I added ID number columns on the Data worksheet to make it more database-like and used those as look-up values instead of values concatenated from names. (I made dynamic named ranges for them.)...

Wow, thank you very much! I will have a look at this and get back to you as soon as I can.

Hmm, I see how the COUNTA counts the number of used rows or columns, but what about a range/table that looks like the top query in my first post? That is, to have the dynamic range not depend on whether the cell (row) has content or not, but depend on what the content actually is. To make a...

Thank you for the welcome and quick reply. Making a sheet with just the plain query is a good idea—I didn't think of that. I wanted to avoid having a worksheet that is only source data like that, but maybe that is the only way. I haven't used dynamic named ranges before, but I looked into it...

Hello, My question is about using data from an Access database and using it in Excel. I would like to link to a single query, but be able to separate this data in Excel while keeping the data link. For example, a query that selects all products associated with a single customer: CustomerID...