I want to make sure that I am going in the right direction on a few reports that I'm going to have to develop for my db.
The end-users requested to be able to see like "trends" for specific data. So after some research, I've figured that Pivot Queries and Pivot Tables would be the best methods for this.
For example: the end-user would like to see how many people come through a specific Point-of-Entry. The conditions that I'm going to have on the form are:
1. User selects the Point-of-Entry
2. User can select specific time intervals (30, 60, 90, 120, or 180 days) via a combo box
3. User can select a specific date using entries via a PopUp-Calendar form
4. If the user does NOT select a date interval or selected dates, then it will DEFAULT to 1 year prior to the current date for the selected Point-of-Entry.
When the user selects the "Show Results" command button. A Pivot Table / Graph will be displayed in a subform which the user will then need to be able to have the ability to PRINT.
Do you think I'm going in the right direction for this? Would it be easier to do something else? If so, what?
Aside from the suitability of using a pivot vs a chart vs a standard report, it sounds like you are heading in the right direction
Personally, I'd go with a chart for trends, I don't like pivot objects, they tend to confuse my users
Now I've gotta figure out what and how I have to write these select queries to get what I need from them for the charts.
Am I wrong by saying that the ones I need for like Country, Region, Point-of-Entry, Visa Type will need to be a Select -> Case statement since I have to have the user first pick the specific above from a combobox, then they can either pick a predetermined set of days, select specific date intervals, or default to 1-year prior to the current date?
I'm also developing the form for that now. I've already put the combobox for the "Country" on it and that pulls up the country list, no problem. Would it be easier to make another table and put in the 30, 60, 90, 120, or 180 days and allow the user to select like that OR put a textbox and have the user ONLY be able to enter in those numbers?
Alright, I've been doing some research and thinking and this is what I've come up with so far...
SELECT DISTINCT tblCountry.CountryID, tblCountryNM, tblPassport.PassportID, tblPassport.IndID FROM tblCountry WHERE cmbCountry = "" AND CASE 1 cmbDays = "" OR CASE 2 txtStartDate = "" and txtEndDate = "" ELSE ((=NOW()-365))
Does that make any sense? **It does in my head!!**
I'll leave that to the SQL gurus around here. I tend to use the Query Builder more than anything.
However, the latter part of that SQL doesn't make sense in my head -- not sure what you're trying to do with the CASE thing.
I'd make a Value List type combo box... depending on what/how you are going to use this in your report/chart.
It's like I said, The idea is in my head and it sounds good there, although many things sound good in my head, so I'm just having the user select a country / region, then the number of days (either pre-determined, between specifically chosen dates, or default to 365 if the previous two aren't chosen), then it needs to return the results for like Vertical Axis= #of days , Horizontal Axis = # of people.