Hello everyone,

I'm trying to develop a query to allow users to do something (obviously). This is what I'm trying to do...

On a form (frmSelectCtry), there are a few controls.
1. Select Country combobox populated by tblCountry
2. Select Specific Days combobox populated by tblDays
3. Select a Start Date and End Date in textboxes respectively. These when double-clicked open a calendar form where the user can select specific start and end dates.

What I'm trying to do is have the user select a country, then
CASE1 - Select a predetermined number of days PRIOR to current date
OR CASE2 - Select specific dates using the textboxes PRIOR to current date
OR CASE3 - default for a YEAR back from prior date.

When the user selects their criteria, they'd click on a command button, cmdGetResults

The results of the query would be displayed in a PIVOT CHART / Bar graph so the user can see how many people came into the U.S. from the specific chosen country.

The way the tables are is that tblCountry is in a tblPassport which also has an IndID in it relating back to the actual Individual or person who is or did enter the U.S.

Anyone have any ideas on how I can write this query? AND show the results in a Pivot table / bar graph?

I hope I explained it fairly well but let me know if you need more details...

Can you post the tables with columns and their relationships with a couple rows of raw data? Then how you want the data to look after a successful query? The date criteria are pretty straight forward. Do you have a handle on that part?


mmcdonal

Look it up at: http://wrox.books24x7.com

I don't know if it'd be able to handle the tables. I can email the db to you as when I zip / rar it the size goes from 15+mb down to like 5mb. I'll try to get it up here if I stop getting errors when I try a full-reply.

Main Table: tblIndividual -
IndID,FirstNM, MiddleNM, LastNM, DOB, GenderID, EthnicityID, Height, Weight, HairCLR, EyeCLR, ARNB, NatNB, FileNB, Alias, Add_Info, Picture, Entered_By, Date_Entered

Combo Box 1: tblCountry -
CountryID, CountryNM, Country_Abbrev, ContinentNM, Cont_Abbrev

Combo Box 2: tblDays -
DaysID, Days

tblPassport: PassportID, IndID, PassportNB, CountryID, Date_Issued, Date_Expired

tblIndividual is on a 1:Many relationship with tblPassport on the IndID field.
tblCountry is on a Many:1 relationship with tblCountry on the CountryID field.

The data, after the query is run would have to be like:
X number of people from Country X during time (Choice A - selected days from tblDays) OR (Choice B - Selected date interval from two text boxes)OR [if no other choice is selected] (Choice C -Default 365 days prior)

I have no idea on how to write this query at all, let alone how to get the information to be displayed in a Pivot Chart (Bar Graph).

I'm trying to figure this out as I'll need to reuse the same query (with edits) to display similar information on similar requests.