Search Form

[Guest]

I have been reading previous posts and have not been able to figure this one
out by myself. Here is the basics of what I have so far:
Tables:
1. Invoice_Entry with fields --> Country, InvoiceNumber, InvoiceDate,
InvoiceAmtLC
2. Reference with fields --> Country, Region, Currency, Rate
3. Period with fields --> Date, Period

Queries:
1. Q_main --> Region, Country, InvoiceNumber, InvoiceDate, Period,
InvoiceAmtUS (this last one is a calculated field [InvoiceAmtLC]/[Rate])

Forms:
1. F_IE --> Country, InvoiceNumber, InvoiceDate, InvoiceAmtLC --> This form
updates the Invoice_Entry table.

Here is what I need:
I'd like to have a form where the user (my boss) could pull revenue totals
by Region-Period having the Region and Period fields as combo boxes and based
on the values selected the corresponding revenue amount for that region in
that period would display on a third textbox.

Thanks for any help on this.

Sebastian

 

[Guest]

Maybe I can make my request a bit simpler.

I have made a query:
Q_REVBYREGANDPER --> This query provides me the totals by Region/Period e.g

Region Period InvoiceAmtUS
CLA 01-2005 1,000.00
EMEA 01-2005 1,500.00

And so on.

I have a Search form with three (3) fields on it.
1. ComboBox "Region" --> Row Source is a the Region column from the above
Query (Q_REVBYREGANDPER)
2. ComboBox "Period" --> Row Source is the Period column from the above
Query (Q_REVBYREGANDPER)
3. TextBox "Total Revenue" --> This is the one that I'd like to update
based on selections from the fields 1 & 2.

Thanks again.
Sebastian

 

[Vincent Johns]

This isn't exactly an answer to your question, but are you trying to use
the two Combo Boxes as sources of information for filtering the output
from your Query? You can do that by setting the criterion in each of
the 2 fields to be that the value is equal to the Combo Box value.

But I'm not sure you need the Form at all (depends on what you're trying
to do, of course). Have you considered setting up a Pivot Table
(Crosstab Query) that lists the regions on the left (one row per region)
and lists the time periods along the top (one period per column)? You
could display the maximum, or the average, or the total invoice amount
for each region & period in the cells in the body of the Pivot Table and
not have to fiddle with the Form at all. You'd probably want to filter
your [Q_REVBYREGANDPER] Query to limit the number of rows & columns to
what will fit conveniently on a page, but the result might be easier for
your boss to use than a Form that has to be accessed separately for
every single value.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Maybe I can make my request a bit simpler.

I have made a query:
Q_REVBYREGANDPER --> This query provides me the totals by Region/Period e.g

Region Period InvoiceAmtUS
CLA 01-2005 1,000.00
EMEA 01-2005 1,500.00

And so on.

I have a Search form with three (3) fields on it.
1. ComboBox "Region" --> Row Source is a the Region column from the above
Query (Q_REVBYREGANDPER)
2. ComboBox "Period" --> Row Source is the Period column from the above
Query (Q_REVBYREGANDPER)
3. TextBox "Total Revenue" --> This is the one that I'd like to update
based on selections from the fields 1 & 2.

Thanks again.
Sebastian

:

I have been reading previous posts and have not been able to figure this one
out by myself. Here is the basics of what I have so far:
Tables:
1. Invoice_Entry with fields --> Country, InvoiceNumber, InvoiceDate,
InvoiceAmtLC
2. Reference with fields --> Country, Region, Currency, Rate
3. Period with fields --> Date, Period

Queries:
1. Q_main --> Region, Country, InvoiceNumber, InvoiceDate, Period,
InvoiceAmtUS (this last one is a calculated field [InvoiceAmtLC]/[Rate])

Forms:
1. F_IE --> Country, InvoiceNumber, InvoiceDate, InvoiceAmtLC --> This form
updates the Invoice_Entry table.

Here is what I need:
I'd like to have a form where the user (my boss) could pull revenue totals
by Region-Period having the Region and Period fields as combo boxes and based
on the values selected the corresponding revenue amount for that region in
that period would display on a third textbox.

Thanks for any help on this.

Sebastian

 

[Guest]

Vincent,

Thanks for your help and suggestions. I tried the crosstab query and it
displays the data in a way that would be quite easy to understand.

Sebastian

This isn't exactly an answer to your question, but are you trying to use
the two Combo Boxes as sources of information for filtering the output
from your Query? You can do that by setting the criterion in each of
the 2 fields to be that the value is equal to the Combo Box value.

But I'm not sure you need the Form at all (depends on what you're trying
to do, of course). Have you considered setting up a Pivot Table
(Crosstab Query) that lists the regions on the left (one row per region)
and lists the time periods along the top (one period per column)? You
could display the maximum, or the average, or the total invoice amount
for each region & period in the cells in the body of the Pivot Table and
not have to fiddle with the Form at all. You'd probably want to filter
your [Q_REVBYREGANDPER] Query to limit the number of rows & columns to
what will fit conveniently on a page, but the result might be easier for
your boss to use than a Form that has to be accessed separately for
every single value.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Maybe I can make my request a bit simpler.

I have made a query:
Q_REVBYREGANDPER --> This query provides me the totals by Region/Period e.g

Region Period InvoiceAmtUS
CLA 01-2005 1,000.00
EMEA 01-2005 1,500.00

And so on.

I have a Search form with three (3) fields on it.
1. ComboBox "Region" --> Row Source is a the Region column from the above
Query (Q_REVBYREGANDPER)
2. ComboBox "Period" --> Row Source is the Period column from the above
Query (Q_REVBYREGANDPER)
3. TextBox "Total Revenue" --> This is the one that I'd like to update
based on selections from the fields 1 & 2.

Thanks again.
Sebastian

:

I have been reading previous posts and have not been able to figure this one
out by myself. Here is the basics of what I have so far:
Tables:
1. Invoice_Entry with fields --> Country, InvoiceNumber, InvoiceDate,
InvoiceAmtLC
2. Reference with fields --> Country, Region, Currency, Rate
3. Period with fields --> Date, Period

Queries:
1. Q_main --> Region, Country, InvoiceNumber, InvoiceDate, Period,
InvoiceAmtUS (this last one is a calculated field [InvoiceAmtLC]/[Rate])

Forms:
1. F_IE --> Country, InvoiceNumber, InvoiceDate, InvoiceAmtLC --> This form
updates the Invoice_Entry table.

Here is what I need:
I'd like to have a form where the user (my boss) could pull revenue totals
by Region-Period having the Region and Period fields as combo boxes and based
on the values selected the corresponding revenue amount for that region in
that period would display on a third textbox.

Thanks for any help on this.

Sebastian