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.
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 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.
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
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
Hi,
I recently got a new job and am trying to learn access for it. I have two tables. Both of them have client id numbers. They are both supposed to have the same client id numbers. However, Table 1 has more client id numbers than Table 2. I want to do a match query that selects the client id numbers in Table 1 that do not have a corresponding match in Table 2. How would I go about doing this?
Also, I want to do a simple select query where I select the client id numbers in Table 1 whose first two numbers are "88." How would I do this in the query or SQL form.
Do you have any recommendations about the best way to learn Access for practical applications like this? I'll also need to get good at making Forms which seem fairly complicated.
Thank you for your time.
Rajiv
Is there a way to have a query return only records where the text in two fields doesn't match?
View 1 Replies View RelatedI have several tables that I need to join together to create a single form for multiple entries. Job Table, OPR Table, Organization, Program List Table, Program Notes, Rank, Reference Table, and Status Table.
Ultimately, I need to be able to display all of these in one form and allow for adding/editing notes from the Program Notes section. I also need to be able to let the user look up all info by selecting the Program Name and have the other field populate correctly. The issue I am running into is that I cannot get the Program Notes table to join to the Program List table correctly.
This is what I attempted to use in SQL:
SELECT [Program Name], [Status], [Reference], [Self-Inspection], [IG Checklist], [Continuity Book], [Bragging Paper], [Program Strengths], [ORI Reports], [Best Practices]
FROM [Program List Table]
UNION
SELECT [Log Date], [Log Entry]
FROM [Program Notes]
However, I keep getting an error. I have read that it is because I don't have the same amount of columns, but the tables don't have the same information.I have also tried to do it through a normal query and through a third table, however I get errors about ambiguous outer joins and I can't seem to make that work either.
I have a query pulling data from several tables. In the middle I've got join between a part number in a BOM table and a part number in an inventory table.
What I want is the query to return the inventory location when we have stock. When we have nothing in, then there would be no entry in the inventory table and it should return a blank or null in this and all the proceeding tables.
Can this be done?
Hello,
i'm not sure how to write a query, maybe it'll take VBA function to accomplish this, but I need to display the results for all invoices for an item.
Little background:
1. This set of invoices are of only one particular product line as it's this product line that needs this special handling. These are Guitar orders.
2. Each guitar item can have option items on the same invoice. So basically these are all considered item numbers.
3. The guitar starts as the bare / basic guitar. The customer can choose to select one or many options for a custom guitar.
4. These orders will need to have special guitar programming codes that need to be entered into their guitar programming machines. Call these "Codes".
5. Depending on the option items for the various guitar items, the Codes could vary.
6. Need to be able to display ONLY the Codes specific to the guitar or guitar and option combinations.
I have several tables:
1. Guitar for all the guitar items
2. Options for all the option items
3. ProgrammingCodes for all the programming Codes
4. ProgramCodes for creating the above combinations with a field named ComboID.
5. GuitarHeader - the invoice number and guitar item
6. GuitarDetails - the invoice number and option item (invoice number to invoicenumber link to the GuitarHeader)
Then there's a main form and a sub-form for the end-user (guitar programmer).
1. The main form is the Guitar items
2. The sub-form is the ProgramCodes fields
this form is used for this end-user select the Guitar and options (where necessary) and select the Codes and enter the ComboID for all.
for instance:
These are all the possible codes for AE185.
AND depending on the Options selected for AE185 on invoices, the Codes will differ.
The ComboID is the differentiator.
Code:GuitarOptionCodeComboIDAE185 185RR 1AE185 186RHT1AE185 187RT 1AE18538185RR2AE18538186RHT2AE18538187RT382AE185BB185RR3AE185BB186RHT3AE185BB188RT-B3AE18538185RR4AE185BB185RR4AE18538186RHT4AE185BB186RHT4AE18538188RT38B 4AE185BB188RT38B 4
so for the Codes that repeats in the ComboID is only because the Option(s) also requires that Code as well as the other Codes.
for instance, if the invoice ONLY has Option 38 from the Options defined for Codes, then the result would be:
Code:AE18538185RR2AE18538186RHT2AE18538187RT382AE185BB185RR3AE185BB186RHT3AE185BB188RT-B3AE18538185RR4AE185BB185RR4AE18538186RHT4AE185BB186RHT4AE18538188RT38B4AE185BB188RT38B4
Code:Invoice Guitar Codes123456 AE185 185RR 186RHT 187RT38 <-- say this invoice had Option 38 that was the only match234567 AE185 185RR 186RHT 188RT-B <-- because BB was the only one that matches345678 AE185 185RR 186RHT 188RT38B <-- because both 38 & BB matches
So guess the question is how do I write a query or function (VBA) to be used in a query to get the results for ONLY the matching options so the correct codes for just these options are displaying?
It's difficult to me in figuring that out. how to make sure that the result does not show the ALL the codes that match but ONLY the codes in the combinations. Again, hence the reason why I got to the point of having Combination IDs to differentiate that.
hope I made sense here. Not sure how else to ask the question other then to try and provide examples
let me know how else to explain if this is not helping.
I have a db which has a query. This query is rather simple where it filters fields based on criteria. Here is the SQL VIEW:
SELECT DSResult.STATUS, DSResult.TESTCODE, DSResult.BATCHNO, DSResult.TESTNO, DSResult.TESTDATE, DSResult.CODE
FROM DSResult
WHERE (((DSResult.STATUS)="TESTED") AND ((DSResult.TESTCODE)="41015" Or (DSResult.TESTCODE)="41016") AND ((DSResult.BATCHNO)<>"1") AND ((DSResult.TESTDATE)>=#1/18/2010#))
ORDER BY DSResult.TESTCODE, DSResult.TESTDATE, DSResult.CODE;
I need to alter this query so that where records that have the same "DSResult.CODE" and counted are greater than 20 occurrences and then display only these records sorted in DSRESULT.Code order.
I am designing a database which keeps track of door access levels at a college, using Access 2010. A door access level is programmed to a key card, which grants access to a number of different doors throughout the college.
So, what I have is an "LevelID" and a "DoorID", where each LevelID has zero to many DoorIDs associated with it, as well as each DoorID has zero to many LevelIDs associated with it.
example table:
LevelID DoorID
1 1
1 2
1 5
1 6
etc..
All the data has been inputted into Access 2010 successfully, however I am having difficulty in developing one of the main functions of the database..
What I want to do is have a checklist of each DoorID displayed on a form, and when any combination of DoorIDs are checked, Access will search to see if a LevelID is associated with that combination. This information can tell me whether a new LevelID is needed to be created.
there are certain txt boxes that once filled out, will filter a subform of a table of 1000's of records to give them a number to use on the form around 200 of the records have a depot in the "depot" field on the table, when they input a depot, it filters to them 200 fine if a depot is there,
what i want is, when they input the depot, if its there, filter it, if its not, to do nothing, as they could still get a unique number if the depot isnt in the list what happens now is, if the depot isnt in the list, it displays no records
Field : Fld_Depot
Table : Tbl_Agreement_Summary
Show : False
Criteria : Like "*" & SearchForText([Forms]![Frm_New_Accounts]![Fld_Depot]) & "*"
Or : Is Null
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;
My new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, *
FROM [TimeSheets All]
WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number]))
ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
running 3 queries together.
1 qry has a date parameter of start/end date(running before the 3 are together)
Have a field in 1 of the 3 queries with a date field that I need to show being <= the date parameter...
How/where do I use this criteria?
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
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.
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 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 Related