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.
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".
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.
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?
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.
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:
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.
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?
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.
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?
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.
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------------------
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):
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?
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.
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:
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.
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
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.
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.
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?
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.
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..