Find Records Where 1 Field Matches But Other Fields Vary
Feb 14, 2007
I have a table with records for products from each sales region (4 regions, N, NE, S, W). The table has the following fields: Region, Product Name, Product Description, Category, Business Line, and Supplier.
While the Product name is consistent across the regions the description, category, bus.line and supplier may have slightly different entries in each region. I need to build a query that when any of the Product Description, Category, Business Line, and Supplier fields do not match across divisions for each Product Name will display all the records for that Product Name. Any ideas?
I am trying to find company matches between 2 tables. The issue I have is that the spellings on table A differs from table B.Some of the differences are minimal like "St. Annes" and "St Annes". And some really big "St. Annes" and "Annes, ST London".
I am running Access 2003 and have created a split form using one of the std form options. The file it is querying has about 20 fields per record. The split form that is generated has four columns of 10 fields each.
E.g. Column 1 Column 2 Column 3 Column 4
Employee Fred Smith Weekending 24-June-2013 Age 25 Hourly_Rate $30.00 Normal_Time 24 Sick_Leave 8 Vacation_Hrs 8 Total_Hrs 40 etc etc Field10_Name, Field10 Contents, Field20_Name, Field20 Contents
When I view the form generated, some of these fields are too narrow and others are too wide. I would like to make the width of the fields various widths. But if I try to widen one field in the column all fields in the same column are made the same width. It appears as if they are multiply selected. Is there any way of selecting a single field and varying the width without impacting fields above or below it in the column?
I wan to build a form with a blank text box (txtbx1) where users can enter a part description, this will search records in Table1>part and return a list of parts that match the textbox input.
Here's what I've done -
txtbx1 - property sheet - control source - part event - on enter - ...
I've built an expression -
[Table1]![part] = [txtbx1]
hoping that this would match records 1n Table1 to txtbx1
In my Patients table i have a field called PatientID (AUTONUMBER) as my primary key I hve another problem. Is this possible as i hve been trying for two days now. I hve tried a lot of ways but i cant get it to work right. I have a table called dependents. ID , PatientID,,Dependents,DOB,Age id being the PK AND PatientID as a number I have a table called vitals. with ID,PatientID,PatientName( which is a dropdown of Dependents), hpp,sats etc. (This is a subform in datasheet style in another form)
In my table vitals i have the follwing for PatientName . comboxbox.
SELECT Dependents.Dependents, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age FROM Dependents;
So it selects fine in a dropdown like John 48, smith 36.
The problem i have is that it selcts all the records irrespective of PatientID.
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2 apple a apple b banana a carrot a carrot b dog b elephant b
I would like my query to now display a third field and group field :
field1 field3 apple both banana a carrot both dog b elephant b
I need to do a query to find a field value from similar fields. Table 1 has fields(customer id,...) and table 2 has fields (customer id, address,...). I need to use customer id from table 1 to find address in table 2. Both customer id fields in both tables is the same.
Hi, I have a cross-ref table (called MFC_CIBC_XREF) which links a bank account to a fund number and a general ledger number. It should be a unique relationship, wherein (the "=" means corresponds/links to)
Bank Account # "=" Fund #
For each bank account, there are sometimes multiple currencies being used, so each currency then links to a general ledger account, such that
Bank Account #.Currency = Fund #.General Ledger #
Sorry, if that's kinda cryptic, but here's an example:
Bank Account - ABCD123 Currencies operated in - CAD, USD Fund # - F30
Based on the above, ABCD123 "=" F30, and including the general ledger numbers, ABCD123.CAD "=" F30.100, ABCD123.USD "=" F30.8121.
Basically, it's a way to keep track of not just the transactions for a particular bank account, but for the transactions in a particular currency.
Anyway, as I said, it's supposed to be a 1-to-1 relationship: each BankAccount.Currency should correspond to 1 and only 1 Fund.GeneralLedger. However, there are some entries in the table which have each BankAccount.Currency corresponding to multiple General Ledger numbers. Using my example above, ABCD123.CAD has two records in the table, one corresponding to F30.100, and another corresponding to F30.8101. This was probably because F30.8101 used to be linked to a different bank account, that got merged (ie, it might have been linked to ABCD124.CAD that then got merged into ABCD123.CAD).
Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger. I don't really know that much SQL, and even in Design View, I'm not sure of the statements to use. Any help is much appreciated.
Hi all, I'm new to this forum. I'm having a problem with a query I'm trying to create.
I have two tables in the database: tblCustomers and tblInstallations. These two tables are linked by a field 'CustomerID'. CustomerID is the Primary Key in tblCustomers. In tblInstallations, the primary key is InstID and for each CustomerID in tblInstallations there are either 1 or 3 InstID records.
Now what I want to do is create a query with fields: Customer; CustomerID; InstID1; InstID2; InstID3. InstID1 will contain the first InstID for the CustomerID, and InstID2 & InstID3 will contain the second and third, if applicable.
I see in Query Design View in the 'Totals' line there is a option to display the 'First' and 'Last' match for a field. So is there any way i can use this method and also display the second record? If not, is there any other way to make this query work?
I have to two tables and I was add data to second table only if the field matches. Lets I have two fields site and recid on both and I want to update site on the second table only if recid from the first table matches the recid from the second table.
On the sub form I am selecting products that I am selling to a customer whose details appear on the main form.
The products on the sub form are selected from a combo box. This section is in datasheet format.
Depending on the product sold some of the labels on the sub form are not relevant. And when this is the case I was thinking maybe the label is rendered invisible.
Maybe this is not the best way to handle this - or is it. It certainly is expedient.
Perhaps as well when the label is rendered invisible entry of a value into the corresponding text box becomes impossible.
Is there a better way to handle this?
How I can render the label invisible and disallow data entry depending on the value of the combo box.
I have two fields on the main form and I need a total count of records between the value of the two and then enter the total in a bound field on the main form
I have 12 fields, each containing a number. I want to display the last non zero value in a query. I've looked at "last" but this returns the items in chronological order, which is not neccessarily the order my data is entered in.
I need to find all MaintItems which field MIC don't exist in either MIC1 or MIC2. So the only result which I need to see is MaintItem '5100161086' as MIC <> MIC1 Unfortunately I can create query which would approve '5100662734' as well
So bottom line I need a query which will display only the row below as MIC doesn't exist in neither MIC1 nore MIC2. Also I will have to deal with much more MICs soon so maybe there is a way to concatenate them and compare, isn't it? 5100161086 500001711 50000173
Attached query is what I got so far, but that displays last 3 rows of table above
I am trying to create a query and in the expression builder to find the max value from 3 different fields in 3 other queries.So each of the fields are called "TopSpeed" and the 3 queries are called "Test", "Training" & "Race".So in my new query I would like to return the MAX speed value from the 3 combined "TopSpeed" fields.Something like
In a form I have Two fields (which are of interest):
1) EmployeesName which is a lookup from another table named
TBLEmployees that contains two Fields EmployeesName EmployeesNumber
2) Second Field named EmployeesNumber which currently also using a lookup from table named TBLEmployees
if possible what I want to try and do when I select EmplyeeName from the dropdown list of names I want the EmplyeeNumber to be inserted into this field automatically rather than searching through the dropdown list again is this possible?
I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas? Regards:confused:
I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
I'm new to Access but comfortable with Excel and VBA.I'm trying to transform a VBA-heavy form from Excel into an Access database, as multiple accessing is required, as well as data analysis. The situation is I have a form where users enter data (name, account number, date, etc). The account number is unique, and I have a table of approximately 14000 account numbers which contains the company name, address, and other details for each account number. I've set the account number as the primary ID on the accounts table.
What I would dearly love to be able to do is: when the account number is entered on the form, it is searched for (e.g. by the user clicking a button) from the accounts table. If it is located, the 7 fields in the relevant row are displayed in 7 textboxes on the form. I do not want to record the address, just display it on the form so the user can choose whether or not to use it.
So far, I have found GetRows, which seems to be able to convert the fields into a 2D array... this could be usable. But how can I do the first bit - find the correct row in the accounts table based on the account number entered into the textbox on the form? Is it possible to do this behind the scenes? In other words, Access finds the right row and displays each field in its textbox on the form, without producing a report form first.
Hi I'm have created a video database with a genre field. Is there a way that when i click a button a list is displayed of all the records which are the same genre? Thanks Aden
