Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

newbie Q. calculations in access

Discussion in 'Information Technology' started by red6000, Jul 28, 2006.

  1. red6000

    red6000
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Newbie here, just wanted to introduce myself Interfaith Dialogues Jan 18, 2005

  3. lgalumbres@gmail.com

    lgalumbres@gmail.com
    Expand Collapse
    Guest

    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
     
  4. red6000

    red6000
    Expand Collapse
    Guest

    Thanks Lem,

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

    Cheers

    <lgalumbres@gmail.com> wrote in message
    news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    > 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

    >
     
  5. lgalumbres@gmail.com

    lgalumbres@gmail.com
    Expand Collapse
    Guest

    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
    >
    > <lgalumbres@gmail.com> wrote in message
    > news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    > > 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

    > >
     
  6. red6000

    red6000
    Expand Collapse
    Guest

    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.

    <lgalumbres@gmail.com> wrote in message
    news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    > 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
    >>
    >> <lgalumbres@gmail.com> wrote in message
    >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >> > 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
    >> >

    >
     
  7. red6000

    red6000
    Expand Collapse
    Guest

    wait, I think I may have got it!

    "red6000" <red1000002001@yahoo.com> wrote in message
    news:44b7cc0f$0$3552$ed2619ec@ptn-nntp-reader01.plus.net...
    > 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.
    >
    > <lgalumbres@gmail.com> wrote in message
    > news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    >> 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
    >>>
    >>> <lgalumbres@gmail.com> wrote in message
    >>> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >>> > 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
    >>> >

    >>

    >
    >
     
  8. red6000

    red6000
    Expand Collapse
    Guest

    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.


    <lgalumbres@gmail.com> wrote in message
    news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    > 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
    >>
    >> <lgalumbres@gmail.com> wrote in message
    >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >> > 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
    >> >

    >
     
  9. lgalumbres@gmail.com

    lgalumbres@gmail.com
    Expand Collapse
    Guest

    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.
    >
    >
    > <lgalumbres@gmail.com> wrote in message
    > news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    > > 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
    > >>
    > >> <lgalumbres@gmail.com> wrote in message
    > >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    > >> > 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
    > >> >

    > >
     
  10. red6000

    red6000
    Expand Collapse
    Guest

    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


    <lgalumbres@gmail.com> wrote in message
    news:1152906509.774897.230810@75g2000cwc.googlegroups.com...
    > 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.
    >>
    >>
    >> <lgalumbres@gmail.com> wrote in message
    >> news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    >> > 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
    >> >>
    >> >> <lgalumbres@gmail.com> wrote in message
    >> >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >> >> > 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
    >> >> >
    >> >

    >
     
  11. red6000

    red6000
    Expand Collapse
    Guest

    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" <red1000002001@yahoo.com> wrote in message
    news:44b801b3$0$69398$ed2619ec@ptn-nntp-reader01.plus.net...
    > 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
    >
    >
    > <lgalumbres@gmail.com> wrote in message
    > news:1152906509.774897.230810@75g2000cwc.googlegroups.com...
    >> 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.
    >>>
    >>>
    >>> <lgalumbres@gmail.com> wrote in message
    >>> news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    >>> > 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
    >>> >>
    >>> >> <lgalumbres@gmail.com> wrote in message
    >>> >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >>> >> > 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
    >>> >> >
    >>> >

    >>

    >
    >
     
  12. red6000

    red6000
    Expand Collapse
    Guest

    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" <red1000002001@yahoo.com> wrote in message
    news:44b915fd$0$3526$ed2619ec@ptn-nntp-reader01.plus.net...
    > 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" <red1000002001@yahoo.com> wrote in message
    > news:44b801b3$0$69398$ed2619ec@ptn-nntp-reader01.plus.net...
    >> 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
    >>
    >>
    >> <lgalumbres@gmail.com> wrote in message
    >> news:1152906509.774897.230810@75g2000cwc.googlegroups.com...
    >>> 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.
    >>>>
    >>>>
    >>>> <lgalumbres@gmail.com> wrote in message
    >>>> news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    >>>> > 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
    >>>> >>
    >>>> >> <lgalumbres@gmail.com> wrote in message
    >>>> >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >>>> >> > 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
    >>>> >> >
    >>>> >
    >>>

    >>
    >>

    >
    >
     
  13. lgalumbres@gmail.com

    lgalumbres@gmail.com
    Expand Collapse
    Guest

    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!

    red6000 wrote:
    > 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" <red1000002001@yahoo.com> wrote in message
    > news:44b915fd$0$3526$ed2619ec@ptn-nntp-reader01.plus.net...
    > > 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" <red1000002001@yahoo.com> wrote in message
    > > news:44b801b3$0$69398$ed2619ec@ptn-nntp-reader01.plus.net...
    > >> 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
    > >>
    > >>
    > >> <lgalumbres@gmail.com> wrote in message
    > >> news:1152906509.774897.230810@75g2000cwc.googlegroups.com...
    > >>> 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.
    > >>>>
    > >>>>
    > >>>> <lgalumbres@gmail.com> wrote in message
    > >>>> news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    > >>>> > 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
    > >>>> >>
    > >>>> >> <lgalumbres@gmail.com> wrote in message
    > >>>> >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    > >>>> >> > 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
    > >>>> >> >
    > >>>> >
    > >>>
    > >>
    > >>

    > >
    > >
     
  14. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    <lgalumbres@gmail.com> wrote in message
    news:1153140697.833031.83310@s13g2000cwa.googlegroups.com...
    > 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!
    >
    > red6000 wrote:
    >> 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" <red1000002001@yahoo.com> wrote in message
    >> news:44b915fd$0$3526$ed2619ec@ptn-nntp-reader01.plus.net...
    >> > 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" <red1000002001@yahoo.com> wrote in message
    >> > news:44b801b3$0$69398$ed2619ec@ptn-nntp-reader01.plus.net...
    >> >> 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
    >> >>
    >> >>
    >> >> <lgalumbres@gmail.com> wrote in message
    >> >> news:1152906509.774897.230810@75g2000cwc.googlegroups.com...
    >> >>> 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.
    >> >>>>
    >> >>>>
    >> >>>> <lgalumbres@gmail.com> wrote in message
    >> >>>> news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    >> >>>> > 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
    >> >>>> >>
    >> >>>> >> <lgalumbres@gmail.com> wrote in message
    >> >>>> >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >> >>>> >> > 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
    >> >>>> >> >
    >> >>>> >
    >> >>>
    >> >>
    >> >>
    >> >
    >> >

    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  15. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    <lgalumbres@gmail.com> wrote in message
    news:1153140697.833031.83310@s13g2000cwa.googlegroups.com...
    > 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!
    >
    > red6000 wrote:
    >> 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" <red1000002001@yahoo.com> wrote in message
    >> news:44b915fd$0$3526$ed2619ec@ptn-nntp-reader01.plus.net...
    >> > 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" <red1000002001@yahoo.com> wrote in message
    >> > news:44b801b3$0$69398$ed2619ec@ptn-nntp-reader01.plus.net...
    >> >> 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
    >> >>
    >> >>
    >> >> <lgalumbres@gmail.com> wrote in message
    >> >> news:1152906509.774897.230810@75g2000cwc.googlegroups.com...
    >> >>> 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.
    >> >>>>
    >> >>>>
    >> >>>> <lgalumbres@gmail.com> wrote in message
    >> >>>> news:1152822504.177142.141190@i42g2000cwa.googlegroups.com...
    >> >>>> > 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
    >> >>>> >>
    >> >>>> >> <lgalumbres@gmail.com> wrote in message
    >> >>>> >> news:1152819996.352245.134430@m73g2000cwd.googlegroups.com...
    >> >>>> >> > 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
    >> >>>> >> >
    >> >>>> >
    >> >>>
    >> >>
    >> >>
    >> >
    >> >

    >
     

Share This Page