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".
I'm new to Access but not SQL, and am having difficulty coming up with a solution to the following problem:
I have one table for all Strings used in all other tables (to make Internationalization easier). The fields are: zId, zType, and zString. zId is an AutoNumber field. zType is a number which defines what type of string it is ( Item Name, Person Name, etc. ). zString is a text field with the actual text string (can be empty).
I have another table for Items, which have (among other things) Names (field name= zName). zName is actually a lookup into the Strings table and stores just the ID.
I want to create a form for people to enter a new Item. When creating a new item using this form, they will have to give it a Name, which will need to add a new entry into the Strings table with the appropriate type (ie, Item Name).
So, my question is "How to do the following..." * on my form to enter Item Data, when a user goes to create a new Item, I must first create a new entry in the Strings table with the correct value for zType. Then, I must get the value for zId for this new entry and assign it to the zName field in the Items table for the new record that I am creating.
I have three tables, with the following data (fields separated by "-" here): Unit - Customer Number - Customer Name - Type of A/R - Total A/R Unit - Customer Number - Customer Name - Type of A/R - Total Overdue Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names: Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this: Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
I am trying to merge data from multiple rows into one row for each unique ID. I can replicate on how to do this on a table, maybe a query or something like that. Attached img shows
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.
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 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 made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:
Function GetAllFiles() Dim fd As Object Dim strFilter As String Dim lngItems As Long
Const msoFileDialogOpen As Long = 3 Const msoFileDialogViewDetails As Long = 2
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this: Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _ & " FROM Table1 " _ & " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _ & "FROM Table2 " _ & " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
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 3 tables made up of ID and NAME and other parameters. NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.
Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C Parking units p1,p2,p3,p4,p5,p6 Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage Unit A p1, p2, p5 s2 Unit B p3 s1 Unit C p4,p6 s3
I was given a task by my boss to create an appraisal system for our department here in one of the local schools in the Philippines.
Anyway, what I created first a form from which all subjects being offered will be displayed. I did this by having a main form (bind to personal info table) and a subform that displays all the subjects (bind to subjects table). Then, the user will simply click on the appropriate rows bearings the subejct that the students wish to enrolled and when the save button is click, such will be saved into the enrol table.
My questions is, what control will I use to allow me to select multiple rows? and how will I be able to save selected rows into the enrol table?
I have a table of products and a table of ordersEach product requires multiple processes to completeCan I make a query that will lookup the products in the orders table and show a list of all the processes that need to be done to complete all the products on order?The bit I dont get is how can a query return multiple rows for each product?
Could somebody please help me! I am new to access and trying to create a database that brings together information from a number of different sources, into one large database. I need to import a large amount of data from another access table and I would like to just cut and paste it into my table. BUT, I can’t figure out how to add multiple rows at once to my existing table. The data that I am importing contains about 6,000 rows and 4 columns. Can anybody tell me how I can do this without having to manually create 6,000 rows, one at a time? I really do not want to do that. Thank you for your help.
But I am getting an error "Missing semicolon( at the end of SQL statement" and placing a semicolon at the end isn't helping either. I can insert a single row so column type or primary key isn't a problem.
Here's the table structure,
rvp - number (pk) rvp_name - text controller_id - number
Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?
Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."
Then it will let me delete one record out of the selection.
I am the only one in that particular table at the time I am trying to delete the record.