Counting Hyperlinks

[Tired]

I started a database project to track the number modules completed by
faculty. There are 128 modules and I have them all in the table as
hyperlinks. Each hyperlink has a description of either 1 or 0, with
1=Completed and linked and 0=not completed. I want to count the number of 1's
using a query but the error I keep getting is that you cannot sum an OLE,
Hyperlink etc.. I tried to convert the hyperlink to a number using format in
the query but this did not work. Any ideas would be much appreciated. Thanks!

 

[a a r o n . k e m p f]

so store it as text!!

 

[Tired]

Yes, I suppose that would be the easy solution. However, then I need to
create another field for each module completed as a hyperlink and
unfortunately I only have 255 fields. It works in excel I'm not sure why it
wouldn't work in Access. Thanks anyways.

 

[John W. Vinson/MVP]

I started a database project to track the number modules completed by
faculty. There are 128 modules and I have them all in the table as
hyperlinks. Each hyperlink has a description of either 1 or 0, with
1=Completed and linked and 0=not completed. I want to count the number of 1's
using a query but the error I keep getting is that you cannot sum an OLE,
Hyperlink etc.. I tried to convert the hyperlink to a number using format in
the query but this did not work. Any ideas would be much appreciated. Thanks!

If you have 128 fields for the 128 hyperlinks... you're "committing
spreadsheet". This is a decent Excel design but it's simply wrong for
a relational database! A many (faculty member) to many (module)
relationship - each faculty member completes zero, one or many
modules, and each module is completed by zero, one or many faculty
members - needs *three* tables:

Faculty
PersonID <employee number, some unique ID for the person>
LastName
FirstName
<other biographical data>

Modules
ModuleID <Primary Key>
ModuleDef <hyperlink>
<any other info about the module itself>

Completion
PersonID <link to Faculty>
ModuleID <link to Modules>
CompletionDate <or other information about this person/this module>

A very simple Totals query will let you count completed modules; a
slightly more complex one will let you count uncompleted ones.

 

[a a r o n . k e m p f]

Access supports more columns than even Excel 2007 if you use Access
Data Projects

 

[a a r o n . k e m p f]

that 255 field limit has honestly been:

#1 reason to not use MS Access

Honestly-- 255 field limit is my # 1 reason to not use MS Access (and
the flakiness stacking queries on top of queries)

 

[Graham R Seach]

If you have all these modules in a single "wide" table then you really need
to think about how your database is designed. If I were you, I'd create a
table just for the modules, and record each module as separate line items.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Tired]

I will try this solution. Thank-you. I am a complete novice at table design
and databases. In fact, this is my first database. Does it show that much?
Thanks for all the suggestions.

 

[Tired]

I'll take a look at Access Data projects. This is good advice. Thank-you very
much for all the tips. Cheers.

 

[John W. Vinson/MVP]

I will try this solution. Thank-you. I am a complete novice at table design
and databases. In fact, this is my first database. Does it show that much?

<chuckle> You're in very good company. This is probably *the* most
common learning hurdle for folks to get over, especially for folks who
have some knowledge of spreadsheets.

 

[a a r o n . k e m p f]

I disagree.

If the baby sized database doesn't fit your needs-- don't make
excuses- move to SQL Server

 

[Graham R Seach]

This is Data Modelling 101, and it has nothing to do with databases. Don't
worry, Aaron, not everyone understands the difference between modelling and
implementation.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

message
I disagree.

If the baby sized database doesn't fit your needs-- don't make
excuses- move to SQL Server

 

[a a r o n . k e m p f]

No.. It's not data modelling 101.

Jet ****ing sucks.. specifically because it can't have more than 255
columns.

Sorry-- but those are the FACTS.

-Aaron

 

[a a r o n . k e m p f]

proper data modelling doesn't always mean 'over normalization'.

sorry

even MS Project almost has more than 250 fields. Honestly-- look into
it.

-Aaron

 

[a a r o n . k e m p f]

proper data modelling doesn't always mean 'over normalization'.

sorry

even MS Project almost has more than 250 fields. Honestly-- look into
it.

-Aaron

 

[Graham R Seach]

proper data modelling doesn't always mean 'over normalization'.
Well I certainly can't argue with a grandiose statement like that, however,
it has no constructive relevence here.
Even if your premise is sound, your argument is fallacious: 'argumentum ad
vericundiam'.

Aaron, I could understand your lack of knowledge if we were discussing a
highly advanced topic, but we're not. This is *very* basic stuff, and
despite your claims of being a professional developer, it's clear you don't
understand it. In this scenario a "module" is an entity and should obviously
be a relation in its own right. Like I said, "Data Modelling 101".

- Graham

message
proper data modelling doesn't always mean 'over normalization'.

sorry

even MS Project almost has more than 250 fields. Honestly-- look into
it.

-Aaron

 

[a a r o n . k e m p f]

it is very basic stuff. And we have a fundamental difference in
handling these problems.

you:
a) run away, redesign everything.. just because you're using a baby-
sized database and you're too stupid to know otherwise

me
a) use an 'ENTERPRISE READY DATABASE' (that is the same price as your
free database)

the 255 column / field limit-- in JET-- is _LITERALLY_ reason # 1 that
I graduated to SQL Server a decade ago.
There's no amount of questioning in the world that can make me UNDO
something that happened 9 years ago.

Sorry-- I was right then-- and I am right now-- I needed a database
with more than 255 columns, and Jet wouldn't give it to me.
So the _LOGICAL_ resolution is to move to a real database.

Yes, I was working in a denormalized manner.. but in the real world--
normalization isnt' always efficient.

I've worked in over-normalized schemas.. and I've worked in under-
normalized schemas.

but at no point is it worth it to 'redesign the schema just because
the database engine has a backwards limit on it'.

Not because 'I needed to normalize more' but because 'I needed a
database without limits'.

You are trying to make the dude feel bad for not normalizing more--
just because your piece of shit JET database can't handle more than
255 fields.
To me-- that sounds like SOUR GRAPES.

Seriously-- don't attack me just because I have a logical response to
the 255 field limit.
You are just too ****ing stupid to know when to use SQL Server.

SQL Server should always be used. and Jet should NEVER be used.

it's basic match.

SQL Server has -MORE- efficient datatypes than Jet.
SQL Server has -MORE- discrete datatypes than Jet.
SQL Server has -MORE- flexibility than Jet.
SQL Server has -MORE- powerful queries than Jet.
SQL Server has -MORE- performance than Jet.
SQL Server has -MORE- support for real-world database schemas than
Jet.


-Aaron

 

[a a r o n . k e m p f]

and again-- it's not 'lack of knowledge'.
I'm not talking about _THIS_ situation specifically-- I'm just saying
that sometimes it makes sense to have repeating columns of
information.
It just depends on what you're trying to do.

I just have the balls to question what I've been told.

Sometimes, it makes a lot of sense to have a VWDB (very wide
database).
SQL Server currently has the technology to work with VWDB.. and Jet
does not.

I think that this capability is _JUST_ as important as the ability to
deal with a VLDB (very large database).

Jet can compete in neither arena-- so why the **** do you sit around
and argue for the usage of jet--

You sit around and make excuses for why Jet is too ****ing stupid to
support more than 255 columns.
Don't blame the messenger-- don't blame the schema-- BLAME THE
DATABASE!!!