Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2008
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    2007 front end db with linked tables; would like a count of records within each table - Table Name and records within that table.

    SELECT
    (Select Count(Process) From FES02AVTALL) As FY02AVT,
    (Select Count(Process) From FES02GRDALL) As FY02GRD,
    (Select Count(Process) From FES03AVTALL) As FY03AVT,
    (Select Count(Process) From FES03GRDALL) As FY03GRD
    From FES02AVTALL, FES02GRDALL, FES03AVTALL, FES03GRDALL;


    this format works but returns row/s of the same number. Just require one row with the answer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    A belated welcome to the Lounge!

    In what form or shape would you like the results?

  3. #3
    New Lounger
    Join Date
    Jun 2008
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786863' date='30-Jul-2009 18:47']A belated welcome to the Lounge!

    In what form or shape would you like the results?[/quote]


    Thanks,

    A comand button.

    Table 1, Table 2, Table 3
    101 , 100, 95

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Oops, you edited your post at the same time I was composing a reply.

    You could create and save separate queries:

    qryA:
    Select Count(Process) As FY02AVT From FES02AVTALL

    qryB:
    Select Count(Process) As FY02GRD From FES02GRDALL

    etc. When you have created such a query for each linked table, create a new query, add qryA, qryB etc. and add the single field from each of these queries to the query grid. This query will return one row, with a column for each table.

  5. #5
    New Lounger
    Join Date
    Jun 2008
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786867' date='30-Jul-2009 18:52']Oops, you edited your post at the same time I was composing a reply.

    You could create and save separate queries:

    qryA:
    Select Count(Process) As FY02AVT From FES02AVTALL

    qryB:
    Select Count(Process) As FY02GRD From FES02GRDALL

    etc. When you have created such a query for each linked table, create a new query, add qryA, qryB etc. and add the single field from each of these queries to the query grid. This query will return one row, with a column for each table.[/quote]


    Thanks Hans, will do - would be nice not to do all the queries.

    Sam

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Another option would be to create a union query that returns one row per table:

    SELECT "FES02AVTALL" AS TableName, Count(Process) As NumRecords FROM FES02AVTALL
    UNION SELECT "FES02AVTALL", Count(Process) FROM FES02GRDALL
    UNION SELECT "FES02AVTALL", Count(Process) FROM FES02AVTALL
    UNION SELECT "FES02AVTALL", Count(Process) FROM FES03GRDALL

    You wouldn't need to create a separate query per table. But the output is in a different format than you asked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •