General Query Usage
Jul 9, 2005hi Guys,
need help in using query, plz let me know that how to use designed query in Reports ?
thanks in advnce.
ADVERTISEMENT
General :: Find Instances Of Field Name Usage
Jul 21, 2013I have written a large database for the school I used to work for. In the main table there is a field named "Active" which is text with yes or no choices.Is there a way to find in what objects this field is being used in the database?
For the life of me I can't remember why I put this field in or if its even used now.This database has had many versions over the years.
I have searched many of the queries but can not find where this field is used.If there isn't a way I plan to backup, remove field from table and test all the features of the program to see which crashes. However I'm hoping there might be a quicker way.This was one of the first DB I wrote and didn't pay much attention to mapping or keeping notes back then.
General :: Split Database - Cannot Edit Table Design Despite Snapshot Usage
May 27, 2014I have a split database made in Access 2007. Each user gets their own copy of the frontend from a script. I wanted to be able to edit the design view of the backend tables even if people were using the database so I made all the forms use snapshot source and only allowed data updates through VBA macro update queries. Having any form open locks the backend source table from being edited. In fact, I've found that just having a normal snapshot query open causes the message "Either an object bound to table 'whatever' is open or another user has the table open. Do you want to open the table as read-only?"
Is there some way to have a table be the source for a form or query, but still have it designable under most circumstances?
Attempted to late-bind a recordset on form load; result was the same:
Code:
Set rs = CurrentDb.OpenRecordset("Select redacted as ft from tblRedacted ", dbOpenSnapshot, dbReadOnly)
Set Me.Recordset = rs
Set rs = Nothing
Query Usage
Sep 26, 2005Over the last 12 months my database has grown in size.
I'd like to delete queries that are no longer used. Is there a utility I can use that will list where all my queries are used, ie used in other queries + reports.
Does anyone know?
Thanks
Parameterized Query Usage
Feb 19, 2008So I have a query which prompts the user for a username to search by, GetUsername.
Im used to TSQL usage which would be something like:
exec GetUsername 'blah'
My goal is to generate about 10 reports based on this query but with different (hardcoded) input. I do not want to prompt the user while generating these reports.
How would I invoke this query with a hardcoded parameter? (Id rather not save 10-20 versions of it).
Track Form/Query/Report Usage
Jul 25, 2007Hi
About 10 years ago I wrote an Access application which is still in use. Over the years certain parts have become superfluous and new parts have been added by a variety of users. Thankfully they have stuck to the original ideal and only used macros and queries - and not modules of badly written code.
However, the system is beginning to creak and I have been asked to rewrite the system from scratch with a shiny new interface, cutting the dead wood and incorporating those new features that did not have a half life of one report.
I would like to be able to monitor the use of the system in order to be able to determine just which tables, forms, queries & reports are being used. I'm not interested in auditing who added what to which table, so a 'normal' audit system is not required. Can anyone tell me how I would record which of the above are being used and how regularly?
I have asked the users and (of course) they don't know.
TIA
Usage Help Please...
Mar 29, 2006How do you calculate usage? For example...
If I've got a field named Time, then a drop-down box with "Morning", "Afternoon" and "Evening"
How do I make a query so it displays the number of each time...
For example...
There's two times for Morning, three for afternoon and one for evening.
I tried using the 'Sum' total and the criteria of "Morning" but it says data mismatch. That was merely a guess, so I would like to know how to do it.
Like...
Time
Morning: Afternoon: Evening:
2 3 1
Thanks.
Usage Of Enter Key
May 26, 2005I have a continuous form with an unbound text box (for search critera) and a command button to kick off the search, which simply applies a filter.
When a user enters text in the unbound text box and hits "Enter", I want the search to be kicked off (instead of having to tab over or mouse over to click the "Search" command button. This is the default functionality of an HTML <form>, so users kind of expect it. But I'm having trouble implementing it in MsAccess.
I've tried using the KeyDown property of the unbound textbox, but no luck, since the KeyDown method occurs before the data in the box is saved. I'm guessing this is related to detecting what key has been pressed but my issue seems to be the order of events occurring on the form.
Any suggestions? This *must* be common situation, but I've found no related topics in searching the forum.
Logging Usage Of Records
Jun 21, 2005I have a form from which people view records out of a db.
I would like to log the date/time and the key record they view into a table
The usage table has a date/time field & a string field for the viewed record.
I can run an append query to update the Usage table but can't figure out how to get the date/time into the table ?
Is it easier to write the whole lot out to a file ?
Can I Calculate Peak Time Usage?
Mar 29, 2006Hello All,
Is it possible to generate a query that can calculate the 'TotalMins' that an event covers our peak times?
I.e. Our peak times are 0700 to 1000 if we have a start time of 0600 and a finish time of 0900 is it possible for the query to work out that we have covered 120 mins of the peak period?
Thanks for any help provided!:)
Converting To 2007 From 2003 - High Cpu Usage
Dec 11, 2007This is very strange. I have converted two computers from 2003 to 2007. I have also installed 5 2007's on to new computers. All of these are Professional XP sp2 setups. I copied over a database to woek on those computers. Originally the performance was very good, on all of the computers (but not the lowest specified converted laptop ) exitting from a form containing a subform takes 37 seconds. The word Calculating... is on the status bar. The laptop takes 3 seconds par with what it used to do.
I have transferred the 'Data' database, the 'Apps' database, Utility.mda and another .MDA. I have also transferred all the Reference executables to one of the other computers. This has not made a jot of improvement.
Has anyone any pointers. I have exhausted all the resources trying to find a solution to this one.
Reports :: Memory Usage / Enforce Garbage Collection
May 1, 2013I have build a database with which I can create quite a lot of reports. When creating them I run out of memory with run time error "2004".
When investigating I detected that a lot of memory is used when creating the reports in PrintPreview mode. When closing the reports the memory is not automatically freed.
Is there a way to enforce garbage collection? Local variables of called routines I set to Nothing.
General Query Help
Sep 7, 2007Hi,
I'm very new to Access DB developing, but I'm learning loads, thanks to this site.
What I'm trying to do, and it's driving me crazy, is the following:
I have a Table that has to fields (Total Daily Weight) and (Total Yearly Weight) and I have a field that calculates the percentage of yearly weight based on the total daily weight.
Now I know you're not supposed to store calculated values, so my question is..How do report on this calculated value?
A little more backround on the database:
The Database tracks waste for companies. They supply me with a total for the year, I weigh the total for the day, and then calculate to % and report on it for them....The problem comes when I go to print a report...I don't get the calculated % of yearly waste..
I keep reading on here that it should be done in a query? Can someone please guide me what I should do? I can put a copy of the DB up if it helps.
Any help would be appreciated.
General :: Sum Fields In Query
May 13, 2015I am currently running queries from a table and I get a list of part numbers and the totals. I would like to collate this into one row for each material instead of multiple - so essentially it would be like using a pivot table and summing the totals.
Example - currently looks like this:
Material Total
F12 33
F12 21
F12 5
F13 44
F13 60
But I want the query to produce this:
Material Total
F12 59
F13 104
General :: Autofill Using Query
Jan 21, 2015How to do an autofill using a query. e.g. if the product code is entered it will complete the price of product (lookup and coding not an option)...
View 1 Replies View RelatedGeneral :: Filter Query Using VBA
Apr 10, 2014So I have a Form with 3 fields. 2 Date fields (Beginning & Ending) as well as a combo box (CName). There is a button (Filter).I want to fill in both dates and select from the dropdown box. Onceeach field has a value then it will send a msgbox saying there were "# of records" between the 2 dates by CName
View 14 Replies View RelatedGeneral :: How To Combine 3 Field Into 1 In Query
Nov 20, 2014how to combine 3 field into 1. For instance. I have a customer table with company name, branch, floor, department. then i am gonna create a query called customerextended and combine all these 3 field so that I can select them in the combobox.
I have a sample here but I just dont know how to edit it...
"Company name: IIf(IsNull([department]),IIf(IsNull([companyname]),[floor],[companyname]),IIf(IsNull([companyname]),[department],[companyname] & " " & [department]))"
General :: Need To Create Query From A Table
Apr 14, 2014I have an old ERP system. The upload function is one line per value. So if I want to upload 7 models I need 7 lines, not one line with Qty of 7
however my source system sends the data as one line with Qty of 7
DLM2245/17DLM2245/17X1
DLP2204/17DLP2204/17X7
DLP2249/17DLP2249/17X7
DLV1004/17DLV1004/17X147
DLV1005/17DLV1005/17X158
DLV1009/17DLV1009/17X187
so in this example line 1 is fine. But line two needs 7 lines, and line 3 needs 7 lines and line 4 147 lines etc etc etc.so this is how the first three lines would look in the query.
DLM2245/17DLM2245/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
DLP2204/17DLP2204/17X
[code]....
General :: Navigation Control With Query?
Jan 21, 2013I have a form for testing purposes only it is named "Navigation Form". It has a tab called WorkingInitial. Within WorkingInitial I have a form called QueryTester. With a list box called List0.
Basically I want to click on the tab and the query to automaically filter based on the criteria I create. I dont want to have to create a bunch of forms with each seperte query. I want VBA to modify the Query.
This is what i have an I have no clue why it doesnt work or what I am missing.
Code:
Private Sub WorkingInitial_Click()
Dim strSQL As String
List0 = strSQL
strSQL = "SELECT * FROM [Table1 Query].[My.StatusInitial] WHERE StatusInitial = 'Working';"
End Sub
General :: Query Last 10 Unique Records
Apr 9, 2014I got a large transaction file with deplicated records!! For unit price checking, I need to copy the last 10 unique records to another file with the same criteria but price is not the same as the current checking record.
For instance, I got 5000 records with around 400 records unit price is not the same as history. For each checking, I needed to copy the same criteria historical records to a tempory file and then delete corresponding duplicated records first, following by another deleting action only to keep the last 10 records according to date field.
General :: How To Get Last Character Of The String In Query
Nov 5, 2012How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
General :: Query Criteria From List Box
Nov 12, 2014I have a parameter query where user enter the department number to get their info. I want instead of entering the department number, a list box pops up and ask to select department as parameter and run the same query.
View 2 Replies View RelatedGeneral :: Loop Through Query For Information
Jun 18, 2014I am using Access 2010. I have a database that on a form uses a multiselect listbox. That part works just fine. The list box is for selecting additional people to email. Now I have had no luck with returning just the email address that are in a hidden column (the persons actual name is seen and "selected"). The names come from a separate table and is used as a forgien key. On that same table are the indivuals email addresses. What I did was loop through to get all of the ID numbers I am getting from the list box (the ID numbers are stored in the table that the form is based on).
Once I have all of the ID Numbers I thought that maybe there was a way to retrieve all of the email address associated with the ID Numbers. This is what I have so far. I know that AllQuery returns the first email address from the list box. I just have no idea if the query is returning more than one record, or if it is how to then go to the next record. I have tried a few things with little to no success.
Code:
Dim ListItem As Variant
Dim AllItems As String
Dim AllQuery As String
For Each ListItem In Me.EmailAdditionEgineers.ItemsSelected
AllItems = AllItems & Me.EmailAdditionEgineers.ItemData(ListItem) & " or "
Next ListItem
AllItems = Left(AllItems, Len(AllItems) - 3)
AllQuery = DLookup("EmailAddress", "AdditionalEmailRequestQuery", "[ID] = " & AllItems) & ";"
General :: Run Query If Database Shuts Down
Oct 11, 2013I have table tblLog set up to track logging on and off. All I am tracking is the date and time the user logs on and logs off. That's pretty simple to do. When logging on it appends the userid, date, and time to tblLog. When logging off, it updates the logoff date and time for the matching fields.
What I need to know is this. If a user closes the database without using the appropriate buttons within the program, such as Ctrl Alt Del, or right clicking and using the Close command, or if the power shuts off, or whatever, is there a way to get that last update query to run...
General :: VBA Select Query With Inserted Value
Feb 13, 2013With the following VBA code, I'm trying to select the text value from a textbox and fill that in a query to select the code from a person. (a password only login).
However, I'm getting a run time error 3078 at the '' Set rs = db.OpenRecordset("strSQL")'' line.
Code:
Private Sub Tekst6_AfterUpdate()
Dim strSQL As String
Dim strBarcode As String
Set db = CurrentDb()
'strBarcode = Me.Tekst6
'MsgBox strBarcode
[Code] .....
General :: Run Query Depending On Date
Jun 4, 2014I would like a query to run, depending on a date..If the date in [Txt_Date_Account_Opened] is between todays date, and within the last 30 days.do CurrentDb.Execute ("Qry_Append_Units")else a msg box stating "account is not yet open"
View 12 Replies View Related