A query based on form action

[Olden]

My current database is made up of mostly 1 table and one 1 main query.
Here's an example of the main query:

foo: [table1]![field1]+[table1]![field2]+[and so forth]
bam: [table1]![field10]+[table1]![field12]+[and so forth]
bar: [table1]![field20]+[table1]![field22]+[and so forth]

I have a form with 3 Checkboxes (fooCHECKBOX,bamCHECKBOX,barCHECKBOX)
that writes Yes/No values to TableOfYesAndNo.

What I want is a query that does something along the line of:

If [TableOfYesAndNo]![fooCHECKBOXvalue]=Yes, Then have my query include
foo: [table1]![field1]+[table1]![field2]+[and so forth] if not just
discard foo from this query all together

If [TableOfYesAndNo]![bamCHECKBOXvalue]=Yes, Then have my query include
bam: [table1]![field10]+[table1]![field12]+[and so forth] if not just
discard bam from this query all together

If [TableOfYesAndNo]![barCHECKBOXvalue]=Yes, Then have my query include
bar: [table1]![field20]+[table1]![field22]+[and so forth] if not just
discard bar from this query all together

Hopefully you've done something similar and can help.

Thanks in advance,

Olden.

 

[Jeff Boyce]

Olden

When I see multiple fields being added together, I begin to wonder if the
data structure is optimized for Access. It is common for multiple
(repeating) fields to show up in a spreadsheet (this is about the only way
to do it), but is is counterproductive to keep this kind of structure in an
Access table. While you can do it (as your description points out), both
you and Access have to work much harder, and you don't get to use many of
the features/functions Access provides.

If you'll describe a bit more about what kinds of data you are storing in
"field1", "field2", ... "field10", ..., the folks here in the newsgroup may
be able to offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Olden]

Thanks for the interest in my situation.

The reason I need such a query is that my table contains monthly sales
each month is comprised of weeks, but the thing is that I have an
offset between my calendar weeks and fiscal weeks. (Some weeks in march
can represent february weeks)

So to revisit the example in my first post:

foo: [table1]![field1]+[table1]![field2]+[and so forth]

You can translate that to:

February: [MainTable]![Week1Sales]+[MainTable]![Week2Sales]+[and so
forth]

This is my initial dataset: 1 table with 52 columns and about 6000
records which are SKUs.

What I want is users to build their own query with checkboxes (or any
other control for that matter), 12 of them when checked that month gets
included in the query, kind of like when you drag field from the table
window in to your query.

How do I optimize, and what should I explore from here?

Thanks in advance,

Olden

Olden

When I see multiple fields being added together, I begin to wonder if the
data structure is optimized for Access. It is common for multiple
(repeating) fields to show up in a spreadsheet (this is about the only way
to do it), but is is counterproductive to keep this kind of structure in an
Access table. While you can do it (as your description points out), both
you and Access have to work much harder, and you don't get to use many of
the features/functions Access provides.

If you'll describe a bit more about what kinds of data you are storing in
"field1", "field2", ... "field10", ..., the folks here in the newsgroup may
be able to offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My current database is made up of mostly 1 table and one 1 main query.
Here's an example of the main query:

foo: [table1]![field1]+[table1]![field2]+[and so forth]
bam: [table1]![field10]+[table1]![field12]+[and so forth]
bar: [table1]![field20]+[table1]![field22]+[and so forth]

I have a form with 3 Checkboxes (fooCHECKBOX,bamCHECKBOX,barCHECKBOX) that
writes Yes/No values to TableOfYesAndNo.

What I want is a query that does something along the line of:

If [TableOfYesAndNo]![fooCHECKBOXvalue]=Yes, Then have my query include
foo: [table1]![field1]+[table1]![field2]+[and so forth] if not just
discard foo from this query all together

If [TableOfYesAndNo]![bamCHECKBOXvalue]=Yes, Then have my query include
bam: [table1]![field10]+[table1]![field12]+[and so forth] if not just
discard bam from this query all together

If [TableOfYesAndNo]![barCHECKBOXvalue]=Yes, Then have my query include
bar: [table1]![field20]+[table1]![field22]+[and so forth] if not just
discard bar from this query all together

Hopefully you've done something similar and can help.

Thanks in advance,

Olden.

 

[Jeff Boyce]

Olden

Instead of going "wide", think "deep". If your table structure was
something like:

tblWeeklySales
SKU
WeekEndingDate
SalesAmt

your query would be a Totals query, GroupBy SKU, GroupBy WeekEndingDate, Sum
SalesAmt.

Note that this approach lets you go on for years without having to add
columns. And you can use this to pull out a total SalesAmt for any
particular date range, and for any specified SKUs.

Regards

Jeff Boyce
Microsoft Office/Access MVP>

Thanks for the interest in my situation.

The reason I need such a query is that my table contains monthly sales
each month is comprised of weeks, but the thing is that I have an
offset between my calendar weeks and fiscal weeks. (Some weeks in march
can represent february weeks)

So to revisit the example in my first post:

foo: [table1]![field1]+[table1]![field2]+[and so forth]

You can translate that to:

February: [MainTable]![Week1Sales]+[MainTable]![Week2Sales]+[and so
forth]

This is my initial dataset: 1 table with 52 columns and about 6000
records which are SKUs.

What I want is users to build their own query with checkboxes (or any
other control for that matter), 12 of them when checked that month gets
included in the query, kind of like when you drag field from the table
window in to your query.

How do I optimize, and what should I explore from here?

Thanks in advance,

Olden

Olden

When I see multiple fields being added together, I begin to wonder if the
data structure is optimized for Access. It is common for multiple
(repeating) fields to show up in a spreadsheet (this is about the only
way
to do it), but is is counterproductive to keep this kind of structure in
an
Access table. While you can do it (as your description points out), both
you and Access have to work much harder, and you don't get to use many of
the features/functions Access provides.

If you'll describe a bit more about what kinds of data you are storing in
"field1", "field2", ... "field10", ..., the folks here in the newsgroup
may
be able to offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My current database is made up of mostly 1 table and one 1 main query.
Here's an example of the main query:

foo: [table1]![field1]+[table1]![field2]+[and so forth]
bam: [table1]![field10]+[table1]![field12]+[and so forth]
bar: [table1]![field20]+[table1]![field22]+[and so forth]

I have a form with 3 Checkboxes (fooCHECKBOX,bamCHECKBOX,barCHECKBOX)
that
writes Yes/No values to TableOfYesAndNo.

What I want is a query that does something along the line of:

If [TableOfYesAndNo]![fooCHECKBOXvalue]=Yes, Then have my query include
foo: [table1]![field1]+[table1]![field2]+[and so forth] if not just
discard foo from this query all together

If [TableOfYesAndNo]![bamCHECKBOXvalue]=Yes, Then have my query include
bam: [table1]![field10]+[table1]![field12]+[and so forth] if not just
discard bam from this query all together

If [TableOfYesAndNo]![barCHECKBOXvalue]=Yes, Then have my query include
bar: [table1]![field20]+[table1]![field22]+[and so forth] if not just
discard bar from this query all together

Hopefully you've done something similar and can help.

Thanks in advance,

Olden.

 

[Olden]

Let me recap to make sure I get what is suggested.

I would go from

+++week1,week2,week3,etc.
SKU1$$$$$,$$$$$,$$$$$$,$$$
SKU2$$$$$,$$$$$,$$$$$$,$$$

To:


SKU,WeekNumber,Amount
45d,Week1 ,500
45d,Week2 ,900
45d,Week3 ,1000

The question would then become what's the quickest way to turn 52
columns into 6000 records with the appropriate amounts, grab the right
week# from the name of a column in a table and apply it to the records?

Thanks in advance,

Olden

Olden

Instead of going "wide", think "deep". If your table structure was
something like:

tblWeeklySales
SKU
WeekEndingDate
SalesAmt

your query would be a Totals query, GroupBy SKU, GroupBy WeekEndingDate, Sum
SalesAmt.

Note that this approach lets you go on for years without having to add
columns. And you can use this to pull out a total SalesAmt for any
particular date range, and for any specified SKUs.

Regards

Jeff Boyce
Microsoft Office/Access MVP>

Thanks for the interest in my situation.

The reason I need such a query is that my table contains monthly sales
each month is comprised of weeks, but the thing is that I have an
offset between my calendar weeks and fiscal weeks. (Some weeks in march
can represent february weeks)

So to revisit the example in my first post:

foo: [table1]![field1]+[table1]![field2]+[and so forth]

You can translate that to:

February: [MainTable]![Week1Sales]+[MainTable]![Week2Sales]+[and so
forth]

This is my initial dataset: 1 table with 52 columns and about 6000
records which are SKUs.

What I want is users to build their own query with checkboxes (or any
other control for that matter), 12 of them when checked that month gets
included in the query, kind of like when you drag field from the table
window in to your query.

How do I optimize, and what should I explore from here?

Thanks in advance,

Olden

Olden

When I see multiple fields being added together, I begin to wonder if the
data structure is optimized for Access. It is common for multiple
(repeating) fields to show up in a spreadsheet (this is about the only
way
to do it), but is is counterproductive to keep this kind of structure in
an
Access table. While you can do it (as your description points out), both
you and Access have to work much harder, and you don't get to use many of
the features/functions Access provides.

If you'll describe a bit more about what kinds of data you are storing in
"field1", "field2", ... "field10", ..., the folks here in the newsgroup
may
be able to offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My current database is made up of mostly 1 table and one 1 main query.
Here's an example of the main query:

foo: [table1]![field1]+[table1]![field2]+[and so forth]
bam: [table1]![field10]+[table1]![field12]+[and so forth]
bar: [table1]![field20]+[table1]![field22]+[and so forth]

I have a form with 3 Checkboxes (fooCHECKBOX,bamCHECKBOX,barCHECKBOX)
that
writes Yes/No values to TableOfYesAndNo.

What I want is a query that does something along the line of:

If [TableOfYesAndNo]![fooCHECKBOXvalue]=Yes, Then have my query include
foo: [table1]![field1]+[table1]![field2]+[and so forth] if not just
discard foo from this query all together

If [TableOfYesAndNo]![bamCHECKBOXvalue]=Yes, Then have my query include
bam: [table1]![field10]+[table1]![field12]+[and so forth] if not just
discard bam from this query all together

If [TableOfYesAndNo]![barCHECKBOXvalue]=Yes, Then have my query include
bar: [table1]![field20]+[table1]![field22]+[and so forth] if not just
discard bar from this query all together

Hopefully you've done something similar and can help.

Thanks in advance,

Olden.

 

[Jeff Boyce]

Olden

Yes, you've captured the intent of my suggestion.

The one part I see missing is the "year" info. That's why I was offering
the idea of a "weekending" date/time field instead values of Week1, Week2,
...., Week52. (By the way, you can have Access calculate a "week number"
from a date).

One (blunt force) approach to converting data from wide to deep might be to
export the data into Excel and use the TRANSPOSE() function in Excel, before
re-importing into Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Let me recap to make sure I get what is suggested.

I would go from

+++week1,week2,week3,etc.
SKU1$$$$$,$$$$$,$$$$$$,$$$
SKU2$$$$$,$$$$$,$$$$$$,$$$

To:


SKU,WeekNumber,Amount
45d,Week1 ,500
45d,Week2 ,900
45d,Week3 ,1000

The question would then become what's the quickest way to turn 52
columns into 6000 records with the appropriate amounts, grab the right
week# from the name of a column in a table and apply it to the records?

Thanks in advance,

Olden

Olden

Instead of going "wide", think "deep". If your table structure was
something like:

tblWeeklySales
SKU
WeekEndingDate
SalesAmt

your query would be a Totals query, GroupBy SKU, GroupBy WeekEndingDate,
Sum
SalesAmt.

Note that this approach lets you go on for years without having to add
columns. And you can use this to pull out a total SalesAmt for any
particular date range, and for any specified SKUs.

Regards

Jeff Boyce
Microsoft Office/Access MVP>

Thanks for the interest in my situation.

The reason I need such a query is that my table contains monthly sales
each month is comprised of weeks, but the thing is that I have an
offset between my calendar weeks and fiscal weeks. (Some weeks in march
can represent february weeks)

So to revisit the example in my first post:

foo: [table1]![field1]+[table1]![field2]+[and so forth]

You can translate that to:

February: [MainTable]![Week1Sales]+[MainTable]![Week2Sales]+[and so
forth]

This is my initial dataset: 1 table with 52 columns and about 6000
records which are SKUs.

What I want is users to build their own query with checkboxes (or any
other control for that matter), 12 of them when checked that month gets
included in the query, kind of like when you drag field from the table
window in to your query.

How do I optimize, and what should I explore from here?

Thanks in advance,

Olden







Jeff Boyce wrote:
Olden

When I see multiple fields being added together, I begin to wonder if
the
data structure is optimized for Access. It is common for multiple
(repeating) fields to show up in a spreadsheet (this is about the only
way
to do it), but is is counterproductive to keep this kind of structure
in
an
Access table. While you can do it (as your description points out),
both
you and Access have to work much harder, and you don't get to use many
of
the features/functions Access provides.

If you'll describe a bit more about what kinds of data you are storing
in
"field1", "field2", ... "field10", ..., the folks here in the
newsgroup
may
be able to offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My current database is made up of mostly 1 table and one 1 main
query.
Here's an example of the main query:

foo: [table1]![field1]+[table1]![field2]+[and so forth]
bam: [table1]![field10]+[table1]![field12]+[and so forth]
bar: [table1]![field20]+[table1]![field22]+[and so forth]

I have a form with 3 Checkboxes
(fooCHECKBOX,bamCHECKBOX,barCHECKBOX)
that
writes Yes/No values to TableOfYesAndNo.

What I want is a query that does something along the line of:

If [TableOfYesAndNo]![fooCHECKBOXvalue]=Yes, Then have my query
include
foo: [table1]![field1]+[table1]![field2]+[and so forth] if not just
discard foo from this query all together

If [TableOfYesAndNo]![bamCHECKBOXvalue]=Yes, Then have my query
include
bam: [table1]![field10]+[table1]![field12]+[and so forth] if not
just
discard bam from this query all together

If [TableOfYesAndNo]![barCHECKBOXvalue]=Yes, Then have my query
include
bar: [table1]![field20]+[table1]![field22]+[and so forth] if not
just
discard bar from this query all together

Hopefully you've done something similar and can help.

Thanks in advance,

Olden.

 

[Olden]

I must admit your way is much more optimized... To convert my data I
queried my query extracting one month at a time adding MONTH: "January"
through "December and appended them to one table.

I can feel a crosstab query comming on... I will build it on a query
that polls my main table. (This is where I'm now making progress on my
initial post). Because my form will now set the criteria in the query
that the crosstab query will be built on, if a particular month is not
in the dataset the crosstab query will not include it as a column.

As for yearly tracking, in this case I don't need it.

My next post, is more likely to be on microsoft.public.access.forms
because at this stage what I expect to be troublesome is the leaving a
text box blank on a form and have the query understand it as having no
criteria (Whenever I leave a text box blank, my queries return no
lines).

Thanks for the insight!

Cheers,

Olden

Olden

Yes, you've captured the intent of my suggestion.

The one part I see missing is the "year" info. That's why I was offering
the idea of a "weekending" date/time field instead values of Week1, Week2,
..., Week52. (By the way, you can have Access calculate a "week number"
from a date).

One (blunt force) approach to converting data from wide to deep might be to
export the data into Excel and use the TRANSPOSE() function in Excel, before
re-importing into Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Let me recap to make sure I get what is suggested.

I would go from

+++week1,week2,week3,etc.
SKU1$$$$$,$$$$$,$$$$$$,$$$
SKU2$$$$$,$$$$$,$$$$$$,$$$

To:


SKU,WeekNumber,Amount
45d,Week1 ,500
45d,Week2 ,900
45d,Week3 ,1000

The question would then become what's the quickest way to turn 52
columns into 6000 records with the appropriate amounts, grab the right
week# from the name of a column in a table and apply it to the records?

Thanks in advance,

Olden

Olden

Instead of going "wide", think "deep". If your table structure was
something like:

tblWeeklySales
SKU
WeekEndingDate
SalesAmt

your query would be a Totals query, GroupBy SKU, GroupBy WeekEndingDate,
Sum
SalesAmt.

Note that this approach lets you go on for years without having to add
columns. And you can use this to pull out a total SalesAmt for any
particular date range, and for any specified SKUs.

Regards

Jeff Boyce
Microsoft Office/Access MVP>

Thanks for the interest in my situation.

The reason I need such a query is that my table contains monthly sales
each month is comprised of weeks, but the thing is that I have an
offset between my calendar weeks and fiscal weeks. (Some weeks in march
can represent february weeks)

So to revisit the example in my first post:

foo: [table1]![field1]+[table1]![field2]+[and so forth]

You can translate that to:

February: [MainTable]![Week1Sales]+[MainTable]![Week2Sales]+[and so
forth]

This is my initial dataset: 1 table with 52 columns and about 6000
records which are SKUs.

What I want is users to build their own query with checkboxes (or any
other control for that matter), 12 of them when checked that month gets
included in the query, kind of like when you drag field from the table
window in to your query.

How do I optimize, and what should I explore from here?

Thanks in advance,

Olden







Jeff Boyce wrote:
Olden

When I see multiple fields being added together, I begin to wonder if
the
data structure is optimized for Access. It is common for multiple
(repeating) fields to show up in a spreadsheet (this is about the only
way
to do it), but is is counterproductive to keep this kind of structure
in
an
Access table. While you can do it (as your description points out),
both
you and Access have to work much harder, and you don't get to use many
of
the features/functions Access provides.

If you'll describe a bit more about what kinds of data you are storing
in
"field1", "field2", ... "field10", ..., the folks here in the
newsgroup
may
be able to offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My current database is made up of mostly 1 table and one 1 main
query.
Here's an example of the main query:

foo: [table1]![field1]+[table1]![field2]+[and so forth]
bam: [table1]![field10]+[table1]![field12]+[and so forth]
bar: [table1]![field20]+[table1]![field22]+[and so forth]

I have a form with 3 Checkboxes
(fooCHECKBOX,bamCHECKBOX,barCHECKBOX)
that
writes Yes/No values to TableOfYesAndNo.

What I want is a query that does something along the line of:

If [TableOfYesAndNo]![fooCHECKBOXvalue]=Yes, Then have my query
include
foo: [table1]![field1]+[table1]![field2]+[and so forth] if not just
discard foo from this query all together

If [TableOfYesAndNo]![bamCHECKBOXvalue]=Yes, Then have my query
include
bam: [table1]![field10]+[table1]![field12]+[and so forth] if not
just
discard bam from this query all together

If [TableOfYesAndNo]![barCHECKBOXvalue]=Yes, Then have my query
include
bar: [table1]![field20]+[table1]![field22]+[and so forth] if not
just
discard bar from this query all together

Hopefully you've done something similar and can help.

Thanks in advance,

Olden.

 

[Jeff Boyce]

De nada.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I must admit your way is much more optimized... To convert my data I
queried my query extracting one month at a time adding MONTH: "January"
through "December and appended them to one table.

I can feel a crosstab query comming on... I will build it on a query
that polls my main table. (This is where I'm now making progress on my
initial post). Because my form will now set the criteria in the query
that the crosstab query will be built on, if a particular month is not
in the dataset the crosstab query will not include it as a column.

As for yearly tracking, in this case I don't need it.

My next post, is more likely to be on microsoft.public.access.forms
because at this stage what I expect to be troublesome is the leaving a
text box blank on a form and have the query understand it as having no
criteria (Whenever I leave a text box blank, my queries return no
lines).

Thanks for the insight!

Cheers,

Olden

Olden

Yes, you've captured the intent of my suggestion.

The one part I see missing is the "year" info. That's why I was offering
the idea of a "weekending" date/time field instead values of Week1,
Week2,
..., Week52. (By the way, you can have Access calculate a "week number"
from a date).

One (blunt force) approach to converting data from wide to deep might be
to
export the data into Excel and use the TRANSPOSE() function in Excel,
before
re-importing into Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Let me recap to make sure I get what is suggested.

I would go from

+++week1,week2,week3,etc.
SKU1$$$$$,$$$$$,$$$$$$,$$$
SKU2$$$$$,$$$$$,$$$$$$,$$$

To:


SKU,WeekNumber,Amount
45d,Week1 ,500
45d,Week2 ,900
45d,Week3 ,1000

The question would then become what's the quickest way to turn 52
columns into 6000 records with the appropriate amounts, grab the right
week# from the name of a column in a table and apply it to the records?

Thanks in advance,

Olden

Jeff Boyce wrote:
Olden

Instead of going "wide", think "deep". If your table structure was
something like:

tblWeeklySales
SKU
WeekEndingDate
SalesAmt

your query would be a Totals query, GroupBy SKU, GroupBy
WeekEndingDate,
Sum
SalesAmt.

Note that this approach lets you go on for years without having to add
columns. And you can use this to pull out a total SalesAmt for any
particular date range, and for any specified SKUs.

Regards

Jeff Boyce
Microsoft Office/Access MVP>

Thanks for the interest in my situation.

The reason I need such a query is that my table contains monthly
sales
each month is comprised of weeks, but the thing is that I have an
offset between my calendar weeks and fiscal weeks. (Some weeks in
march
can represent february weeks)

So to revisit the example in my first post:

foo: [table1]![field1]+[table1]![field2]+[and so forth]

You can translate that to:

February: [MainTable]![Week1Sales]+[MainTable]![Week2Sales]+[and so
forth]

This is my initial dataset: 1 table with 52 columns and about 6000
records which are SKUs.

What I want is users to build their own query with checkboxes (or
any
other control for that matter), 12 of them when checked that month
gets
included in the query, kind of like when you drag field from the
table
window in to your query.

How do I optimize, and what should I explore from here?

Thanks in advance,

Olden







Jeff Boyce wrote:
Olden

When I see multiple fields being added together, I begin to wonder
if
the
data structure is optimized for Access. It is common for multiple
(repeating) fields to show up in a spreadsheet (this is about the
only
way
to do it), but is is counterproductive to keep this kind of
structure
in
an
Access table. While you can do it (as your description points
out),
both
you and Access have to work much harder, and you don't get to use
many
of
the features/functions Access provides.

If you'll describe a bit more about what kinds of data you are
storing
in
"field1", "field2", ... "field10", ..., the folks here in the
newsgroup
may
be able to offer better suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My current database is made up of mostly 1 table and one 1 main
query.
Here's an example of the main query:

foo: [table1]![field1]+[table1]![field2]+[and so forth]
bam: [table1]![field10]+[table1]![field12]+[and so forth]
bar: [table1]![field20]+[table1]![field22]+[and so forth]

I have a form with 3 Checkboxes
(fooCHECKBOX,bamCHECKBOX,barCHECKBOX)
that
writes Yes/No values to TableOfYesAndNo.

What I want is a query that does something along the line of:

If [TableOfYesAndNo]![fooCHECKBOXvalue]=Yes, Then have my query
include
foo: [table1]![field1]+[table1]![field2]+[and so forth] if not
just
discard foo from this query all together

If [TableOfYesAndNo]![bamCHECKBOXvalue]=Yes, Then have my query
include
bam: [table1]![field10]+[table1]![field12]+[and so forth] if not
just
discard bam from this query all together

If [TableOfYesAndNo]![barCHECKBOXvalue]=Yes, Then have my query
include
bar: [table1]![field20]+[table1]![field22]+[and so forth] if not
just
discard bar from this query all together

Hopefully you've done something similar and can help.

Thanks in advance,

Olden.