COUNT(*) From Multiple Tables - Query Issue - Please Help!!

May 6, 2006

Hi,
I am building a small sales application where I would like to get the number of opportunities created in multiple regions. The QUERY that I am using in MS Access is as follows:
-----------------------------------------------------------
SELECT OPPORTUNITY.OPP_CREATION_MONTH AS [MONTH], Count(*) AS TOTAL,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG1') AS Expr1,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG2') AS Expr2,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG3') AS Expr3,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG4') AS Expr4
FROM OPPORTUNITY
WHERE (((OPPORTUNITY.OPP_CREATION_YEAR)=2006))
GROUP BY OPPORTUNITY.OPP_CREATION_MONTH;
-----------------------------------------------------------

What I am trying to do is get the total opportunity count for each month, followed by, the total opportunity count for the REG1 for that month, the total opportunity count for the REG2 for that month,the total opportunity count for the REG3 for that month,the total opportunity count for the REG4 for that month.

Expected output should be something like this:

Month | TOTAL | REG1 | REG2 | REG3 | REG4
--------------------------------------------
Jan 11 8 1 1 1
Feb 4 1 1 1 1

However, I am not getting the above result. In the results that are displayed the "TOTAL" column is correct but under the "REG" columns the same number keep repeating in all rows. Any help will be appreciated.

Thanks.

View Replies


ADVERTISEMENT

Queries :: How To Count Records Based On Multiple Criteria From Multiple Tables

Jan 4, 2014

I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".

View 4 Replies View Related

Multiple Count Rows, Multiple Tables

Feb 19, 2008

I want to count based on 1 (same) field from each of two tables, based on if ID is in "X".

select a.groupid, count(a.id), count(b.id)
from ta a, tb b
where value in ('a','b','c')
group by a.groupid

Thats sort of the psuedocode but Im not getting the right results.

Any help would be appreciated.

View 6 Replies View Related

Count: Multiple Tables

Jun 20, 2005

Hi all!

What I need is propably easy to do. I have a patient information database, where patient information, visits, treatments etc. are on separate tables. One patient can have several visits and one visit can include several different treatments.

At this point I need to count how many treatments (treatment instances) each doctor has given (= a report "Treatments per doctor").

I have (at the moment) three (3) tables, which contain information of three different types of treatments. The tables are different, but each has a similar field with the doctors ID, and an autonumber field for each treatment instance.

tblTreatmentXX:
treatmentXXID, visitID, doctorID, (+ treatment-specific fields)

Now, I know how to count how many times each doctor has given a treatment *from one table* (group by the doctor's ID and then count the treatment instance IDs), but how do I combine the information from three different tables?

All help is greatly appreciated!

- Garcanrya

View 2 Replies View Related

Trying To Get A Count Report From Multiple Tables

Jul 4, 2015

I am trying to generate a count report from a multi-table Access database. It is a recording database with a songs in one table, recordings in a second table, and a third table that links the two. I am trying to generate a report of songs with the most numerous recordings in the database.

For this operation, I don't need the recordings table. The main question is: For each title in the songs table, how many records are there in the links table with key number <50000? More specifically, for the report, I only care about the ones with a count >= 10.

Ideally, my report would look something like this:

65 Moon River
58 White Christmas
43 Autumn Leaves
42 Way You Look Tonight, The
etc.

As a corollary to this, how would I add a current, dynamic count to the form I use to access the database. Whenever a song title is pulled up, a field would show the current count of records in the links table with key number <50000.

View 1 Replies View Related

Queries :: Getting A Text Field Count From Multiple Tables

May 16, 2013

I am working with 4 tables and I am trying to get a count of the email address field in each table grouped by Email Group Name. I tried the dcount function but returned an error because some of the email addresses have an apostrophe in the field. Is there a way to do this in 1 query? This is an example of the result I would like:

EmailGroup/TtlEmailSents/TtlEmailsOpened/TtlEmailsClicked/TtlFormSubmits
AAA 50 30 15 10
BBB 100 75 50 20
CCC 60 25 5 1

Tables:
Emails Sent
Emails Opened
Emails Clicked
Form Submits

Field Names in all tables:
Email Group Name
Email Address

Field I want to count is Email Address for each table, grouped by Email Group Name.

View 5 Replies View Related

Tables :: Splitting Table Into Multiple Sets Based On Row Count

Oct 5, 2012

I want to split a table into multiple sets based on rowcount. Suppose I have a table having 10,000 records. I want different sets which should have values based on rowcount. Suppose if I select set 1 then the table should populate records from 1-2500. If I select set 2 then the table should automatically give the records from 2501-5000. If i select set3 then the table should have values from 5001-7500 and so on.

View 3 Replies View Related

Append Query, Multiple Tables To Multiple Tables In Another Database

Nov 29, 2007

Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?

Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...

View 4 Replies View Related

Queries :: Count Query From Two Tables

Nov 5, 2013

I have TableA with

CityCode (indexed, no duplicates, Primary Key)
CityType (duplicates ok)

TableB, with
CityCode (indexed, duplicates OK)
StreetCode (indexed, No Duplicates, Primary Key)

A One to Many relation between Tables on City Code

In need to feed a cell table with the value of the number of streets that are in "Type1" cities

View 5 Replies View Related

Multiple Fields Of Multiple Tables To One Table Query Or Report

Apr 12, 2013

I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?

View 1 Replies View Related

Multiple Tables And 1 Query

Apr 24, 2005

Hello all:

I have multiple tables in my access db, the structure of the tables (4 in all) are exactly the same except that the billing dates are for year 2001 to 2004 i.e. separate table for each of the years.

I don't want to copy the information in these 4 tables into 1 table because I have over 2 million rows in each table. However, I want to be able to run 1 query that will provide information from all 4 tables. E.g. filtering for billing dates between 2001 to 2004 for a particular customer.

How do I join the tables together so that I can run a query on all 4 tables at one time.

Thanks for your input.

Odun

View 2 Replies View Related

Query From Multiple Tables

Mar 31, 2006

i have an excel worksheet with 24 similar sheets : a,b,c,d, aso
the sheets contains similar data: nr, firm, invoice, sum, payment date, etc
i linked them to access creating tables : a,b,c,d, aso
i must create a query based on them to sort the data in the tables ascending by the due payment date (query based on multiple tables)

i've done this for 2 tables:

SELECT *
FROM a, b
WHERE (((a.SCADENTA)<=[due payment date]) And ((b.SCADENTA)<=[due payment date]));

but it multiplies the data by 2 or many times in the resulting table.
what is wrong?

View 3 Replies View Related

Query On Multiple Tables

May 31, 2007

Here is a some what complicated question dealing with queries.

I have several tables with data and every month I create a new table with similar data. I am trying to build a query to keep track of certain statistics with in the monthly tables. However I do not want to have to update query each month linking the tables together. What I really would like to do is have a table with the names of each of the tables, which I could up date easily, which links the query to each table to determine the statistics. If this is making any sense, I would appreciate the help.

Thanks!
Mike

View 2 Replies View Related

Query - JOIN Multiple Tables

Jun 11, 2006

Hello All;

Could someone please assist me on this?

The following is the Tables:

ContentTypes ---> Site Content Directory
Cats ---> Categories
Sierra ---> Store Content for Sierra
BrushStrokes ---> Store Content for BS

I have "tried" to create a QUERY for the 4 Tables
(The code that I have, originally came with 3-Table Query connection
I have added in the 4th Table "BrushStrokes")

This is the Query that I created. Before the code, a little insite

At first there was: RIGHT & LEFT JOIN's, But I would receive this:
Error 3258
After not really finding any good examples of how to create the
2- Queries and have then work together as one.
I decided to try something else, which was to create all [INNER JOIN]'s
Which got rid of the Error, and the code still will not work in my site.
========
Microsoft JET Database Engine error '80040e14'
Syntax error in JOIN operation.
=======

So, here is the code, please let me know what is wrong with it?
And if possible, a good example of creating the [2 - Queries] that work as one.

Thank you all
Carrzkiss
-----------------------SQL QUERY------------------
SELECT Cats.CatID, Sierra.CatID, BrushStrokes.CatID, Sierra.ContentID, BrushStrokes.BSContentID, Sierra.Product_Title, BrushStrokes.BSProduct_Name, Cats.CatDescription, Cats.CatTypeId, Sierra.Product_Th_Image, BrushStrokes.BSThumbnail, Sierra.Our_Price, BrushStrokes.BSPrice, Sierra.Retail_Price, BrushStrokes.BSRetail, Sierra.Aff_Name, BrushStrokes.Aff_Name, Sierra.Brand_Name, BrushStrokes.BSArtistName, Sierra.YAvailable, BrushStrokes.BSStatus, Sierra.Percent_Savings, Sierra.Unique_Product_ID, BrushStrokes.BSProductID, Sierra.Main_Cat, BrushStrokes.BSCol_Cat, ContentTypes.ContentType, Sierra.Product_Description, BrushStrokes.BSDescription
FROM ((ContentTypes INNER JOIN Sierra ON ContentTypes.ContentTypeID = Sierra.ContentTypeID) INNER JOIN BrushStrokes ON ContentTypes.ContentTypeID = BrushStrokes.ContentTypeID) INNER JOIN Cats ON (BrushStrokes.CatID = Cats.CatID) AND (Sierra.CatID = Cats.CatID)
WHERE (((Sierra.Display)=1) AND ((BrushStrokes.Display)=1));
-----------------------------END SQL QUERY------------------

View 3 Replies View Related

Access Multiple Tables Query

Oct 6, 2006

this is a tricky one.
Basically I have 3 tables

country
state
city

relationship:

1:m -> country:state
1:m -> state:city

I want to get all the country, state and cities. SELECT * FROM country, state, city

thats fine. But I only want to show unique values, in other words, just want to show unique country values, not duplicates. How can I do this?

View 1 Replies View Related

How To Clean Out Multiple Tables With One Query?

Mar 2, 2007

Is their a way to edit the following query so that it will delete from more than one table?

DELETE FROM tblSample

Or do I need to write a seperate query for each table I want to delete?

View 4 Replies View Related

Query Accross Multiple Tables

Jul 13, 2007

Not sure if this belongs in Queries or Tables or Forms, but I'm realy stuck on this one. I've been quite verbose in my question, so please stick with me on this, but I'm trying to make sure you helpful souls have all the information you need.

The premise, is as follows:

Master table recording general appointment details (time place etc)

For each appointment we have a number of tests that need to be performed. What I have been asked to provide is the following:

For each appointment there must be one or many tests.

For each test, there must be 1 or many sub tests.

The test number is just an integer

Each SubTest has a code, description, price. The price for each test is date dependant. The operator should just be able to enter the sub test code and autopopulate the remaining fields.

So, I want to add a subform in a subform to the main appointment form.

The tables are as follows (Excluding appointment table):

tbl_Test
Test_Number_PK (AutoCounter)
Appointment_Number_FK
Test_Comments

tbl_link_Test_to_Sub_Test
Test_Number_PK (Composite Primary Key)
Test_Code_PK

tbl_Test_Code
Test_Code_PK
Test_Desc

tbl_Test_Code_Price
Test_Code_PK
Price_Date_PK
Price

Now, as the price is dative I have a query on a query (both select queries) to get the prices as of the appointment date. This works fine:

First Filter:

SELECT tbl_Test_Code_Price.Test_Code_PK, tbl_Test_Code_Price.Price_Date_PK, tbl_Test_Code_Price.Price, tbl_Test_Code.Test_Desc
FROM tbl_Test_Code INNER JOIN tbl_Test_Code_Price ON tbl_Test_Code.Test_Code_PK = tbl_Test_Code_Price.Test_Code_PK
WHERE (((tbl_Test_Code_Price.Price_Date_PK)>=#1/1/2007#));

**Note I've just set the Where statement as a dummy value in order to test**

Second Filter:

SELECT qry_first_filter.Test_Code_PK, Max(qry_first_filter.Price_Date_PK) AS MaxOfPrice_Date, qry_first_filter.Price, qry_first_filter.Test_Desc
FROM qry_first_filter
GROUP BY qry_first_filter.Test_Code_PK, qry_first_filter.Price, qry_first_filter.Test_Desc;


I've added the link table to get over the many to many relationship I have between Tests and Test Codes.

As soon as I try and link the tables or tables + query together for the sub sub form (i.e. the test codes data entry)it all goes wrong. I get no option to add data....

Linking the tbl_lnk to Second Filter:

SELECT tbl_Link_Test_To_Sub_Test.Test_Number_PK, tbl_Link_Test_To_Sub_Test.Test_Code_PK, qry_second_filter.MaxOfPrice_Date, qry_second_filter.Price, qry_second_filter.Test_Desc
FROM tbl_Link_Test_To_Sub_Test LEFT JOIN qry_second_filter ON tbl_Link_Test_To_Sub_Test.Test_Code_PK = qry_second_filter.Test_Code_PK;

Or Simply trying to link the tables:

SELECT tbl_Link_Test_To_Sub_Test.Test_Code_PK, tbl_Test_Code.Test_Desc, tbl_Test_Code_Price.Price_Date_PK, tbl_Test_Code_Price.Price
FROM (tbl_Test_Code INNER JOIN tbl_Link_Test_To_Sub_Test ON tbl_Test_Code.Test_Code_PK = tbl_Link_Test_To_Sub_Test.Test_Code_PK) INNER JOIN tbl_Test_Code_Price ON tbl_Test_Code.Test_Code_PK = tbl_Test_Code_Price.Test_Code_PK;

If I manually enter the data into the seperate tables it works fine, and will display correctly on the Appointments form, but you still cannot edit/add records - the error is that this recordset is not updatable.

So, firstly, have I set the tables up correctly? secondly if yes, Am I using the correct approach to this? If yes to both, where am I going wrong? Do I need to create a table from the pricing query or is it much simpler?

Thanks in advance

View 5 Replies View Related

Show Multiple Tables In Query

Oct 1, 2013

How to show multiple tables in one query without duplications. I've attached a mock database of what I am working on. Basically, I want to show all of the numbers from different tables regarding one patient. But when I do using the Patient ID, it repeats the information over and over again. Is this the right way? Or is there a better way? I know I can use forms, but I ultimately want to transfer this database to excel so I can use graphs to describe the trends of the patients' results. My real database has about 300 columns, for all 5 tables.

View 9 Replies View Related

Query With Specific Date From Multiple Tables

Dec 19, 2005

I am trying to pull just a specific date that a Claim was sent to be processed. But the query has 40 tables that it needs to go through to get all possible dates. I know you will all say this is over kill on the tables, but each table represent a travel claim.

The problem I have is that I get to the 10th claim and it stops pulling the date. I put the following statement in the criteria:

[Type the beginning date MM/DD/YYYY]

any ideas?

View 14 Replies View Related

Update Query Covering Multiple Tables

Jan 25, 2006

Hello everyone! I am constantly have to change a field, called Product_Code, that resides on multiple tables. To change the field I have to run a series of 9 update queries. I'm looking for a way for all these updates to occur at the press of one button on a form. So I would set my criteria using fields on the form that correspond to the appropriate fields on the query and when I press the button all the updates would occur. My question is what is the best way to go about programming this form and is a form my best option to accomplish my goal. I hope I have explained this thoroughly enough...if not I am more than willing to answer any and all questions in order to get this task automated. Thanks everyone.

View 3 Replies View Related

Query From Multiple Tables Using Date Input

Feb 22, 2008

I have 3 tables, "Customer", "Offsite Service" and "Service".

Customer:
ID
Last Name
First Name
...

Service:
ID
Date
Price
Trans#

Offsite Service:
ID
Date
Price
Trans#

All 3tables are joined by the ID#.

I need to create a sales report/query based on the results of the user input, which is a date range. I've got it working great with two of the tables, but not the third. Here's the code for the working one I have now. I had to add the Trans# to make sure it returned all the results. If I take it out it only shows a small percentage of the records. The Trans# is NOT the same on both the service tables.

This pulls the information from "Customer" and "Service" tables and returns
Last Name - First Name - Date(Based on user input) - Price

SELECT [Customer List].[Last Name], [Customer List].[First Name], First([Service Records].Date) AS [First Of Date], Sum([Service Records].Price) AS Price
FROM [Customer List] INNER JOIN [Service Records] ON [Customer List].ID = [Service Records].ID
GROUP BY [Customer List].[Last Name], [Customer List].[First Name], [Service Records].[Trans #]
HAVING (((First([Service Records].Date))>=["Start Date" Use format MM/DD/YY] And (First([Service Records].Date))<["End Date" Use format: MM/DD/YY]))
ORDER BY [Customer List].[Last Name];

This pulls the information from "Customer" and "Offsite Service" tables and returns
Last Name - First Name - Date(Based on user input) - Price

SELECT [Customer List].[Last Name], [Customer List].[First Name], First([Offsite Service Records].Date) AS FirstOfDate, Sum([Offsite Service Records].Price) AS SumOfPrice
FROM [Customer List] INNER JOIN [Offsite Service Records] ON [Customer List].ID = [Offsite Service Records].ID
GROUP BY [Customer List].[Last Name], [Customer List].[First Name], [Offsite Service Records].[Trans #]
HAVING (((First([Offsite Service Records].Date))>=["Start Date" Use format MM/DD/YY] And (First([Offsite Service Records].Date))<["End Date" Use format: MM/DD/YY]))
ORDER BY [Customer List].[Last Name];



I want to be able to pull records from a date range that returns customer name, the date and price from service AND the price from offsite service.This way I have the price from both tables that I can add up and get a total sales report.

And yes I'm an access dummy, so please keep your directions easy for me to understand. Thank you in advance.
Bill

View 3 Replies View Related

Do Tables Have To Be Linked To Do A Multiple Table Query?

Sep 13, 2006

Please could you advise me if tables have to be linked in order to perform a query on multiple tables? For example searching for a date and matching records on more than one table at the same time.

View 1 Replies View Related

Queries :: Search Across Multiple Tables With One Query

Apr 18, 2013

Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.

I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.

I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.

View 7 Replies View Related

Append Query - Appending Multiple Tables Quickly

Aug 12, 2005

Hi Guys and Girls.

I have about 100 or so tables that I need to append back into one table :eek:

However all of these 100 tables all begin with the number 100 at the beggining. I know that you can append tables - but as far as I know you can only do one at a time. Is there any easy way to do this - for example writing a small SQL statement - saying select all tables that begin with 100* and then append into a master table :confused:

Alternatively are there any programs on the net that can do this (I just wrote a massive macro to import them from a text file in to access- not realising this problem would happen!) :(

Unfortunately each of the tables has the first row as the column heading too?

Any help would be much appreciated!!

Cuurently using Access 97 though.

View 1 Replies View Related

1 Query Pulling Info From Multiple Identicle Tables

Feb 14, 2007

Hi guys.
For my coursework I was told I need complex queries to gian high grades.

This is my problem:
I have many many tables all identicle. They have the same field names just different information on them.
I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.

I have tried everything, I fidled with the relationship but can't understand it.

Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.

I would really really apretiate all responses as this needs to be done asap.

Thanks in advanced.

View 9 Replies View Related

Tables :: Query Off Of A Junction Table - Multiple Categories

Sep 26, 2013

So I have a form that has a sub form on it (based off of a junction table), indicating which category a particular project belongs to. It can belong to multiple categories:

ProjectID
100
Category
Admin
Finance

But I want to run a query to find all of the projects that are both admin and finance. When I do that now, it comes up as two separate records in the query, which is fine... but I have to know the projects before hand to find if it's in both, because it returns heaps of records, anything that says admin or finance... and that defeats the whole purpose. I want the query to show me JUST the projects that have both of those in their subform..

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved