Getting a Count for a specific cell

[Mike S.]

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this is my
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken. The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?

 

[KARL DEWEY]

Try this --
SELECT Consultants.NetID, Count(Consultants.NumCoverages) AS
CountOfNumCoverages
FROM Consultants
GROUP BY Consultants.NetID;

 

[vbasean]

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this ismy
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken.  The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?

If the number of covaerages are a sum of the linked table then run a
totals query by ID and sum the coverages

ir the number of coverages is a count of records then run a totals
query by ID and count the coverages field


create query

ad coverages table

view, totals

add consultantID (child field)
keep as "group by"

add coverages
change "group by" to either "sum" or "count" as neccessary

once you've created this query you can save it "qrycoveragesSum" or
qryCoveragesCount" and link it to the Consultants table

 

[standridgekelly]

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this ismy
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken.  The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?

That's probably not something you would want to store in a table, but
rather use a query to retrieve. I suspect that the NumCoverages will
change as Consultants add more coverages, drop coverages, etc. In
other words, it's going to be a variable number, so you wouldn't want
to store it in a table. Develop a query using both tables, and then
you'll always have an accurate and up-to-date count.
Kelly

 

[Mike S.]

Again, forgive my lackluster knowledge, but where am I putting this SQL
command? A query? Or somewhere else?

Try this --
SELECT Consultants.NetID, Count(Consultants.NumCoverages) AS
CountOfNumCoverages
FROM Consultants
GROUP BY Consultants.NetID;

--
KARL DEWEY
Build a little - Test a little

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this is my
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken. The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?

 

[vbasean]

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this is my
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken.  The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?

If the number of covaerages are a sum of the linked table then run a
totals query by ID and sum the coverages

ir the number of coverages is a count of records then run a totals
query by ID and count the coverages field

create query

ad coverages table

view, totals

<b> add consultantID (child field)<b/>
keep as "group by"

add coverages
change "group by" to either "sum" or "count" as neccessary

once you've created this query you can save it "qrycoveragesSum" or
qryCoveragesCount" and link it to the Consultants table- Hide quoted text -

- Show quoted text -

I guess <b>consultant ID<b/> should be <b> NetID <b/>

 

[Mike S.]

So I guess it's not possible to have the query update the entry in the
corresponding table?

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this is my
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken. The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?

That's probably not something you would want to store in a table, but
rather use a query to retrieve. I suspect that the NumCoverages will
change as Consultants add more coverages, drop coverages, etc. In
other words, it's going to be a variable number, so you wouldn't want
to store it in a table. Develop a query using both tables, and then
you'll always have an accurate and up-to-date count.
Kelly

 

[KARL DEWEY]

Create a new query. Open in design view, click on menu VIEW - SQL View. Copy
the post into the SQL window. Edit the SELECT line to remove any returns the
pasting/posting may have inserted after the 'AS' so that the SELECT statement
is on one line.
Save, close, & run.
--
KARL DEWEY
Build a little - Test a little

Again, forgive my lackluster knowledge, but where am I putting this SQL
command? A query? Or somewhere else?

Try this --
SELECT Consultants.NetID, Count(Consultants.NumCoverages) AS
CountOfNumCoverages
FROM Consultants
GROUP BY Consultants.NetID;

--
KARL DEWEY
Build a little - Test a little

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this is my
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken. The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?

 

[Mike S.]

Thanks so much! A modified version of your SQL query did it!

SELECT Consultants.NetID, Count(Coverages.CoveredBy) AS CountOfCoverages
FROM Coverages
WHERE Coverages.CoveredBy=Consultants.NetID
GROUP BY Consultants.NetID;

Create a new query. Open in design view, click on menu VIEW - SQL View. Copy
the post into the SQL window. Edit the SELECT line to remove any returns the
pasting/posting may have inserted after the 'AS' so that the SELECT statement
is on one line.
Save, close, & run.
--
KARL DEWEY
Build a little - Test a little

Again, forgive my lackluster knowledge, but where am I putting this SQL
command? A query? Or somewhere else?

Try this --
SELECT Consultants.NetID, Count(Consultants.NumCoverages) AS
CountOfNumCoverages
FROM Consultants
GROUP BY Consultants.NetID;

--
KARL DEWEY
Build a little - Test a little


:

OK, I am not sure how to explain this, but I hope you'll be able to answer
this.

I have an access database that I'm working on, to try and learn about the
application and it's uses (I normally use pre-developed databases, this is my
first attempt at writing my own).

I have two tables in this database: Consultants [Fields are NetID(Text),
ConsFirst(Text), ConsLast(Text), NumCoverages(Number)] and Coverages [Fields
are CoverageID(AutoNumber for Tracking Purposes), ShiftDate (Date),
ShiftTime(Time), ShiftEnd(Time), CoveredBy(Text, pulling from the Consultants
field NetID).

Now, the problem is that I would like the NumCoverages field in Consultants
table to report a count of the number of coverages per NetID that someone has
taken. The data is available in the Coverages table, but I do not know how
to go about getting the data. :\

Any ideas, or recommendations as to what to do?