Create flexible Query - One instead of dozens

[BlueWolverine]

Hello,

Running Access 2003 on XP Pro.

I am trying to write a document generator; said documents are based on the
data in the master table.

I want to write a query that populates based on the choices made on a form.
The problem is I want 1 query to do it, instead of a ton of them.

Here's what I've got. a document is defined by Day of the Week and Fleet
Activity.

So "Monday DC" would be a unique document.

So there are two fields in the picker form. "Day of the Week" and "Fleet
Activity".

I want a query to pull all the records out of the table for (in this
example) Monday and DC. The catch is that since I did this originally in
Excel, I have a column for each day of the week, and one for each Activity,
such that I can't have the query look for Monday in Day of the Week, It would
look for True under Monday. Well what if it's Tuesday? It won't find True
under Monday (unless it's both).

The same problem applies to the fleet activity.

So here's what I want in pseudo-fake-Ijustmadeitup code.

formname = bob
Day of the Week Textbox = Dotw
Fleet Activity Textbox = FAT

Return all records where rec_id.Dotw = True and rec_id.FAT = True

End pseudocode

So the idea is not to have the value in the field of the record be flexible,
it's the name of the field that's flexible. I would rather not do it with
VBA but I can if that's what is needed.

Best ideas? Thank you so much.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to do this. See
also CreateQueries4 and CreateQueries5.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[BlueWolverine]

So there's no way for the SQL to be flexible on its own, you have to write
inside of VBA and have that save the query?

Here's the approach I am going to take.

I think I am going to preface the query generation with a if statement
asking if the query is already written. (and saved). I think I want the
queries to be saved so that I do other things with them... Otherwise I
suppose I could just save over themselves.


Thanks for the samples and awesome library.

Let me know if you guys (all of you) think of someway to not involve VBA
into this one.

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to do this. See
also CreateQueries4 and CreateQueries5.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Bob Barrows [MVP]]

Hello,

Running Access 2003 on XP Pro.

I am trying to write a document generator; said documents are based
on the data in the master table.

I want to write a query that populates based on the choices made on a
form. The problem is I want 1 query to do it, instead of a ton of
them.

Here's what I've got. a document is defined by Day of the Week and
Fleet Activity.

So "Monday DC" would be a unique document.

So there are two fields in the picker form. "Day of the Week" and
"Fleet Activity".

I want a query to pull all the records out of the table for (in this
example) Monday and DC. The catch is that since I did this
originally in Excel, I have a column for each day of the week, and
one for each Activity, such that I can't have the query look for
Monday in Day of the Week, It would look for True under Monday. Well
what if it's Tuesday? It won't find True under Monday (unless it's
both).

The same problem applies to the fleet activity.

So here's what I want in pseudo-fake-Ijustmadeitup code.

formname = bob
Day of the Week Textbox = Dotw
Fleet Activity Textbox = FAT

Return all records where rec_id.Dotw = True and rec_id.FAT = True

End pseudocode

So the idea is not to have the value in the field of the record be
flexible, it's the name of the field that's flexible. I would rather
not do it with VBA but I can if that's what is needed.

Best ideas? Thank you so much.

You should start by "folding" the data into a true database table. Use a
saved union query. I cannot get very specific since you did not supply
specifics about your table structure, but a folding query would look
like this"

select "Monday" as DayOfWeek, <rest of the fields needed>
from table where [Monday] = true
union all
select "Tuesday" as DayOfWeek, <rest of the fields needed>
from table where [Tuesday] = true
union all
etc.

Now it should simply be a matter of
Select <stuff> from qryFoldedData
where DayOfTheWeek = frm!Dotw.value
and FleetActivity=frm!FleetActivity.value

 

[BlueWolverine]

Ok that sounds like a gameplan. What happens when A unique record is tru for
both Monday and Tuesday? would you get two records in this folded table?

For instance

In original Table
Name | Monday| Tuesday | Comments
Bob | X | X | I owe him $5

would show up in the folded data as
Name | DayofWeek |Comments
Bob | Monday |I owe him $5
Bob |Tuesday | I owe him $5

Is that accurate?

If so this is EXACTLY what I wanted. I'd have to write a bunch of "sub
queries" within the big one, but it would all be in one place in a more
manageable form.

Thank you a ton!

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Hello,

Running Access 2003 on XP Pro.

I am trying to write a document generator; said documents are based
on the data in the master table.

I want to write a query that populates based on the choices made on a
form. The problem is I want 1 query to do it, instead of a ton of
them.

Here's what I've got. a document is defined by Day of the Week and
Fleet Activity.

So "Monday DC" would be a unique document.

So there are two fields in the picker form. "Day of the Week" and
"Fleet Activity".

I want a query to pull all the records out of the table for (in this
example) Monday and DC. The catch is that since I did this
originally in Excel, I have a column for each day of the week, and
one for each Activity, such that I can't have the query look for
Monday in Day of the Week, It would look for True under Monday. Well
what if it's Tuesday? It won't find True under Monday (unless it's
both).

The same problem applies to the fleet activity.

So here's what I want in pseudo-fake-Ijustmadeitup code.

formname = bob
Day of the Week Textbox = Dotw
Fleet Activity Textbox = FAT

Return all records where rec_id.Dotw = True and rec_id.FAT = True

End pseudocode

So the idea is not to have the value in the field of the record be
flexible, it's the name of the field that's flexible. I would rather
not do it with VBA but I can if that's what is needed.

Best ideas? Thank you so much.

You should start by "folding" the data into a true database table. Use a
saved union query. I cannot get very specific since you did not supply
specifics about your table structure, but a folding query would look
like this"

select "Monday" as DayOfWeek, <rest of the fields needed>
from table where [Monday] = true
union all
select "Tuesday" as DayOfWeek, <rest of the fields needed>
from table where [Tuesday] = true
union all
etc.

Now it should simply be a matter of
Select <stuff> from qryFoldedData
where DayOfTheWeek = frm!Dotw.value
and FleetActivity=frm!FleetActivity.value

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Ok that sounds like a gameplan. What happens when A unique record is
tru for both Monday and Tuesday? would you get two records in this
folded table?

Of course you would. Try it.

For instance

In original Table
Name | Monday| Tuesday | Comments
Bob | X | X | I owe him $5

would show up in the folded data as
Name | DayofWeek |Comments
Bob | Monday |I owe him $5
Bob |Tuesday | I owe him $5

Is that accurate?
Yes


If so this is EXACTLY what I wanted. I'd have to write a bunch of
"sub queries" within the big one, but it would all be in one place in
a more manageable form.

I would suggest that you would get a bigger bang from this buck by
redesigning your original table so it looks like the folded table. Use
the "folding" query to insert the data from the original table into the
new table. This will allow you to create a primary key as well as
indexes to improve performance, something that is not possible using a
saved union query.

The normalization lesson to be learned here is:
Data (the day of the week is data) belongs in table rows, not in column
names

 

[BlueWolverine]

Thank you very much for the help. I have no formal training in Database
design and I am kinda learning as I go.

The origin of this weird table is that it came from excel. I had this huge
obscene file in excel that did what I am writing this database to do, but the
performance in excel was atrocious. 15 minutes macros etc. When I did it
the first time, I was learning VBA for Excel, and had no knowledge of Access.
Now I know a bit about access and realize it's more suited. The problem is
I imported data from Excel and that's how I did it in excel. So I have to
convert and that takes time etc.

Anyway, Thank you for the design considerations.

--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!