Query Problem, Please Help!!

[HOW DO I CREATE A SEARCH FORM????]

The situation is as follows:

I created a table that is called "ST-1", it has 14 fields total, 12 of them
labeled after every month of the year, 1 labeled "year" and the last one is a
"client id" field.

"client id" and "year" fields are for text input, while the january -
december fields are yes/no or checkmark fields.

I'm trying to create a query from this table that shows me only the records
that contain the current year and where the current month checkmark="no".

how would i do this???

i hope that i was clear enough, if anyone needs further clarification on
what i'm trying to do please let me know.

 

['69 Camaro]

Hi.

how would i do this???

First, you would redesign your table into a normalized table. In this
redesign, you would avoid time-wasting bug-makers, such as Reserved Keywords
(like "year"), and non-alphanumeric characters in table names and field
names, such as spaces and dashes. The one exception is the underscore
character.

You would eliminate the 12 month columns and replace them with two columns,
one to hold the month (preferably not the name of the month, but the foriegn
key of a lookup table containing the names of the months), and one to hold
whatever value (if there is one) which is currently being placed in any of
the current 12 month columns for each record. If the current 12 month
columns only hold a value to indicate which month the record is for, then you
can skip creating this second column.

You would change the name of the "year" column to something like "STYear"
and change the data type to number (size can be Integer or Long).

If you did these things, the query would be super easy, such as the following:

SELECT *
FROM tblST_1
WHERE ((checkmark=no) AND (STYear = DatePart("yyyy", Date())));

If you used a foreign key to store the months, then the query could join the
two tables and the SQL statement would look like the following:

SELECT STYear, MonthName, checkmark, ClientID
FROM tblST_1 INNER JOIN tblMonths ON tblST_1.STMon = tblMonths.ID
WHERE ((checkmark=no) AND (STYear = DatePart("yyyy", Date())));

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[Chaim]

Bad table design is a problem here.

Rather than having a column per month, you would be better off with a table
that looks like: ST-1 --> (theMonthAndYear, theCheckMark). theMonthAndYear
field is Date/Time.

Then your query is straightforward:

select * from [ST-1]
where year(theMonthAndYear) = year(Date())
and month(theMonthAndYear) = month(Date())
and theCheckMark = "no"

As is, you've described a spreadsheet, not a relational DB table structure.
--

Chaim


"HOW DO I CREATE A SEARCH FORM????"

 

['69 Camaro]

Sorry. I neglected to include the month criteria. The corrected SQL
statements would look like this:

With the month name in the table:

SELECT *
FROM tblST_1
WHERE ((checkmark=no) AND (STMon = DatePart("m", Date())) AND (STYear =
DatePart("yyyy", Date())));

With a foreign key for the month name:

SELECT STYear, MonthName, checkmark, ClientID
FROM tblST_1 INNER JOIN tblMonths ON tblST_1.STMon = tblMonths.ID
WHERE ((checkmark=no) AND (STMon = DatePart("m", Date())) AND (STYear =
DatePart("yyyy", Date())));

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[John Slattery]

When I first saw your post, my thoughts were along the lines posted by Chaim
and '69 Camaro. However, something has occurred to me that I would like to
share. It is true that answering the question you posed is simpler with the
schema of your database modified as described, but is there a reason that
you designed it as you did? I am wondering if your original or primary
purpose was to compare months within a client-year. If so, your original
design simplifies this and modifying the design as suggested will make that
about as difficult as your present problem. The notion that your table is
not normalized is debatable. If the relation is that of client-years and
the months are attributes of these entities, one could argue that your table
is normalized.

FWIW,

John

 

[HOW DO I CREATE A SEARCH FORM????]

I completely understand what you two guys are telling me and trust me i'm not
trying to complicate my life on purpose here, but the problem is that i need
this table to be laid out the way it is. The reason for this is that it is a
subform in a form and it is more user friendly that way. The way that your
telling me to do it wouldnt be user friendly in my main form, all it would
show would be numbers.

 

[HOW DO I CREATE A SEARCH FORM????]

DO YOU GUYS HAVE AN EXAMPLE OF THIS THAT I CAN DOWNLOAD OR SEE, I'M NOT AS
SMART AS I SOUND, IM NOT AN EXPERT WITH ACCESS.....THANX
(SORRY BOUT THE CAPS, (HABIT))

Bad table design is a problem here.

Rather than having a column per month, you would be better off with a table
that looks like: ST-1 --> (theMonthAndYear, theCheckMark). theMonthAndYear
field is Date/Time.

Then your query is straightforward:

select * from [ST-1]
where year(theMonthAndYear) = year(Date())
and month(theMonthAndYear) = month(Date())
and theCheckMark = "no"

As is, you've described a spreadsheet, not a relational DB table structure.
--

Chaim


"HOW DO I CREATE A SEARCH FORM????"

The situation is as follows:

I created a table that is called "ST-1", it has 14 fields total, 12 of them
labeled after every month of the year, 1 labeled "year" and the last one is a
"client id" field.

"client id" and "year" fields are for text input, while the january -
december fields are yes/no or checkmark fields.

I'm trying to create a query from this table that shows me only the records
that contain the current year and where the current month checkmark="no".

how would i do this???

i hope that i was clear enough, if anyone needs further clarification on
what i'm trying to do please let me know.

 

['69 Camaro]

Hi.

the problem is that i need
this table to be laid out the way it is. The reason for this is that it is a
subform in a form and it is more user friendly that way.

The reason that experts recommend using a query as the Record Source for a
form instead of a table is because the table is so inflexible. The query
will allow the designer to display the data in just about any way needed. If
you store the data in Normalized tables, SQL is a very powerful programming
language and will allow you to manipulate that data for display purposes.
SQL queries are very flexible and can even make calculations and display
those calculations for your forms and reports when those values aren't even
stored in the tables.

The way that your
telling me to do it wouldnt be user friendly in my main form, all it would
show would be numbers.

You need to join more than one table together in a query to display the
"text" values associated with those numbers in the main table. Make the
subform's Record Source Property this query, and then it will be very
user-friendly.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[HOW DO I CREATE A SEARCH FORM????]

John,

thank you for your interest, I don't really understand what you guys mean by
a "normalized table". your suspicion of why i designed this table the way i
did is right. Is there anything you know that might be able to help me?

 

['69 Camaro]

Hi.

DO YOU GUYS HAVE AN EXAMPLE OF THIS THAT I CAN DOWNLOAD OR SEE

The Northwind.mdb database comes with Access and provides many examples. If
you did not install this on your hard drive when you installed Access, then
you may download a copy for Access 2000 from the following Web page:

http://www.microsoft.com/downloads/...72-8dbe-422b-8676-c632d66c529c&displaylang=en

Look at the "Quarterly Orders" form, the "Quarterly Orders Subform" form,
and "Quarterly Orders by Product" query. Notice that the tables used for
these forms ("Products," "Orders," and "Order Details") do not have fields
with repeating data (Qtr1, Qtr2, Qtr3, Qtr4) shown in the subform? The field
names (Qtr1, Qtr2, Qtr3, Qtr4) displayed in the subform don't exist in any of
the tables. The data is stored in a normalized structure in the tables and
the query manipulates the data into the desired display format, and then the
query is used as a Record Source for the subform.

(SORRY BOUT THE CAPS, (HABIT))

Writing more than a short phrase or two in all caps in a message posted to
newsgroups and discussion groups is considered shouting. It's also
considered rude.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

DO YOU GUYS HAVE AN EXAMPLE OF THIS THAT I CAN DOWNLOAD OR SEE, I'M NOT AS
SMART AS I SOUND, IM NOT AN EXPERT WITH ACCESS.....THANX
(SORRY BOUT THE CAPS, (HABIT))

Bad table design is a problem here.

Rather than having a column per month, you would be better off with a table
that looks like: ST-1 --> (theMonthAndYear, theCheckMark). theMonthAndYear
field is Date/Time.

Then your query is straightforward:

select * from [ST-1]
where year(theMonthAndYear) = year(Date())
and month(theMonthAndYear) = month(Date())
and theCheckMark = "no"

As is, you've described a spreadsheet, not a relational DB table structure.
--

Chaim


"HOW DO I CREATE A SEARCH FORM????"

The situation is as follows:

I created a table that is called "ST-1", it has 14 fields total, 12 of them
labeled after every month of the year, 1 labeled "year" and the last one is a
"client id" field.

"client id" and "year" fields are for text input, while the january -
december fields are yes/no or checkmark fields.

I'm trying to create a query from this table that shows me only the records
that contain the current year and where the current month checkmark="no".

how would i do this???

i hope that i was clear enough, if anyone needs further clarification on
what i'm trying to do please let me know.

 

['69 Camaro]

Hi, John.

I am wondering if your original or primary purpose was to compare months
within a client-year.

The data doesn't need to be stored this way for it to be displayed in this
manner. This type of display can be serviced with a crosstab query from
normalized tables. For more information on building crosstab queries for
this type of situation, please use the link to Tom Wickerath's step-by-step
crosstab query tutorial located on the following Web page:

http://www.Access.QBuilt.com/html/articles.html

The notion that your table is not normalized is debatable.

That her table is not normalized is not debatable. Clearly it is not
normalized. What _can_ be debated is whether or not it _should_ be.

If the relation is that of client-years and the months are attributes of
these entities, one could argue that your table is normalized.

These 12 months are a set of related attributes, not separate attributes of
the entity. Putting related attributes in the same record will result in
repeated grouping of these attributes, which violates First Normal Form
(http://www.datamodel.org/NormalizationRules.html and
http://support.microsoft.com/default.aspx?id=209534).

Remember that "columns are expensive, records are cheap." Twelve records
containing the primary key, month, year, checkmark, and ClientID are much
cheaper in storage space than twelve records containing the primary key,
each of the 12 months, year, checkmark, and ClientID. Queries will be far
less complex, scalable, flexible, and prevent anomolies if this data is
normalized.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

 

[John Slattery]

You've probably realized that the wrinkle that makes your requirement
difficult is the part about current month and year. There are two solutions
that I can see. One would be to create the query on the fly so that, in
effect, you would be able to specify a year and month. It would involve
some programming and I suspect that is not an attractive option to you. The
other is somewhat clumsy and involves working in SQL view as opposed to
design view. You create a union query that results in something that looks
like the table others have suggested. Following is the SQL to paste into
SQL view:

SELECT [ST-1].[Client ID], [ST-1].[Year], 1 AS Month, [ST-1].[Jan] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 2 AS Month, [ST-1].[Feb] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 3 AS Month, [ST-1].[Mar] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 4 AS Month, [ST-1].[Apr] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 5 AS Month, [ST-1].[May] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 6 AS Month, [ST-1].[Jun] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 7 AS Month, [ST-1].[Jul] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 8 AS Month, [ST-1].[Aug] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 9 AS Month, [ST-1].[Sep] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 10 AS Month, [ST-1].[Oct] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 11 AS Month, [ST-1].[Nov] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 12 AS Month, [ST-1].[Dec] AS f
FROM [ST-1];

The only thing you may need to change are the names of the month fields if,
for instance, you used full names for them. Name the query ST1UnionMonth
and the following query will bring things together:

SELECT [ST-1].*
FROM ST1UnionMonth INNER JOIN [ST-1] ON (ST1UnionMonth.Year = [ST-1].Year)
AND (ST1UnionMonth.[Client ID] = [ST-1].[Client ID])
WHERE (((ST1UnionMonth.Year)=CStr(Year(Date()))) AND
((ST1UnionMonth.Month)=Month(Date())) AND ((ST1UnionMonth.f)=False));

It looks nasty but after pasting it into SQL view, you can switch to design
view. Date() returns the current system date. (As long as you don't run
this across midnight on the last day of the month, you'll be OK.) Year()
returns a numeric representation of a date's year. Month() does likewise.
CStr() is used to turn the numeric year returned by Year() into a string of
text that will match your Year field. The idea is to use the union query to
identify the Client ID-Year combinations in which you are interested and
then use them to pull the records out of ST-1.

To keep things simple, I've disregarded practices that I normally follow,
most notably conventions for naming. Access is an incredible tool, but it
is much more difficult to get started with than, say, Excel. Also, I think
the Help has been rather a disaster following Access 97. I found Using
Microsoft Access 2 from Que by Jennings a good first reference, particularly
the chapter on Access's dialect of SQL. I hope that subsequent editions for
later versions of Access are as good. It will take you up to your first
exercises in programming. Then, I would suggest the Access xxxx Developer's
Handbook from Sybex. The authors vary a bit, but I think Litwin and Getz
have been onboard for most, if not all, editions.

John

 

[HOW DO I CREATE A SEARCH FORM????]

John,

I tried the sql formula you posted and it worked perfectly!, thanx a lot
for all of your help.

You've probably realized that the wrinkle that makes your requirement
difficult is the part about current month and year. There are two solutions
that I can see. One would be to create the query on the fly so that, in
effect, you would be able to specify a year and month. It would involve
some programming and I suspect that is not an attractive option to you. The
other is somewhat clumsy and involves working in SQL view as opposed to
design view. You create a union query that results in something that looks
like the table others have suggested. Following is the SQL to paste into
SQL view:

SELECT [ST-1].[Client ID], [ST-1].[Year], 1 AS Month, [ST-1].[Jan] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 2 AS Month, [ST-1].[Feb] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 3 AS Month, [ST-1].[Mar] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 4 AS Month, [ST-1].[Apr] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 5 AS Month, [ST-1].[May] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 6 AS Month, [ST-1].[Jun] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 7 AS Month, [ST-1].[Jul] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 8 AS Month, [ST-1].[Aug] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 9 AS Month, [ST-1].[Sep] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 10 AS Month, [ST-1].[Oct] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 11 AS Month, [ST-1].[Nov] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 12 AS Month, [ST-1].[Dec] AS f
FROM [ST-1];

The only thing you may need to change are the names of the month fields if,
for instance, you used full names for them. Name the query ST1UnionMonth
and the following query will bring things together:

SELECT [ST-1].*
FROM ST1UnionMonth INNER JOIN [ST-1] ON (ST1UnionMonth.Year = [ST-1].Year)
AND (ST1UnionMonth.[Client ID] = [ST-1].[Client ID])
WHERE (((ST1UnionMonth.Year)=CStr(Year(Date()))) AND
((ST1UnionMonth.Month)=Month(Date())) AND ((ST1UnionMonth.f)=False));

It looks nasty but after pasting it into SQL view, you can switch to design
view. Date() returns the current system date. (As long as you don't run
this across midnight on the last day of the month, you'll be OK.) Year()
returns a numeric representation of a date's year. Month() does likewise.
CStr() is used to turn the numeric year returned by Year() into a string of
text that will match your Year field. The idea is to use the union query to
identify the Client ID-Year combinations in which you are interested and
then use them to pull the records out of ST-1.

To keep things simple, I've disregarded practices that I normally follow,
most notably conventions for naming. Access is an incredible tool, but it
is much more difficult to get started with than, say, Excel. Also, I think
the Help has been rather a disaster following Access 97. I found Using
Microsoft Access 2 from Que by Jennings a good first reference, particularly
the chapter on Access's dialect of SQL. I hope that subsequent editions for
later versions of Access are as good. It will take you up to your first
exercises in programming. Then, I would suggest the Access xxxx Developer's
Handbook from Sybex. The authors vary a bit, but I think Litwin and Getz
have been onboard for most, if not all, editions.

John

 

[Diana Prince]

You've probably realized that the wrinkle that makes your requirement
difficult is the part about current month and year. There are two solutions
that I can see. One would be to create the query on the fly so that, in
effect, you would be able to specify a year and month. It would involve
some programming and I suspect that is not an attractive option to you. The
other is somewhat clumsy and involves working in SQL view as opposed to
design view. You create a union query that results in something that looks
like the table others have suggested. Following is the SQL to paste into
SQL view:

SELECT [ST-1].[Client ID], [ST-1].[Year], 1 AS Month, [ST-1].[Jan] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 2 AS Month, [ST-1].[Feb] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 3 AS Month, [ST-1].[Mar] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 4 AS Month, [ST-1].[Apr] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 5 AS Month, [ST-1].[May] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 6 AS Month, [ST-1].[Jun] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 7 AS Month, [ST-1].[Jul] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 8 AS Month, [ST-1].[Aug] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 9 AS Month, [ST-1].[Sep] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 10 AS Month, [ST-1].[Oct] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 11 AS Month, [ST-1].[Nov] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 12 AS Month, [ST-1].[Dec] AS f
FROM [ST-1];

The only thing you may need to change are the names of the month fields if,
for instance, you used full names for them. Name the query ST1UnionMonth
and the following query will bring things together:

SELECT [ST-1].*
FROM ST1UnionMonth INNER JOIN [ST-1] ON (ST1UnionMonth.Year = [ST-1].Year)
AND (ST1UnionMonth.[Client ID] = [ST-1].[Client ID])
WHERE (((ST1UnionMonth.Year)=CStr(Year(Date()))) AND
((ST1UnionMonth.Month)=Month(Date())) AND ((ST1UnionMonth.f)=False));

It looks nasty but after pasting it into SQL view, you can switch to design
view. Date() returns the current system date. (As long as you don't run
this across midnight on the last day of the month, you'll be OK.) Year()
returns a numeric representation of a date's year. Month() does likewise.
CStr() is used to turn the numeric year returned by Year() into a string of
text that will match your Year field. The idea is to use the union query to
identify the Client ID-Year combinations in which you are interested and
then use them to pull the records out of ST-1.

To keep things simple, I've disregarded practices that I normally follow,
most notably conventions for naming. Access is an incredible tool, but it
is much more difficult to get started with than, say, Excel. Also, I think
the Help has been rather a disaster following Access 97. I found Using
Microsoft Access 2 from Que by Jennings a good first reference, particularly
the chapter on Access's dialect of SQL. I hope that subsequent editions for
later versions of Access are as good. It will take you up to your first
exercises in programming. Then, I would suggest the Access xxxx Developer's
Handbook from Sybex. The authors vary a bit, but I think Litwin and Getz
have been onboard for most, if not all, editions.

John

John, you've been working with Access since the days of Access 2.0, yet you
convince her that her table is normalized (read "just fine") and offer this
*solution*? Any recordset created from this union query is not going to be
updateable, meaning she won't be able to make changes to existing records, or
add or delete records. Her table structure isn't flexible or scalable.
She's going to have problems building the application if she continues to add
additional capabilities from this table, especially if she uses your example
for how to create and work with more non-normalized tables in the database.

You're telling a blind woman, "You're almost there. Just take two more
steps forward . . ." when you know she's standing on the edge of a cliff.

Incredible.

Di

 

[HOW DO I CREATE A SEARCH FORM????]

DIANE,

My table is pretty much at its maximum potential. I dont need the query to
be updateable nor do i need to add additional records. in fact i prefer it
this way. The way John showed me to do it, is exacly what i wanted, maybe my
whole purpose is hard to understand but I designed it the way I did for a
reason, if only you guys could see the database then, i think, you would
understand better.

thanks for your concern though,

juan (yeah! i'm a guy!)

You've probably realized that the wrinkle that makes your requirement
difficult is the part about current month and year. There are two solutions
that I can see. One would be to create the query on the fly so that, in
effect, you would be able to specify a year and month. It would involve
some programming and I suspect that is not an attractive option to you. The
other is somewhat clumsy and involves working in SQL view as opposed to
design view. You create a union query that results in something that looks
like the table others have suggested. Following is the SQL to paste into
SQL view:

SELECT [ST-1].[Client ID], [ST-1].[Year], 1 AS Month, [ST-1].[Jan] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 2 AS Month, [ST-1].[Feb] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 3 AS Month, [ST-1].[Mar] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 4 AS Month, [ST-1].[Apr] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 5 AS Month, [ST-1].[May] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 6 AS Month, [ST-1].[Jun] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 7 AS Month, [ST-1].[Jul] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 8 AS Month, [ST-1].[Aug] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 9 AS Month, [ST-1].[Sep] AS f FROM
[ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 10 AS Month, [ST-1].[Oct] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 11 AS Month, [ST-1].[Nov] AS f
FROM [ST-1]
UNION
SELECT [ST-1].[Client ID], [ST-1].[Year], 12 AS Month, [ST-1].[Dec] AS f
FROM [ST-1];

The only thing you may need to change are the names of the month fields if,
for instance, you used full names for them. Name the query ST1UnionMonth
and the following query will bring things together:

SELECT [ST-1].*
FROM ST1UnionMonth INNER JOIN [ST-1] ON (ST1UnionMonth.Year = [ST-1].Year)
AND (ST1UnionMonth.[Client ID] = [ST-1].[Client ID])
WHERE (((ST1UnionMonth.Year)=CStr(Year(Date()))) AND
((ST1UnionMonth.Month)=Month(Date())) AND ((ST1UnionMonth.f)=False));

It looks nasty but after pasting it into SQL view, you can switch to design
view. Date() returns the current system date. (As long as you don't run
this across midnight on the last day of the month, you'll be OK.) Year()
returns a numeric representation of a date's year. Month() does likewise.
CStr() is used to turn the numeric year returned by Year() into a string of
text that will match your Year field. The idea is to use the union query to
identify the Client ID-Year combinations in which you are interested and
then use them to pull the records out of ST-1.

To keep things simple, I've disregarded practices that I normally follow,
most notably conventions for naming. Access is an incredible tool, but it
is much more difficult to get started with than, say, Excel. Also, I think
the Help has been rather a disaster following Access 97. I found Using
Microsoft Access 2 from Que by Jennings a good first reference, particularly
the chapter on Access's dialect of SQL. I hope that subsequent editions for
later versions of Access are as good. It will take you up to your first
exercises in programming. Then, I would suggest the Access xxxx Developer's
Handbook from Sybex. The authors vary a bit, but I think Litwin and Getz
have been onboard for most, if not all, editions.

John

John, you've been working with Access since the days of Access 2.0, yet you
convince her that her table is normalized (read "just fine") and offer this
*solution*? Any recordset created from this union query is not going to be
updateable, meaning she won't be able to make changes to existing records, or
add or delete records. Her table structure isn't flexible or scalable.
She's going to have problems building the application if she continues to add
additional capabilities from this table, especially if she uses your example
for how to create and work with more non-normalized tables in the database.

You're telling a blind woman, "You're almost there. Just take two more
steps forward . . ." when you know she's standing on the edge of a cliff.

Incredible.

Di

 

[John Slattery]

I've let stand critiques of the table with one exception and have gently, I
hope, suggested in the final paragraph of my last post that I see room for
improvement. If you have been reading carefully, then it must be that one
exception to which your post is directed. You disagree with my contention
that the table is normalized. I suspect that you share the opinion of an
earlier post that the Boolean fields named for the months of the year
represent a repeating group of fields, duplicate columns, etc.

First normal form is about the atomiticity of attributes. The typical
bullet point explanation is that field values have to be atomic and the
typical example of failure is something like a Product field with a
comma-delimited list of product names. If you get the paragraph-or-two
explanation, you also understand that repeating groups of fields are against
the rules but how you get from the basic principle of atomic attributes to
the implication that repeating groups are bad is, maybe, a little foggy. You
readily see the inefficiency and complications introduced by them, though,
and move on. Without understanding the connection, however, you may have
difficulty identifying a repeating group.

The connection becomes clear when you consider what someone would likely do
to resolve a Product field with a comma-delimited list of product names if
all he had were the bullet point explanation--Product would soon be joined
by Product2, Product3, and Product4. Our designer thinks he's made a
substantial improvement until he realizes that it's just as hard to poke
through four fields to see if a customer bought a hammer as it is to parse a
comma-delimited list and inspect it. Then, of course, there are Product5
and Product6 that show up later. You know the story. The two approaches
look different in terms of the RDBMS, but are one-in-the-same as far as
relational database theory.

Then, do the twelve fields in the table represent a list of values that must
be searched to find the value of interest? Clearly, they do not. If you
want the value for whatever the April Boolean represents, you issue a SELECT
for April with an appropriate WHERE clause and you are done. If the table
had been implemented as ST-1{Client ID, Year, Month1, Month2, Month3, ...,
Month12} and the presence of a date, number, or string indicating a month
appeared in one of the Month fields to represent what the Boolean does, the
table would not be in first normal form. If you want the value for April in
this case, you need to inspect the values of a number of fields to determine
if it is true or false. You're looking for a hammer in haystack again.

The table is in first normal form and therefore normalized if we assume that
Client ID and Year are never packed which doesn't seem like much of a
stretch. Certainly, no one has suggested otherwise. We'll need the data and
an understanding of it to determine if it meets higher forms.

If you accept that the table is normalized but still contend that the
one-to-many relationship of ClientYear{Client ID, Year} and
ClientYearMonth{Client ID, Year, Month} is somehow better or more efficient,
we're not there yet. The only task that the ClientYear(1-oo)ClientYearMonth
design better facilitates is the one that launched this discussion: obtain
the value for one of the Booleans when which one is not known at design
time, in this case for the current month. Everything else is more
difficult. For example, imagine trying to find client years with some set of
values for each month. With ST-1, finding client years where the April and
May Booleans are true and the others are false is trivial. With
ClientYear(1-oo)ClientYearMonth, it becomes a nasty exercise in sub selects
or aliased table joins. Yes, it's true you could get at the answer from
this design with a cross tab. My experience with cross tabs is that with
anything more than trivial data, they become slow. Besides, as you took
exception to my solution, neither the cross tab nor the sub select or
aliased table join approaches yield an updatable recordset.

I sense from your post and others that there is also concern about using up
so many fields on these twelve Booleans, that ClientYear(1-oo)
ClientYearMonth is better because it uses fewer fields. Carry that thinking
through to its logical end. You end up with tables that contain nothing
more than primary keys and other related tables that contain primary and
foreign keys, an attribute name field, and an attribute value field. I
don't know about you, but this isn't some place I want to go.

 

[John Slattery]

Please see my 8/26/2005 12:34 PM post for a response.