A query based on form action

O

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.
 
J

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
 
O

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







Jeff said:
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 said:
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.
Click to expand...
Click to expand...
 
J

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>

Olden said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
O

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

Jeff said:
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>

Olden said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update query or VBA request 3

Top