Maybe a simple Query question, not sure

[Lynxador]

im working on a very simple way to create sales reports, that is created when you enter what city you want the report for. The problem that im having is that if i hand the database to them as is, im sure their heads would explode. they aren't the brightest when it comes to computers.

instead of them having to constantly change the Search criteria in the City section, is there anything i can do form wise that will ask them what city they want to look for, and it will change the search criteria for them?

Thanks,
Lynx

 

[Andrew Fergus]

Hi Lynx

I recommend you create a new form with 3 elements - the first being a combo box that looks up the cities (from a table of cities that you should already have in your database, if not then you might need one). You can use the wizard to help you set that up. The second being an "ok" button that opens the report and the third being a cancel button that closes the form - again the wizards should be sufficiently helpful.

In the query that you have based the report on (I think you already have a query given you mentioned 'criteria'), change the city criteria to something like this :
=[Forms]![Your_Form_Name]![Your_Combo_Box_Name]

Be sure to use your actual form and combo box name. You can set the name of the combo box, when in the form design, by setting the 'name' property to something relevant. This query will now look to the form for the criteria and when the report opens, it will only show the city that has been selected.

If the users are not the smartest, you might want to have this form pop up whenever they open the database, you can force that to happen, under Tools- > Startup and select your form under the 'Display Form' heading. Lastly, instead of the cancel button closing the form you might want to change it so that it exits Access to prevent your users inadvertently messing up the database.

HTH, Andrew.

P.S. there are some useful self-help resources available here and here.

 

[Lynxador]

Thanks Andrew that helped alot. but now i've run into another problem. befor i put your idea to use, i Had 2 tables. 1 with customer information wich is [CustomerID], [CustomerName], [City], [Province], [PostalCode], [PhoneNumber]. and the second table wich is [CustomerID], [Sales2003], [Sales2004], [Sales2005]. I have a one-to-many relationship Between CustomerID from table2 to table1. Pretty simple setup. now here is the problem:

i create a query to get all sales data for a customer, and for the most part it works. for customers that have boughten from us in the past 3 years. but, i ahve a 5.5k customer database, and when i run this query only 3.8k show up. i know the problem is because on my Tables with the Sales, some customers aren't there because the fact that they havn't boughten from us in the past 3 years. but for the report im making, its essential that they are there, Sales info or not. Preferably, id like it that if Table1 can't find its matching sales data in table 2, to put 0 in the 2003, 2004, 2005 field. but im lost as what to do :-/

On an off note, im more of an Excel user, and when i was making these reports by hand i built a macro that would do just this. Search sheet 2 for related data, import it to sheet 1, and if it doesn't find anything than put a 0 in the field and move on to the next customer. is there a way to incorporate this code into access?

 

[Andrew Fergus]

Hi Lynx
If you want to include the customers that do not have any sales, then in your query design, double click on the link between the two tables (in the top half of the screen, double click the line, a screen should pop up that says something like 'join properties') and click the circle beside the option which say something like 'include all records from Tbl_Customers and only matching records from tbl_Sales'.
HTH, Andrew.

 

[Lynxador]

Bah, I love you andrew. see, i had 'include all records from Tbl_Customers and only matching records from tbl_Sales' selected already. but after you brought it up, i went in to check again and found my problem. was running my query off a bad table that i forgot to delete. so now everything works. thank you much :D