hi
how can i make query that selects the first match of a criteria, or multiple criteria. lets say list of members first record in CA only then first record in OR only .... basically select first record from list of criteria.
Greetings all!
I am working on something and it has me stumped. Basically I have a list of Eastings and Northings and I am trying to find the closest postcode centroid based on the PAF file (for those that do not know of the PAF file, it contains a list of postcodes and the easting and northing of the centroid).
The best way that I can see of doing this is:
For each record I am trying to match, calculate the distance from the Easting,Northing to every easting,northing in the PAF.
Select Min(Straight Line Distance) from the results.
However I do not know how to do this without matching each record that I am looking at individually! Can anyone give me an idea of how I can put this query together?
I am using MS Access :(
Thanks!
~Shiv
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies View RelatedI have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
Hi,
is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help.
Thanks!
Probably an easy one...I have two tables, each with a number field say "Cat-ID" and "Ref-ID". The query I am looking to write will show the outstanding values. For example
Cat-ID: 100, 200, 300, 400, 500
Ref-ID: 100, 200, 500
Query Result: 300, 400
Any ideas on how to do this? I'm stumped.
Hi,
I have two tables of software names.
A count is needed of those names that are in both tables, as follows :
Table1.software name Count1 Table2.software name Count2
matching on software name.
The problem is that names match only partially, for examle:
Table 1 :
Acrobat Writer 6.0 std
Table 2:
Adobe Acrobat standard edition 6.0
This is the same software, but how do I make Access recognize that?
can't enter every parameter manually using wildcards, because
1) tables are too large to ask for each name separately
2) Sofware names matches on different parts, not only a first or second word.
Does anyone know how to do that ?
thanks
I have a table with two fields:
Field1 Field2
S11.1 111001
S13 130001
S11 110001
S13.1 120001
I need to query to find where Field2's first three digits does not match the numbers in field1. Giving the sample: 120001 would be listed in the results because it does not match the numbers in S13.1 130001(2nd record) does because if there is no "."; it defaults to zero.
I don't know to set up the criteria or what to ask so it would give me the results needed.
Can anyone help please.
Thanks
I am exporting an Oracle report to excel, and using an Access macro to import into a table.
The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later).
This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate.
Any help would be appreciated.
I have converted an Old FileMaker Pro DB to Access.
There is a record for each of the 22,000 sheets of engineering project documents issued over a period of years.
One of the main fields we query by is the "Description" field.
If I have the following Criteria:
[Enter Document Description]
Then save and exucute the query Access seems to look for an exact match to the value I provide.
I need a query to return ANY record with the criteria value in it.
For example If I entered "Fire" I would like to return anything with "Fire" in the description:
B Building FIRE alarm Install
Building C FIRE sprinkler Demolition
A Building FIRE Exit Signage
Site FIRE Protection Main
etc.
How do I phrase a query to return records that contain the value I provide within the text string in the "Description" field?
I realize this is a dumb, rookie qiestion. I tried Access Help and
I tried a "search" in this Forum, I probably didn't do a good job describing my problem.
I am attaching a pic of my relationships page to show the table structure I am dealing with. What I need to do is match forecasted values with actuals based on the same date range, sales_num and unit.
The two tables that hold this data after downloading from our systems are
ForecastOE_T
ActualOE_T
The other tables are crossrefs for additional information and aggregation.
I have been able to query successfully each of these separately based on my desired date range, and successfully aggregate based on salesrep number and unit... but, I don't know how to properly write the query joining them.
Here are my issues:
Not all reps forecast all units.
Not all reps sell all units.
So, when I try to join these values (forecast and actual), the query I write gives me only those rep/unit combinations that BOTH a forecast was made and an actual appears (we don't get a download of zero activity). What I need is output that shows the rep/unit combination for all entries that have EITHER the forecast or the actual, with zeroes defaulted wherever appropriate. This has been done in excel in the past, but the file sizes are prohibitive now... and I'm trying to help out, but am stumped here.
ANY HELP IS GREATLY APPRECIATED!!
Tom
How can I get an exact match in a search. If I search for say C1 I get results showing everything with C1 i.e C1, C11, C12, C13 etc. I just want to show C1's
This shows my query at present.
Like [Forms]![Search]![BoxNo] & "*"
Hi all
I'm using an array with the Instr function. The array (or the Instr function) doesn't seem to be case sensitive... which is a pain because its picking words within words where the case doesn't match!! I'd like the vales in the array to match exactly when using the Instr function
Any idea how I can get around this??
Thanks
Damon
Hello,
My database has two tables:
Master and History
Both tables have a SSN field with the following format:
000-00-0000XX (xx stand for two additional characters)
I have a search form with a textbox that I use to type criteria to open a form. The text box has the following SSN input mask: 000-00-0000CC;0;_
This allows me to return data even if I have a part of the SSN
I have now placed a button which verifies if the SSN I will type in the textbox already exists in table History. I am having problems with the SSN format as I have to type the whole SSN while I would like to type part of it (like the textbox input mask).
If Not IsNull(DLookup("[SSN]", "History", "[SSN] = '" & Me![txtbox] & "'")) Then
DoCmd.OpenForm ("popupssnsearch")
Is there a way I can do this?
THanks.
I need help writing an SQL statement in Access 2007 to select the closest date/time. I have Spot Time (the date/time commercials ran) and Call Time (the date/time we received calls).
I need to match these two fields so that I can tell which calls came in within 5 minutes before the Spot Time and which came in 15 minutes after.
Between DateAdd("n",-5,[SpotTime]) AND DateAdd("n",15,[SpotTime])
This gives me results within that 20 minute time range, but does not match each call up to the closest time.
For example,
SpotTime--------------- CallTime
6/30/2007 10:45 AM 6/30/2007 10:55 AM
6/30/2007 10:50 AM 6/30/2007 10:55 AM
6/30/2007 10:55 AM 6/30/2007 10:55 AM
In this example I would need the three Call Times to correspond with the 10:55 AM spot because the Call Time occurs very close to the Spot Time. I am unclear how to proceed.
I have got a combo box with a product code in it, and depending on which code is selected I want the data to come from one of two different tables. Is there any way I can do something like a match formula in Excel to see if the stock code is contained in a table?
View 3 Replies View RelatedI am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value.
View 11 Replies View Relatedi have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..
hope someone can help me out
When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do".
Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
[Code].....
(this might be more appropriate in Forms; apologies if I'm putting it in the wrong place.)
My company runs a home repair program and we keep track of our clients in a database I made a few months ago. We use almost exclusively a form drawing from 3 tables. We just hit 800 records (according to the form) and I was checking some things, and found that there are 807 records in each of our 3 tables.
I did a preliminary check, and the primary keys match as best I can tell, so I'm trying to figure out how 7 records can be missing from our form.
Any ideas?
Thanks in advance.
will
Hello all,
Happy New Year.
Hope you ca help with the following.
I have 2 tables in access. One is a table with the us state abbreviations.
I have another table, one of the fields is an address field, e.g.
SOQUEL CA 95073
SOUTHAVEN, WA 98671
SOUTHBURY, CT 06488
SPENCER IA 51301
SPOKANE, WA 99201
SPRINGFE VA 22150
I would like to create a query, joining these two tables together so that the query can give me the 2 state abbreviation e.g.:
Address field/ Abbreviation field
SOQUEL, CA 95073/ CA
SOUTHAVEN, WA 98671/ WA
SOUTHBURY, CT 06488/ CT
SPENCER, IA 51301/ IA
SPOKANE, WA 99201/ WA
SPRINGFE, VA 22150/ VA
Is this possible?
Thanks for your help,
Quick question… I am trying to accomplish something that I believe it is fairly simple. At least in Excel, it is. I have two tables with names. I am trying to create a formula that will compare name in table 1 with a name in table 2. If the names match (exact matches only) it will let me know. Something like =IF (NAME1=NAME2,”TRUE”,”FALSE”) in Excel. I have trying using Iif, but it returns that I cannot divide by zero. I have linked the tables, so if I query NAME 1 and NAME 2, it will only display names on table 1 that are found on table 2.
Any help would be appreciated.
Thanks,
Hi,
I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'.
The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table.
So simply put the query I want is the opposite of the one I made with the wizard.
I hope that makes sense?
Any help would be fantastic!
Cheers
Phill
How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name, description, type and date.
Thanks
J
--------------------------------------------------------------------------------
How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name (text), description (text), type (text) and date (date). I would always search on all four fields.
Thanks
J
I have a form based on a table called checkwriter. Right now it is comprised of multiple dropdowns that work great but now I need a textbox where the user can type in a few characters and have the form display the results which match the first few characters from the field "memo" from the checkwriter table. Im so a newbie to this and not sure whether I need 2 write a seperate query for this or write a simple procedure for that text box. Any help to get me started would be greatly appreciated.
View 4 Replies View Related