need help with my project..please

[saraQ]

Hi,
I have a project about creating a data base of Foreign Trade, where I have to choose the country, then to determine the period (years).
so, could you please tell me how:
1- to link a combo box with a query (comob box of list of countries)
2- to link 2 combo boxes with a query of starting date and ending date
(the criteria is: >=[start date]and<=[end date] ).....should I separate this criteria and put it in to 2 columns of same data (years)...I tried it but it didn't work.
3- to link a Run Query command button with conditions mentiond above. When I established the command button, it showed the whole data base, so how can I link it with 2 conditions above.
Waiting your appreciated replies a.s.a.p.

Thanks,

 

[saraQ]

Still waiting for a wonderful person..

 

[CEH]

Sara,
Little hard to tell what your end goal is here.... But I'll hit on a couple of points.. One, use the wizard to do your combo box for your "Countries" I assume you have something like "CountryID" and "CountryName" as fields.
As for your dates I'm not sure if your trying to filter a form or if this is to produce a report showing your givin criteria....... Usually on your "Date" field you would have in the criteria....(on one field.. A "Between...And" something like.... Between [Forms]![frmReportCriteria]![txtStartDate] And [Forms]![frmReportCriteria]![txtEndDate]
This would pull your Start date from an unbound textbox named "txtStartDate" off of a form called "frmReportCriteria" like wise with you end date.
Maybe this will point you in the right direction..........

 

[saraQ]

Curtis,
Thanks for your reply, I'm very grateful to you. I feel that I've put myself in the right direction after reading your valuable reply.

use the wizard to do your combo box for your "Countries" I assume you have something like "CountryID" and "CountryName" as fields.

I used the wizard. I have one table that containes the following:
- ID: autonumber
- Country Name: text
- Year: number
- Exports: NO.
- Re-exports: NO.
- Imports : NO.
All of these in one table, then I built a function in Query to generate 2 things:
- Balance Trade
- Total Trade Exchange

I'm not sure if your trying to filter a form or if this is to produce a report showing your givin criteria

Yes, I'm trying to produce a report. I can see that you recommend me to use Report instead of Form, and to do this I have to create 3 things on the Report (which would be the user interface) as the following:

Report​

Country : (combo box)


From : (combo box)


To : (combo box)

Run : (command button)

then, I'll have the following table:



Country Name:
Year 2003 2004 2005
Exports
Re-exports
Imports
Balance Trade
Total Trade Exchange

I just want to give you a clear idea bout my DB, and I'll try to do what you told me and see the results, then I'll tell you.
Many thanks for that.
Cheers,

 

[saraQ]

Curtis,
I attached my DB for you to get what I want to reach.
It contains more than 130 country for the last 3 years, therefore the country will appears 3 times in the combo box which is located in the Form, and also the year. please chech the attached file then tell me what I have to do.
Thanks a lot.
Sara

 

[Luddite Lad]

For starters I'd split the country and years into their own tables so each value will only appear once in your combo box(s), and in your main table store only the Country ID and Year ID.

 

[saraQ]

Thanks for that, I'll try to do it. But my main problem is how to link the combo boxes and the command button, which are located in the Form, with the query to produce a report?

 

[CEH]

Sara,
Have a look here....
I also would agree with Luddite...... 3 tables, set up your relationships,then create your forms. If you get your "normalization" correct at the beginning everything else falls into place
I didn't play with your relationship setup, but if I were you... I'd try doing it again.

 

[saraQ]

Curtis,
Do you mean that I have to make tables for:
1- countries
2- years
3-vlaues(exports, re-exports, imports)
OK, I'm not professional in Access, I've just start working on it from month ago. I did'nt think that establishing a DB will take long time.
OK, my question is:
How can make an ID number for each of the 2 variables (countries & years)?
Should I memorize that each country (out of more than 130) has the ID of #? I think there is a way to do that without memorizing. Could anyone tell me please.
Curtis and Luddite, thanks for your kind cooperation.

 

[saraQ]

Sara,
Have a look here....
I also would agree with Luddite...... 3 tables, set up your relationships,then create your forms. If you get your "normalization" correct at the beginning everything else falls into place
I didn't play with your relationship setup, but if I were you... I'd try doing it again.

Curtis,
You are amazing, that's really what I wanted to reach from month ago...!
you did exactly what I want. My boss will be happy, because he got confused with the previous one where he was forced to write the country's name, and hence results in having mistakes in spelling the country's name sometimes.
I'll make some changes then tell you the great results.
Thanks for that, and I wish to help you one day.