I have an Access database set up with a table, several queries and forms (Weekly, Monthly, Quarterly, Pending). When I click on the any of the forms the table come up with rows of data just like it should. When I click on any of the rows instead of the data coming up so that I can edit it the entire Access database shuts down and then restarts. A backup version of the database is generated and it asks me to save it. I have verified that the forms are linked to the correct queries so I'm at a loss as to why this is occurring.
I am working in MS Access 2000. I have a query that is returning a table with various types of data. The problem is I can't seem to find anywhere in the Access GUI that will show me the data types of columns it has returned so I can't manually create a table to hold the values returned by query. Question is two fold:
1. Is there a way in Access SQL to import data returned from a query into a table that is not yet defined. Some like this: a. Create a table that has a primary key but doesn't yet know the number of columns and/or data types of those columns. b. Import the data from a query into this table and have it create the columns and copy the data types and populate the table while also numbering each row by primary key.
or 2. Is there a way to find out what the data types are for a table returned by a query in MS Access. I have checked the properites of the query and have been up and down the gui looking but I can't find a way while looking at the data returned by a query to explicitely see the data type (i.e., number, Date/Time, etc..) used by each column.
is it possible to repeat a query on data returned by the same query while returning it to a single table?
i have a query that runs on the following info: PartNumber - the part number of an item AsmNumber - the part number of the item the part belongs to IsAsm - true/false on whether or not the part is an assembly
(see attached database)
the current query, when 11135 is used as input, returns 11124, 11165, 11103, and 11155. likewise when 11165 is entered the query returns 11109, 11110, and 11111.
i would like the query to return 11124, 11165, 11109, 11110, 11111, 11103, and 11155 when 11135 is entered.
the sample database attached is stripped down but accurately reflects what i'm working with. i do not know how many sub-assemblies belong to a top-assembly (could be zero, one, or sixteen), therefore i think the 'sub-query' would use the 'IsAsm' value as some sort of criteria - yes?
if what i'm asking is impossible, i'm very open to suggestions as long as they work with the data given.
i appreciate any help (especially being this is my first post) and apologize for the verbose post, i'm not good with brevity.
Hi All. I have a query that is based on 1 table. I am using a form with a calendar control for the users to select the date range. In my table the date format is mm/dd/yyyy. My calendar control is pulling the date format mm/dd/yyyy I need my query to only look at the mm/dd part of the field. In my criteria field inc_date I have Between [Forms]![frm_LookUpEnquiry]![txtSelectDate] And [Forms]![frm_LookUpEnquiry]![txtDate2] I have tried the datepart but Im not sure I am doing it right. Basicly Im not sure how to parse out the date to only look at the mm/dd. Or would this be better done in a SQL query Any Idea's for this would be very appreciated. Im kinda in a bind on this one and its the last thing I have to do to finish this database.
Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.
I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.
What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )
I have a query that returns several records, typically 1 to 5. I need to print a label for each record returned by the query. Printing a label is no problem, but how do I print sequentally for each record in the query?
I am trying to use Dcount to find the number of records in a query and put it on a text box in the header of a report. The query is a parameter query. The button opens a form which asks for start and end dates, the query reads those dates from the form and the report displays the correct number of records that fall within the date range. The query is based on another query, which is based on the table. I tried =dcount("*","qryName") but I get #error. Maybe becauase of dates? or parameters?
I'm trying to run a line of code after doing a Query Search that reports back the total number of results pulled from the search. I've dug around and found some useful code, but nothing that gives the results I'm needing. Most everything gives me the total number from the query. I'm also using a Split Form.
The process goes: Enter numeric search in Text10 and hit the search button to run the following macro: [studentID]=[Forms]![Query1]![Text10]
It then gives me the requested information.I have a second text box (Text19) that needs to display the number of pulled results. I've written VBA that only gives me the total number searched for studentID or for the entire Query.
I can get the number of records to be returned but I want to run a condition if the records returned is zero. To do this, I need to put the number returned into a variable but I haven't been find a way.
For instance, this code does work:
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));" conDatabase.Execute SQL
But I want to store the count as a variable. What I want is something that look this (it doesn't work but I just want to give an idea what I'm looking for)
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));" Dim hold as Integer hold = conDatabase.Execute SQL
If(hold = 0) Then 'tell them nothing returned End If
So how can I go about this?
Update: I see that someone posted a solution a few threads down using queries which did work. Can I still still do the same thing with SQL strings?
I have a query that i run and now I am adding another column to provide a ETA for an order, when i specify a date I only get results that have that date and when I leave out the date I get too many results. Can someone please let me know how I can modify my query so that I get all the results with an ETA and also all the accounts that do not have an order placed.
I am trying to create a Membership report that shows member activity over several months.
My problem is that the software that gives me the Member Activity does not report on accounts that were not used during that month and some members do not come in every month.
I have a Member's Details table as well as individual months activity and have queries set to show the results of each month individually (these work, obviously)
The Reports I am trying to get from Access only show the activity of Members that have some movement every month, but totally skips members that are not present for 1 or more.
The report uses these queries but fails to report any member that is not in the tables for all months.
Example:
September Rank Member FirstName LastName Activity 1 100 Andy Todd 200
October Rank Member FirstName LastName Activity
November Rank Member FirstName LastName Activity 5 100 Andy Todd 50
The report needs to show: Member FirstName LastName Sept.Rank Sept.Activity Oct.Rank Oct.Activity Nov.Rank Nov.Activity but will not because there are no values for that member in October.
I am teaching myself Access as I go, so I am sure there is an easy solution.
I have made a query from different tables; however, my query is returning about 5 rows for each person because some fields in the query return more than 1 row.
I have an Access Database that I will be using on a desktop. I have a table in this database that mirrors the structure of a table on a remote server in a SQL database. I have successfully created a vba function within the Access database that uses a server-side php subroutine to select records (I usually won't know how many) from the SQL database and return them to the access database. The code I use in the access vba subroutine to access the php subroutine is:
Code: With CreateObject("Shell.Application").Windows Set ieWindow = CreateObject("InternetExplorer.Application") ieWindow.Visible = False apiShowWindow ieWindow.hwnd, SW_MAXIMIZE ieWindow.Navigate "Web address for server-side php file" End With
The last command in my php subroutine is "return $retrievedData." $retrievedData is a multidimensional array containing data from 42 fields in multiple records (again, I usually won't know how many). I've checked the data in php so I know it has been stored correctly.how do I access the returned data within my access vba subroutine?
I should add that my overall reason for doing it this way is that I want to maintain my server-side database as an untouched master. Users can only add data to it. My client-side database is used to update the input table and further process the data. The subroutines described above are intended to retrieve "new" records only (i.e., records posted since the last access database update) from the server-side database and transfer them to the access database for further processing.
I have no idea if this is possible or not. I have a requirement to pull a list of Doctor's, their address, and which counties they serve. Sounds fine, right? Well, they want all the counties on one line... One record for each unique address.
So, my query would return this Dr Address County Dr Smith Main Street Plymouth County Dr Smith Main Street Sarasota County
But they want it to look like this Dr Address County Dr Smith Main Street Plymouth County Sarasota County
Any idea on how to pull that together for them and put it in an Excel spreadsheet?
Okay, I have a unbound form with three combo boxes the selection of these three & then clicking of a filter button opens the relevant form showing records based on the selection. Great that bit working, no major feat.
Trouble is when I select three criterea in the combo boxes that no record(s)exist for and hit the filter button. A blank form appears not very friendly or appealing.
what I'm after is a simple, thats me all over by the way, of a message box appearing to tell the user no records where found.
Finally got my DB straight so I am not using calculated fields however can't figure out the best way to handle the Combo Box so I do not show closed records ( Item checked out and item returned).
Please see the attached table of materials checked out and materials returned. How to setup a query so that if an item has been returned and matches the item checked out, Both the original check-in and the matching return records are filtered and not displayed in the Combo Box.
Also, I am a little worried about partial returns as when an employee only returns half the amount.
I have a subquery on a table which got the previous and next rows from a table. This worked fine, until I started replacing my table with real data instead of dummy-data. I now receive the "at most one record can be returned by subquery" error every time I run it. I checked that I do not have any identical rows.
My query:
Code: SELECT z.DateFrom, z.DateTo, z.ArticleID, z.retailerID, z.Actuals, z.Statistical, (SELECT y.Actuals FROM tblActuals AS y WHERE (y.retailerID=z.retailerID) AND (y.ArticleID=z.ArticleID) AND ((DatePart("ww",z.DateFrom,2,3)-1)=DatePart("ww",y.DateFrom,2,3))) AS ActualsMin1 FROM tblActuals AS z;
I append new rows via a VBA-script from another table.
I have a ODBC (Firebird) table linked using a DSN connection which has >300 fields. In Access, the linked table only show 256 fields (max for Access). Otherwise the link works fine. I have no control over the source Firebird table.
What I'd like to do is limit which fields from the Firebird table are shown in Access. I don't need all the fields, but I need some that are at the end and are not linked in the current setup.
I'm very much a novice at using ODBC, but can I somehow use SQL or some other method to specify which fields to show in the linked table?
I wrote a query to return the TOP 32 items for a key. Works fine:-
SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=1 GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;
I then plugged this into a query in the WHERE clause using In (Select Top .....;); [changing the =1 condition to pick up the correct id from the emcompassing query]:-
SELECT [my Disks Coefficients].[my Disk ID], [my Disks Coefficients].[thier Disk ID], [my Disks Coefficients].coefficient FROM [my Disks Coefficients] WHERE [my Disks Coefficients].[thier Disk ID] In (SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=[my Disks Coefficients].[my Disk ID] GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;);
This runs fine but the the number of items returned is for each key is less than if I run the Top query by itself for each key seperately? So when I run it stand-alone for ID 1 I get 127 rows (there are many equal coefficients); when I run it as a sub-query I get only 121 rows for ID 1!
Good afternoon! I have a database that tracks the withdraws and returns of shared tools. The withdraw form has a check box on it that is checked to show removed when the item is ordered (this keeps that item from showing up in inventory searches). When the item is returned, that check box is unchecked, showing that the item can be withdrawn again.
When a person withdraws an item their supervisors name is also logged so that we know who to chase down when the item doesn't come back.
I have a query that is supposed to show what items are still outstanding based on a date range (I got the prompt for date range to work..yea me). However, the query shows every time that the item was borrowed.
Example... I borrow a hammer and my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The next I borrow the same hammer and again my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The day after I borrow the same hammer and my supervisor is Joe. I return the hammer. It does not show as still withdrawn in my query.
Some other day I borrow the same hammer and my supervisor is Joe. I do not return the hammer. The two times I borrowed the hammer with my supervisor as Bob show up as well as the other time that I borrwed it with my supervisor as Joe shows up as well as the one that shows I still have it. . One of the criteria in the query is essentially only show the items that have the check box checked (criteria=true). But that apparently does nothing, or (more than likely), I am doing something wrong.
This is the SQL:
SELECT tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number GROUP BY tblItemSpecifics.Removed, tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number HAVING (((tblItemSpecifics.Removed)=True));
At most one record can be returned by this subquery. (Error 3354)
This is my SQL:
SELECT Students.[Student ID], Students.[Student Name] FROM Students WHERE (((Students.[Student ID])=(SELECT [Student ID] FROM [Student_Sports] WHERE [Sporting Team] = [Forms]![SV_Teams]![txtTeam])));
