Hi,

I have been assigned to "improve" an Access database that has been created by a user. However, i do not know where to start. One of the things I have to do is write a stored procedure so that when the user runs the queries, the user is prompted to enter the criteria (for instance, a data range) to narrow down the number of records retrieved from the SQL backend. I have been told to improve the database. However, I do not have any experience in this and don't know how to start. The database has been created by a user so there's obviously lots of room for improvement. For instance, there are a million queries and I don't think that many are really needed. The goal is to minimize the size of the database. Can someone please guide me as to how I should go about starting to improve this database?

Thanks.

Well, if it were me, I'd start by making sure the tables were normalized properly. However, since you have an SQL back end, it seems like maybe its been around a while and was developed by experienced people. So, assuming that's the case, or that you can't do anything about the table structures, I'd dig into the queries that are there and see what you need and what you don't.

For the date thing, it sounds like you need to make the queries into "parameter queries" into which the desired dates can be entered to limit the results. You can make set it up to prompt the user for the dates, or you can make a form from which the query will draw the dates. I'm sure you'll find lots of info if you search the forum on "parameter query" or the like.

I use the form approach, personally. I create a form with two unbound text box controls with Default Values from DMin and DMax to populate the boxes with the earliest and latest dates from the table as starting places. Then the user can make whatever changes they want and click a button to run the query. I made you an example of this - see the attached.

Good Luck!