Do I fix this with a query or VB?

[gremillion1 via AccessMonster.com]

Table example:

Last First Read-1 Lang-1 Read -2 Lang-2

Doe Jane 89 90 88 50
Smith Joe 50 60 70 80
Cook Willie 100 80 90 87

The desired output is:
Last First Read-Avg Lang-Avg
Smith Joe 60 70

I only want to display students with 2 or more averages below 73. This is
why Jane Smith doesn’t appear (only one below 73), and Willie Cook doesn’t
appear (none below 73.)

Currently I’m getting:
Last First Name Read Avg Lang-Avg
Doe Jane 70
Smith Joe 60 70
Cook Willie

 

[Jeff Boyce]

When your table has "repeating fields" ("Read-1", ... "Read-2", ...), you
don't have a table! That's how a spreadsheet would add new values, but in a
relational database like Access, a well-normalized table is "deep, not
wide".

With your design, if you add another category, you have to change the table,
the query, the form, the report, the code, ...?! That's a maintenance
nightmare!

How about a table that contains [categoryID] and [categoryname], and another
than contains [personID], [categoryID] and [score]? That way, if you need
to add a new category and make it available for record persons' scores, you
simply add the category to the category table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Use a union query to get there --
SELECT Last, First, "Read" As [Category], Read-1 AS [Score]
FROM YourTable
UNION SELECT Last, First, "Read" As [Category], Read-2 AS [Score]
FROM YourTable
UNION SELECT Last, First, "Lang" As [Category], Lang-1 AS [Score]
FROM YourTable
UNION SELECT Last, First, "Lang" As [Category], Lang-2 AS [Score]
FROM YourTable;


--
KARL DEWEY
Build a little - Test a little

When your table has "repeating fields" ("Read-1", ... "Read-2", ...), you
don't have a table! That's how a spreadsheet would add new values, but in a
relational database like Access, a well-normalized table is "deep, not
wide".

With your design, if you add another category, you have to change the table,
the query, the form, the report, the code, ...?! That's a maintenance
nightmare!

How about a table that contains [categoryID] and [categoryname], and another
than contains [personID], [categoryID] and [score]? That way, if you need
to add a new category and make it available for record persons' scores, you
simply add the category to the category table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[steve dassin]

...in a relational database like Access....

People who think Access is "relational" are clueless. Those who think sql
server is relational are clueless-er. And those who think sql server is more
"relational" than Access are the clueless-est. Access is a nice little
utility for data operations and reporting. But lets not undermine what
professionalism still exists in IT by blindly throwing around a concept that
still eludes most.

www.beyondsql.blogspot.com

 

[Jeff Boyce]

"Steve"

How does this provide assistance to the original poster?

Aside from the reference to a weblog, can you offer scientific or academic
references that support your assertion?

(I asserted that Access is a relational database because I am able to use it
that way. How are you using it?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[steve dassin]

Hello Jeff,

How does this provide assistance to the original poster?

It doesn't. I just decided to take license for a comment on a public forum

Aside from the reference to a weblog, can you offer scientific or academic
references that support your assertion?

Of course, history is on my side

Codd, E.F. (1970). "A Relational Codd, E.F. (1970).Model of Data for Large
Shared Data Banks".
Communications of the ACM 13 (6): 377-387.

Commentary by C. J. Date on Codds early relational papers:
The Birth of the Relational Model - Part 1
http://www.aisintl.com/case/library/Date_Birth of the Relational Model-1.html

The Birth of the Relational Model - Part 2
http://www.aisintl.com/case/library/Date_Birth of the Relational Model-2.html

The Birth of the Relational Model, Part 3 of 3
http://www.intelligententerprise.com/db_area/archives/1998/9812/online1.jhtml

Anything (book or article) by C. J. Date and/or Hugh Darwen. Examples
aplenty at:
www.thethirdmanifesto.com

The requirements for a system to be relational:
"Databases, Types, and The Relational Model: The Third Manifesto", 3rd
edition, Addison-Wesley, 2006
by C. J. Date and Hugh Darwen

Using a relational database:
www.dataphor.org

First and foremost understand that this is not personal. I am not a hoodlum
mvp/ms mindless basher found on too many public ms ngs. Real criticism
cannot be mindless but can be quite civil. And there is way too much bashing
and way too little criticism. Further, I have the highest regard for those
who have made themselves experts. But the database world of IT shares much
in common with the global economic meltdown. Users know as much about
relational databases as investors know about derivatives. Or voters know
about basic politics:
Americans Flunk Simple 3-Question Political Survey
http://www.livescience.com/culture/081015-political-news.html
(Only 18% knew the controlling party of the U.S. House of Representatives,
the U.S. secretary of state
and Great Britain's prime minister).

If a product has the SELECT, FROM and JOIN keywords it's a relationl system.
This is how IT thinks. This type of thinking turns IT into TI, totally
incompetent. So Access, Sql Server, MySql and Oracle are all relational
systems. If someone drinks a fifth of scotch and falls off a bar stool it
does not mean they are a physicist. A relational system has as much to do
with basic computer science as it does with the operations everyone is
familiar with. The "tables" that Access manipulates have no relationalship
to the "tables" that Codd was talking about. A table is first foremost a
VARIABLE and "table" is simply a TYPE just as 4 is a type of integer and can
be
assigned to a variable. The basic idea of all things in a database as a
variable with a specific type supporting assignment (X:=whatever) is the
foundation of a relational system. If you can use the '=' sign to compare
two integer variables why shouldn't you be able to use it to compare two
table variables? (The primitives of sql CRUD are not the same primitives for
a relational system.) And this simple idea remains a moving target for IT.
Such a system is distinctly different from "sql" systems everyone is
familiar with. There are many sql server experts who actually think that
because MS includes a UNION and JOIN and a relational system also supports
such operations that sql server is therefore a relational database. The best
erudite word I can think of to describle this logic is - crap. Programming
relationally is a different mindset that programming sql. The idea that both
advocate a declarative type of programming does not equate to equivalence.
There is no such idea as "thinking in terms of type" in sql as there is
relationally. I've
tried to give a taste of what relational programming is all about on my
blog.

I asserted that Access is a relational database because I am able to use
it that way.

Your good faith as a professional has not be rewarded. The vendors, the
whole industry has failed you. You have not been given the appropriate
knowledge nor tools. You have not be give the opportunity to use a
"relational" database. You have been given a loose
and expedient scripting language and led to believe (implicitly and
explicitly) that it was the same thing as what was rattling around in Codds
head. The idea that you can use an sql system relationally is..well...like
mating with a blowup doll Your assertion is an error of omission not
commission.

How are you using it?

I haven't used it for some time. I first started using it in the mid 90's.
The Rac utility for sql server (www.rac4sql.net) I wrote, especially for
crosstabs, was influenced by the simplicity of the Access crosstab query
(and reporting). I learned alot of sql from it and was a frequent ng
contributor (under many aliases. It's too bad MS didn't listen to me and
turn it into a relational system.

best,
steve

www.beyondsql.blogspot.com

 

[Jeff Boyce]

Thanks for taking the time to clarify and to provide references.

It will take some time to work them all.

(and I don't consider myself to have 'made myself an expert' -- I use the
product, found ideas and techniques that work and don't work, and offer
assistance/ideas to folks who are trying to use the product)

Regards

Jeff Boyce
Microsoft Office/Access MVP