newbie Q. calculations in access

  • Thread starter Thread starter red6000
  • Start date Start date
R

red6000

Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the number of times
each name appears.

What I would like is to able to perform a calculation with the Query results
and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear in the DATA
table. What I would like is not the number of times that the name appears
but 7 minus the number of times it appears. So if A appears 5 times I want
the result to show as 2.

Is this possible? My current query doesn't cross refer to the NAMES table to
count names so doesn't show names that don't appear in the table which I
also need to get around.

Thanks
 
Hi,

If I understood you correctly this should work....

SELECT NAMES.Names, (7-(Count(DATA.Names))) AS CountOfNames
FROM NAMES LEFT JOIN DATA ON NAMES.Names=DATA.Names
GROUP BY NAMES.Names;

Hope that helps!
- Lem
 
Open your Access database. On the left hand side under "Objects" pane
click on "Queries". Then click the "Create query in Design view" in
the middle pane.

A "Show Table" window will open. Click the close button to close the
"Show Table" window. Now you should be in the Query By Example window.


Right-Click on the top pane of the Query By Example window and choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so they
may not match whats is in your database.

Good luck!
 
Thanks Lem,

I've tried what you said and I get a syntax error in JOIN Operation.

My actual code is:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSR_Name))) AS CountOfNames
FROM CSRNAMES LEFT JOIN DATA ON CSRNAMES.CSRNames=QAS.CSR_Name
GROUP BY CSRNAMES.CSRNames;

Any clues?

Thanks for your help.
 
wait, I think I may have got it!

red6000 said:
Thanks Lem,

I've tried what you said and I get a syntax error in JOIN Operation.

My actual code is:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSR_Name))) AS CountOfNames
FROM CSRNAMES LEFT JOIN DATA ON CSRNAMES.CSRNames=QAS.CSR_Name
GROUP BY CSRNAMES.CSRNames;

Any clues?

Thanks for your help.
Click to expand...
 
Great I've now got that to work, what I would also like to do thou is only
return the answers if the date in a column is July (the date is in format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.
 
Hi,

Glad you got the previous one to work.

For your other inquery, try..

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, [QAS].[Date case worked by CSR]
HAVING ((Month([QAS].[Date case worked by CSR])=7))

Cheers!
- Lem
Great I've now got that to work, what I would also like to do thou is only
return the answers if the date in a column is July (the date is in format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.


Open your Access database. On the left hand side under "Objects" pane
click on "Queries". Then click the "Create query in Design view" in
the middle pane.

A "Show Table" window will open. Click the close button to close the
"Show Table" window. Now you should be in the Query By Example window.


Right-Click on the top pane of the Query By Example window and choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so they
may not match whats is in your database.

Good luck!
Click to expand...
Click to expand...
 
Thanks for all the help Lem, but using that only returns the list of names
that appear in QAS table and doesn't include the names in the CSRNames
table.

Cheers again


Hi,

Glad you got the previous one to work.

For your other inquery, try..

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, [QAS].[Date case worked by CSR]
HAVING ((Month([QAS].[Date case worked by CSR])=7))

Cheers!
- Lem
Great I've now got that to work, what I would also like to do thou is
only
return the answers if the date in a column is July (the date is in format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.


Open your Access database. On the left hand side under "Objects" pane
click on "Queries". Then click the "Create query in Design view" in
the middle pane.

A "Show Table" window will open. Click the close button to close the
"Show Table" window. Now you should be in the Query By Example window.


Right-Click on the top pane of the Query By Example window and choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so they
may not match whats is in your database.

Good luck!

red6000 wrote:
Thanks Lem,

I'm new to access, so where abouts do I put this?

Cheers

Hi,

If I understood you correctly this should work....

SELECT NAMES.Names, (7-(Count(DATA.Names))) AS CountOfNames
FROM NAMES LEFT JOIN DATA ON NAMES.Names=DATA.Names
GROUP BY NAMES.Names;

Hope that helps!
- Lem

red6000 wrote:
Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the number of
times
each name appears.

What I would like is to able to perform a calculation with the
Query
results
and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear in
the
DATA
table. What I would like is not the number of times that the name
appears
but 7 minus the number of times it appears. So if A appears 5 times
I
want
the result to show as 2.

Is this possible? My current query doesn't cross refer to the NAMES
table
to
count names so doesn't show names that don't appear in the table
which
I
also need to get around.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Still stuggling to get this one to work.

I csn do it by breaking it down into 2 queries, but not as one query.

Thanks for the help.


red6000 said:
Thanks for all the help Lem, but using that only returns the list of names
that appear in QAS table and doesn't include the names in the CSRNames
table.

Cheers again


Hi,

Glad you got the previous one to work.

For your other inquery, try..

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, [QAS].[Date case worked by CSR]
HAVING ((Month([QAS].[Date case worked by CSR])=7))

Cheers!
- Lem
Great I've now got that to work, what I would also like to do thou is
only
return the answers if the date in a column is July (the date is in
format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.


Open your Access database. On the left hand side under "Objects" pane
click on "Queries". Then click the "Create query in Design view" in
the middle pane.

A "Show Table" window will open. Click the close button to close the
"Show Table" window. Now you should be in the Query By Example
window.


Right-Click on the top pane of the Query By Example window and choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so they
may not match whats is in your database.

Good luck!

red6000 wrote:
Thanks Lem,

I'm new to access, so where abouts do I put this?

Cheers

Hi,

If I understood you correctly this should work....

SELECT NAMES.Names, (7-(Count(DATA.Names))) AS CountOfNames
FROM NAMES LEFT JOIN DATA ON NAMES.Names=DATA.Names
GROUP BY NAMES.Names;

Hope that helps!
- Lem

red6000 wrote:
Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the number of
times
each name appears.

What I would like is to able to perform a calculation with the
Query
results
and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear in
the
DATA
table. What I would like is not the number of times that the name
appears
but 7 minus the number of times it appears. So if A appears 5
times I
want
the result to show as 2.

Is this possible? My current query doesn't cross refer to the
NAMES
table
to
count names so doesn't show names that don't appear in the table
which
I
also need to get around.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
PS should have posted my 2 SQLs. Just looking for a way to combine the 2
queries into 1:

QUERY1 CALLED 'CSR Background count'
SELECT QAs.CSRName, QAs.[WFI Number]
FROM QAs
WHERE (((Month([Date case worked by CSR]))=[Please choose month, enter
1-12]));
================================
QUERY2 CALLED 'CSRCOUNT'
SELECT CSRNAMES.CSRNames, (7-(Count([CSR Background count].CSRName))) AS
Audits_Remaining
FROM CSRNAMES LEFT JOIN [CSR Background count] ON CSRNAMES.CSRNames = [CSR
Background count].CSRName
ORDER BY (7-(Count([CSR Background count].CSRName))) DESC;

Cheers

red6000 said:
Still stuggling to get this one to work.

I csn do it by breaking it down into 2 queries, but not as one query.

Thanks for the help.


red6000 said:
Thanks for all the help Lem, but using that only returns the list of
names that appear in QAS table and doesn't include the names in the
CSRNames table.

Cheers again


Hi,

Glad you got the previous one to work.

For your other inquery, try..

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, [QAS].[Date case worked by CSR]
HAVING ((Month([QAS].[Date case worked by CSR])=7))

Cheers!
- Lem

red6000 wrote:
Great I've now got that to work, what I would also like to do thou is
only
return the answers if the date in a column is July (the date is in
format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.


Open your Access database. On the left hand side under "Objects"
pane
click on "Queries". Then click the "Create query in Design view" in
the middle pane.

A "Show Table" window will open. Click the close button to close the
"Show Table" window. Now you should be in the Query By Example
window.


Right-Click on the top pane of the Query By Example window and choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so they
may not match whats is in your database.

Good luck!

red6000 wrote:
Thanks Lem,

I'm new to access, so where abouts do I put this?

Cheers

Hi,

If I understood you correctly this should work....

SELECT NAMES.Names, (7-(Count(DATA.Names))) AS CountOfNames
FROM NAMES LEFT JOIN DATA ON NAMES.Names=DATA.Names
GROUP BY NAMES.Names;

Hope that helps!
- Lem

red6000 wrote:
Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the number
of
times
each name appears.

What I would like is to able to perform a calculation with the
Query
results
and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear in
the
DATA
table. What I would like is not the number of times that the name
appears
but 7 minus the number of times it appears. So if A appears 5
times I
want
the result to show as 2.

Is this possible? My current query doesn't cross refer to the
NAMES
table
to
count names so doesn't show names that don't appear in the table
which
I
also need to get around.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Ok, you should use an INNER JOIN instead of a LEFT JOIN to show results
from both tables where "Names" equal. So the query would be:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES INNER JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, ((Month([QAS].[Date case worked by
CSR])=7))
HAVING ((Month([QAS].[Date case worked by CSR])=7))

This will give you a count of names with month of July.

Cheers!
PS should have posted my 2 SQLs. Just looking for a way to combine the 2
queries into 1:

QUERY1 CALLED 'CSR Background count'
SELECT QAs.CSRName, QAs.[WFI Number]
FROM QAs
WHERE (((Month([Date case worked by CSR]))=[Please choose month, enter
1-12]));
================================
QUERY2 CALLED 'CSRCOUNT'
SELECT CSRNAMES.CSRNames, (7-(Count([CSR Background count].CSRName))) AS
Audits_Remaining
FROM CSRNAMES LEFT JOIN [CSR Background count] ON CSRNAMES.CSRNames = [CSR
Background count].CSRName
ORDER BY (7-(Count([CSR Background count].CSRName))) DESC;

Cheers

red6000 said:
Still stuggling to get this one to work.

I csn do it by breaking it down into 2 queries, but not as one query.

Thanks for the help.


red6000 said:
Thanks for all the help Lem, but using that only returns the list of
names that appear in QAS table and doesn't include the names in the
CSRNames table.

Cheers again


Hi,

Glad you got the previous one to work.

For your other inquery, try..

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, [QAS].[Date case worked by CSR]
HAVING ((Month([QAS].[Date case worked by CSR])=7))

Cheers!
- Lem

red6000 wrote:
Great I've now got that to work, what I would also like to do thou is
only
return the answers if the date in a column is July (the date is in
format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.


Open your Access database. On the left hand side under "Objects"
pane
click on "Queries". Then click the "Create query in Design view" in
the middle pane.

A "Show Table" window will open. Click the close button to close the
"Show Table" window. Now you should be in the Query By Example
window.


Right-Click on the top pane of the Query By Example window and choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so they
may not match whats is in your database.

Good luck!

red6000 wrote:
Thanks Lem,

I'm new to access, so where abouts do I put this?

Cheers

Hi,

If I understood you correctly this should work....

SELECT NAMES.Names, (7-(Count(DATA.Names))) AS CountOfNames
FROM NAMES LEFT JOIN DATA ON NAMES.Names=DATA.Names
GROUP BY NAMES.Names;

Hope that helps!
- Lem

red6000 wrote:
Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the number
of
times
each name appears.

What I would like is to able to perform a calculation with the
Query
results
and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear in
the
DATA
table. What I would like is not the number of times that the name
appears
but 7 minus the number of times it appears. So if A appears 5
times I
want
the result to show as 2.

Is this possible? My current query doesn't cross refer to the
NAMES
table
to
count names so doesn't show names that don't appear in the table
which
I
also need to get around.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
also substitute having for where (speed-up) as having is only applied after
the total amount of data is processed

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES INNER JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE Month([QAS].[Date case worked by CSR])=7
GROUP BY CSRNAMES.CSRNames

ie in the GUI always add the restriction a seccond time (if you want the
data displayed) and/or set it's "group by property" to where

Pieter

Hi,

Ok, you should use an INNER JOIN instead of a LEFT JOIN to show results
from both tables where "Names" equal. So the query would be:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES INNER JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, ((Month([QAS].[Date case worked by
CSR])=7))
HAVING ((Month([QAS].[Date case worked by CSR])=7))

This will give you a count of names with month of July.

Cheers!
PS should have posted my 2 SQLs. Just looking for a way to combine the 2
queries into 1:

QUERY1 CALLED 'CSR Background count'
SELECT QAs.CSRName, QAs.[WFI Number]
FROM QAs
WHERE (((Month([Date case worked by CSR]))=[Please choose month, enter
1-12]));
================================
QUERY2 CALLED 'CSRCOUNT'
SELECT CSRNAMES.CSRNames, (7-(Count([CSR Background count].CSRName))) AS
Audits_Remaining
FROM CSRNAMES LEFT JOIN [CSR Background count] ON CSRNAMES.CSRNames =
[CSR
Background count].CSRName
ORDER BY (7-(Count([CSR Background count].CSRName))) DESC;

Cheers

red6000 said:
Still stuggling to get this one to work.

I csn do it by breaking it down into 2 queries, but not as one query.

Thanks for the help.


Thanks for all the help Lem, but using that only returns the list of
names that appear in QAS table and doesn't include the names in the
CSRNames table.

Cheers again


Hi,

Glad you got the previous one to work.

For your other inquery, try..

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, [QAS].[Date case worked by CSR]
HAVING ((Month([QAS].[Date case worked by CSR])=7))

Cheers!
- Lem

red6000 wrote:
Great I've now got that to work, what I would also like to do thou
is
only
return the answers if the date in a column is July (the date is in
format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.


Open your Access database. On the left hand side under "Objects"
pane
click on "Queries". Then click the "Create query in Design view"
in
the middle pane.

A "Show Table" window will open. Click the close button to close
the
"Show Table" window. Now you should be in the Query By Example
window.


Right-Click on the top pane of the Query By Example window and
choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so
they
may not match whats is in your database.

Good luck!

red6000 wrote:
Thanks Lem,

I'm new to access, so where abouts do I put this?

Cheers

Hi,

If I understood you correctly this should work....

SELECT NAMES.Names, (7-(Count(DATA.Names))) AS CountOfNames
FROM NAMES LEFT JOIN DATA ON NAMES.Names=DATA.Names
GROUP BY NAMES.Names;

Hope that helps!
- Lem

red6000 wrote:
Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the
number
of
times
each name appears.

What I would like is to able to perform a calculation with the
Query
results
and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear
in
the
DATA
table. What I would like is not the number of times that the
name
appears
but 7 minus the number of times it appears. So if A appears 5
times I
want
the result to show as 2.

Is this possible? My current query doesn't cross refer to the
NAMES
table
to
count names so doesn't show names that don't appear in the
table
which
I
also need to get around.

Thanks
Click to expand...
Click to expand...
Click to expand...



--
 
also substitute having for where (speed-up) as having is only applied after
the total amount of data is processed

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES INNER JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE Month([QAS].[Date case worked by CSR])=7
GROUP BY CSRNAMES.CSRNames

ie in the GUI always add the restriction a seccond time (if you want the
data displayed) and/or set it's "group by property" to where

Pieter

Hi,

Ok, you should use an INNER JOIN instead of a LEFT JOIN to show results
from both tables where "Names" equal. So the query would be:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES INNER JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, ((Month([QAS].[Date case worked by
CSR])=7))
HAVING ((Month([QAS].[Date case worked by CSR])=7))

This will give you a count of names with month of July.

Cheers!
PS should have posted my 2 SQLs. Just looking for a way to combine the 2
queries into 1:

QUERY1 CALLED 'CSR Background count'
SELECT QAs.CSRName, QAs.[WFI Number]
FROM QAs
WHERE (((Month([Date case worked by CSR]))=[Please choose month, enter
1-12]));
================================
QUERY2 CALLED 'CSRCOUNT'
SELECT CSRNAMES.CSRNames, (7-(Count([CSR Background count].CSRName))) AS
Audits_Remaining
FROM CSRNAMES LEFT JOIN [CSR Background count] ON CSRNAMES.CSRNames =
[CSR
Background count].CSRName
ORDER BY (7-(Count([CSR Background count].CSRName))) DESC;

Cheers

red6000 said:
Still stuggling to get this one to work.

I csn do it by breaking it down into 2 queries, but not as one query.

Thanks for the help.


Thanks for all the help Lem, but using that only returns the list of
names that appear in QAS table and doesn't include the names in the
CSRNames table.

Cheers again


Hi,

Glad you got the previous one to work.

For your other inquery, try..

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
GROUP BY CSRNAMES.CSRNames, [QAS].[Date case worked by CSR]
HAVING ((Month([QAS].[Date case worked by CSR])=7))

Cheers!
- Lem

red6000 wrote:
Great I've now got that to work, what I would also like to do thou
is
only
return the answers if the date in a column is July (the date is in
format
dd/mm/yyy).

I've tried the following but it returns no results at all:

SELECT CSRNAMES.CSRNames, (7-(Count(QAS.CSRName))) AS CountOfNames
FROM CSRNAMES LEFT JOIN QAS ON CSRNAMES.CSRNames=QAS.CSRName
WHERE (((Month([QAS.Date case worked by CSR]))=7))
GROUP BY CSRNAMES.CSRNames;

thanks for all the help.


Open your Access database. On the left hand side under "Objects"
pane
click on "Queries". Then click the "Create query in Design view"
in
the middle pane.

A "Show Table" window will open. Click the close button to close
the
"Show Table" window. Now you should be in the Query By Example
window.


Right-Click on the top pane of the Query By Example window and
choose
SQL View..

Copy and paste code in there.

Note that the field names in the select statement are made up so
they
may not match whats is in your database.

Good luck!

red6000 wrote:
Thanks Lem,

I'm new to access, so where abouts do I put this?

Cheers

Hi,

If I understood you correctly this should work....

SELECT NAMES.Names, (7-(Count(DATA.Names))) AS CountOfNames
FROM NAMES LEFT JOIN DATA ON NAMES.Names=DATA.Names
GROUP BY NAMES.Names;

Hope that helps!
- Lem

red6000 wrote:
Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the
number
of
times
each name appears.

What I would like is to able to perform a calculation with the
Query
results
and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear
in
the
DATA
table. What I would like is not the number of times that the
name
appears
but 7 minus the number of times it appears. So if A appears 5
times I
want
the result to show as 2.

Is this possible? My current query doesn't cross refer to the
NAMES
table
to
count names so doesn't show names that don't appear in the
table
which
I
also need to get around.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - retrieve data on last create named range from previous inserted named range 0
First Names a little different 5
Newbie Help - Probably a simple query 3
newbie Q: calcing %'s 7
Sumproductlike in Access 1
Skip printing selected records 14
Turning a 3 column flat file into a "3D" Table 2
Access Dcount (multiple criteria) 3

Back
Top