i need to be able to use "lookup", i have a set of postcodes from 3000 customers, i need to extract these and compare them to lists i have of postcodes, which make up a region. And run queries from these
e.g a customer on my database has the postcode BD21 7KK
On my list of postcodes i have on paper - WEST YORKSHIRE - BD21 7
therefore The BD21 7KK Customer falls in the West Yorkshire region.
I need to do this four 4 regions with lists i have on paper then run queries.
Can some one please provide the neccessary steps?
Or even give me an example made?
Im really stuck on this at the moment and me job depends on it ! :(
How do I get a field to calculate this formula? I want a script to find the precise current date and from another field find a number and add it to the current date day to make current date + number = date in the future.
For example Field: Total Nights Hired = 2 Field: Date due back = 23/02/07 + 2 = 25/02/07
I am sorry if I did not explain this well enough. I am totally new to databases but need help in my corsework. I am designing a video rental system.
Here is what i would like to do if anyone can help
I have a table with the following columns which are currency values, along with aload of other stuff High Med Low
These have been put into a form as bound text boxes, i would like to allow the user to chose their rate, say by a button or maybe option group "i'm open to ideas" Dependent on the choice made the rate chosen will then go into a independent text box.
After there i'm fine, the reason for this is,.. the chosen rate/independent text box will form part of a calculation
I'm getting an error when trying to run a report. "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again."
1.) I tried to install both the V-Tools and Rick Fisher add-ins, and neither one could be added. I got errors from both.
2.) I'm working with a small test database to create a report. Db is tiny, 1.8Mb. But I've also created a report which has several embedded subreports. Everything is based off of queries which certainly have to sort through some data.
Can someone please tell me if there is a way to perform lookups in Excel from an Access table without first exporting the data into Excel? I have a form that is being exported into Excel from a pricing tool our company uses from an outside vendor. It is important the users have the ability to perform complicated calculations and are not at all experienced in Access. There are several data sources that are well over the 65,000+ rows limited by Excel. I need to either prove or disprove whether it is possible to perform lookups between Office applications. If it is possible to lookup between applications, I would appreciate any sources known to learn how to do this. If not possible, I would like to be able to confirm that fact to my manager.
Hi there. Does anyone know how to use an update query to copy some records from one table into another? I have table1 which has all the contact details and what I would like to do is work out a process for importing records from an excel spreadsheet. What I have so far is as follows:
1. In access go into the queries tab and select update query 2. Open both table1 and table2 3. Drag the fields to be updated (table1) 4. In Update to select [table2].[table2 column name]
When i run this nothing appears to happen. 0 records are appended. Does anyone now how to go around this with another solution or should I be using other SQL methods?
Environment: Front-end Access application developed in 2003, with back-end database in 2003. Being deployed in a mixed 2003/2007 environment. Users with Office 2007 will have to open and use the 2003 Access application without converting it to Access 2007.
Problem: When I take the Access 2003 application and open it with 2007, some forms give the following error message (whether selecting Open, Layout View, or Design View):
"There isn't enough memory to preform this operation, Please close unneeded programs and try the operation again."
The real problem is some of the most complex forms open in design view, and some of the simplest do not. There appears to be no ryhme or reason to it. Some of the forms are bound and some aren't. Some of the bound forms are bound at run-time with embedded SQL, some aren't. All query objects seems to be fine, when they are used on bound forms. Some forms have significant amounts of code behind them, some do not.
All report objects execute fine with no problems opening any view.
I can open the same file in Access 2003 and everything is fine. I've compacted and repaired the DB in Access 2003, no help when I go back to 2007.
Wondering if some references are off? But I have some really simple forms that can't be opened. I find nothing on MSDN on this.
OK, usually I can figure these things out for myself, but I'm realy stuck on this one and any help would be appreciated
background: I work in a small scale pilot production line, and we take a series of measurements for every part that we make, each of which has a unique ID. Sometimes we need to take the measurements more than once, so that the data output from the measurement system (which it can append straight to an access table) has a part ID and a measurement number, the combination of which is unique (for reasons that I won't go into it is not possible to combine them into a single field, otherwise we would do this)
problem: The measurement system can be temperamental and sometimes sends the same record more than once, and It is horribly time consuming to go through and check manually It occured to me that we could send the measurements to table1 (where they would be stored temporarily), and write a sub to go through each record in table1 in turn, compare the part ID and measurement number to the records stored in table2 (where they would be stored permanently), and append the record to table2 only if the part ID and measurement number combo has not already been taken, before moving onto the next record in table1 and doing the same thing
The part I just can't figure out is how to go through the records in turn and compare them, I can do the append and deletion using SQL, that's not a problem.
if anyone can point in the right direction I'd be realy grateful (or tell me if I'm barking up the wrong tree completely)
I am having trouble making a command button do what I want. I have a subform within my form, but I only want it to appear when I click the button. I tried using the wizard but there is no option to open subform, only open form. Any thoughts would be greatly appreciated!
I have my setup all complete and everything works great. Now, I have a subNavigation form within the Navigation form. Thing is, I am not sure how to go about the code for the query to pick up the Beginning and End Date to generate my report. Here is my Form setup:
frmNavigationForm (Main form) - Tabs on top. NavigationSubform (first Navigation Form, where I have a tab called Admin) frmAdminNav (where I have embedded under the Admin tab) - Tabs on left side. NavigationSubform (Second Navigation form where I have a tab called "Search") I don't recall what the name. How do I find it, stupid question, I know... frmSearch (where I am trying to pull a detailed report from date search criteria)
Here is one of the codes I have been trying to use in my Query and nothing seems to work.
Code: Between [Forms]![frmNavigationForm]![NavigationSubform].[Form]![frmAdminNav].[Form]![frmSearch]![BeginDate] And [Forms]![frmNavigationForm]![NavigationSubform].[Form]![frmAdminNav].[Form]![frmSearch]![EndDate]
tell me that can we use functional keys F1, F2,F3,,,,,,,,,,,,,,F12 in ms access to perform commands like saving a form data , for closing form refreshing form, clear form.Or is there any way to make shortcut keys combination like[(ctrl+s)(ctrl+c) (ctrl+A)] in access to perform action like saving,closing,clearing, current form.
How can I edit a query in SQL view to run multiple lines of SQL?
For example I can use the GUI to create a query, then in SQL view enter the following:
DELETE FROM Employees_copy;
which clears the Employees_copy table. Perfect. But what if I want to do this for mulitple tables in the same query eg for a second table Employees_copy_2 as well? I've tried
DELETE FROM Employees_copy DELETE FROM Employees_copy_2;
which gives a syntax error, as does
DELETE FROM Employees_copy, DELETE FROM Employees_copy_2;
I have a text box on a form, currently it performs a dlookup for me. I want to change it so that it only performs the Dlookup if a checkbox is checked, but otherwise will display the text that's in an unbound text box.
Not 100% this is even possible, but hoping so. So far I have in the textbook:
The dlookup works fine by itself, it's just making it conditional that's causing me problems. I know it could just be a small problem with the way I've written it, or it could be a bigger thing with me not understanding the way IIF works.
I have a subform based on a query. That query contains a couple of calculations that end up in textboxes on the subform. The problem is that the calculations require me to enter 2 dates that are on the subform. If these are not entered before the calculation is performed I get a "Cannot update record" error message and then #error in both Lease cost and Discount Cost textboxes.
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
Has anyone ever got this error message before? I’ve never came across it and I don’t know how to fix it. The error message displayed when I added three extra labels to a form then tried running it. At the beginning, it took more time than usual to think about it then the error message was displayed. Now I can’t open my form in design view nor can I run it! It’s totally inaccessible.
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
I have a Word 2010 document linked to an Access 2010 data source. When a user clicks a button in Access, the Word document loads and performs a email merge using the below VBA code:
Private Sub Document_Open() With ActiveDocument.MailMerge .Destination = wdSendToEmail .SuppressBlankLines = True
[Code] ....
However, as the .mailsubject part is not in the loop it is only retrieving the first Return Code. I have tried to integrate in the loop to no avail. Also, how do I add static text to the Subject, I need something like "Your Return Code" + "Return Code"..
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS: ID_PRODUCT (primary key, autonumber long integer) ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS: ID_ORDER (primary key, autonumber long integer) FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.) FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen) CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS] from PRODUCTS where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
