Search results

I have a series of nested parameter queries that calculate a value. I need to store this value in a table. And yes, I know storing a calculated value is poor practice but in this situation it is necessary. Usually, I would use an update query to move the value from the query to the table...

Thanks Jon K. That works well and I was not aware of the Top function before you called my attention to it.

Hello: This should be simple but I couldn't find a similar thread. I have a table that contains values by date. Using a parameter query I want the user to enter a date and the database will pull the value from 2 years ago. I am using this as a criteria DateAdd("yyyy",-2,[DateSelect]) in the...

Hello: The database I am constructing will frequently need to import data from Excel. I built one central import form that allows the user to 1) select the Excel file they want to import (using the common dialog) 2) select the destination table in Access and 3) then import the data. The...

Thank you. It was the Is Null that I was missing. I was using """" which caused an error.

Hello: I need a query to find the total (sum of) active client reports on a specified date – StartDate. My table has a ReportOpen date field and a ReportClose date field. I plan to use a parameter query that uses criteria supplied by a data entry form to allow the user to enter a date range...

My suggestion is that you spend your energy normalizing the data in the tables so that you do not have to resort to complicated queries.

I'm not an expert and am just giving you my advice because no one else did. In my opinion Excel is good if you are doing something that is a one off. It is easy to set up a workbook to get quick results and you don't have to do a lot of pre-plannning. A database gives you a lot more control...

Why don't you have help files? Data should be entered into a table by a form. You can then control what data goes into the table. It is possible to do what you want. The cascading combo boxes suggested will go on this form.

I suggest you use the Access help files to learn more about Access tables and how to use them. Then I think you will see that your question is misguided. Hope this helps. Dwight

I don't have an answer but a bit of advice. Just use an autonumber for your primary key field. This field should not have any meaning other than being unique. If you still want the 4 digit code then use a seperate field. My two cents. Dwigth

Do you have to keep the old address? Can't you have your user update the old address to the new rather than having both? If not maybe you could use a checkmark to denote the current address. This is a yes/no value. So if the person moved 3 times only 1 address would have the current address...

Referential Integrity (RI) helps prevent orphaned records. For example, it won’t allow you to delete the one side of a one to many relationship. The textbook example is if you have a Customer table and then a separate CustomerOrders table. If no RI is set then I can delete a Customer which...

I hope you find a solution to your problem. It is my understanding that referential integrity is a core ingredient to a solid database design. You should take the time to set it up. Good luck. Dwight

Hello, My VBA questions are always ridicoulsy simple. Add this one to the list: I have a main form/subform setup for transaction entry. The main form has some unbound controls that allow the user to enter general information once so they don't have to repeat it below. Then they go into the...

I am not an expert but I would take a look at your referential integrity settings. For example, if you have cascade deletes set then if you delete a record in the primary table then all related records in the linked tables will also be deleted. Dwight

I think I got this licked. I just used multiple nested subforms and made sure the linking names were proper as was suggested. Sometimes you just have to start from scratch and draw it out on paper. Thanks for the help. Dwight

I’ve made myself dizzy with this so I thought I would lay it out from the beginning and see if someone can help. IIkhoutx’s advice has been good (and appreciated) but my VBA skills are currently too poor to implement his technique. Here’s what I have: tblAccountID AccountID field, autonumber...

Look up parameter query in Help. Basically you create the query but the user can set criteria e.g. date range on a form. Hope this helps. Dwight

Thanks for the advice. It doesn’t surprise me that I’ve botched something. The idea is to have a main form with 3 level hierarchy (Account > Portfolio > SubPortfolio). These are one to many relationships i.e. 1 account can have many portfolios and 1 portfolio can have many SubPortfolios. Each...