Searching For Records Based Off Of One Field From A Table

Mar 19, 2014

I have a database that I am creating for my work. I have a form that I am trying to get it to search the information from a table to pull the record on the form. I would like to search infomation such as employee id and wanting it to pull that information from the table.

View Replies


ADVERTISEMENT

General :: Searching For Records Based On Only Highest Autonumber

Jul 30, 2012

I have a database in which the year is stored in a table called year with as laid out below

"YearID" autonumber field and the
"Year" field which is a date field.

I am trying to search for records via a query for just the current year, i.e. the highest autonumber and I am having no success.

View 4 Replies View Related

How Can I Get A PK In Another Table, Based On Searching ...

Feb 5, 2005

How can I get a PK in another table, based on searching for it based on 2 values. By searching with 2 values you will always get only 1 record.

READ BELOW FOR SITUATION

My Problem on my form:

The user enters 2 values, after entering the 2nd value a Unbound combo box runs a query, which searches for these 2 values (unique), that appear in a separate table.

These 2 values are unique toegether, but records in this separate table have another field, its Primary Key (AutoNumber), used to identfy the pair.

So back to this Unbound Combo Box that searched for the primary key of the 2 values entered. Now Only the Primary key can be selected from the combo, the value that I'm after.

But since there will only be 1 option (being unique), how can I have the PK value aready stored in an Unbound text box, as opposed to having options in a Combo box?

View 5 Replies View Related

Select 10 Or Fewer Records Based On ID Field Within Table.

Apr 19, 2007

Hello,

I've looked through the thread and was unable to find exactly what I'm looking for ?

Basically I have a table with multiple fields & records and I would like to key on the ID field which has a slew of different ID's..... these ID's can be the same & show up on the table anywhere from 1 to 600 times.

What I'm trying to do is select each ID and pull only 10 records for each or if less than 10 pull in those as well.

So instead of pulling in the 600 records for that particular ID I only want to pull in 10 of those records, and for the ID that only has the 1 record pull in the info for that as well.

Any help would be greatly appreciated.

Thank you

View 6 Replies View Related

Need Report To Show A Number Of Records Based On A Value In A Field Of A Table

Aug 9, 2006

Hi,

I have a table with a field that shows the number of pieces that a parcel contains. It looks like this:
ParcelID, Pieces, Description

Data example:
1001, 5, Jackets
1002, 10, shoes
etc

I need to print labels for each piece that shows to which parcel it belongs. The report will look like:
1001, Total pieces = 5, 1 of 5 pieces, Jackets
1001, Total pieces = 5, 2 of 5 pieces, Jackets
1001, Total pieces = 5, 3 of 5 pieces, Jackets
1001, Total pieces = 5, 4 of 5 pieces, Jackets
1001, Total pieces = 5, 5 of 5 pieces, Jackets

and
1002, Total pieces = 10, 1 of 10 pieces, shoes
1002, Total pieces = 10, 2 of 10 pieces, shoes
1002, Total pieces = 10, 3 of 10 pieces, shoes
1002, Total pieces = 10, 4 of 10 pieces, shoes
1002, Total pieces = 10, 5 of 10 pieces, shoes

If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.

Can anyone help?

View 1 Replies View Related

Searching And Selecting Records To Be Appended To Table

Dec 6, 2004

Hi,

I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.

I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.

In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet

Can anyone please help ?

View 14 Replies View Related

Searching Records Within A Table By Code Builder

Jan 14, 2008

i am a beginner..
how i can write a code in a afunction that search
a record in a table according to some conditions
example:
i hv table employee that contains Title field which can have one of 3 values:Admin, Rep or Driver
i need to search the employee that has his type = admin
i can have one or more records that have this type...
so how to find first one, last one and all?

View 10 Replies View Related

Modules & VBA :: Delete Records From A Table Based On Records In Another

Feb 7, 2014

I have a table InvPrice and Updated Pricing

Need to delete all records from InvPrice that Match UpdatedPricing

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I have tried something like this...

Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError

View 14 Replies View Related

Searching Form Based On Query

Dec 16, 2006

Hi,

I have a form that is based on a query with many fields. As an example there is a field called "Medium" which contains values such as "Oil on canvas", "Oil on wood" etc.

At the moment I have a filter which works fine but this doesn't allow me to search all records which have the word "Oil" as part of the Medium.

I would like to be able to search all fields on the form. I have looked at a few exmaples on the forums but could not find one that applies to my scenario.

Can anyone help?

Thanks in advance.

View 4 Replies View Related

Searching Database Based On 3 Columns

Aug 23, 2011

I have a requirement of searching the database based on 3 columns. I have attached the search form and the code.

Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String

[Code] .....

View 12 Replies View Related

Tables :: Update A Field In One Table Based On Status Of A Field In Another Table

Aug 2, 2013

I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.

I want to prepare a report from the projects table that lists projects IF they have any open tasks.

In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'

View 7 Replies View Related

Update OnHandQty Table Field Based On Qty Field From Another Table / Form

Oct 30, 2012

I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.

View 5 Replies View Related

Insert Records Into Table Based On The Select Criteria From The First Table.

Mar 22, 2005

Hi

I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).

Forms involved are frmmain and frmsub.

Frmmain contains the call table information in the main form and parents information in the subform.

When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.

Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”

View 4 Replies View Related

Adding New Records To Table Based On Values In Another Table?

Aug 19, 2015

I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:

1. tblPatients - PatientID (PK), LastName, FirstName, DOB,...
2. tblLocations - LocationID (PK), LocationLabel,....
3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc
4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate
5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)

All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.

Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.

View 3 Replies View Related

How To Select Records Based On Summation Of Another Field

Jan 14, 2007

Hello,

I have a dataset that consists of Stands within a forest inventory. There are two Fields: the first field is StandValue (dollars), the other field is the StandArea( hectares).

I want to select the highest valued stands BUT the sum of the individual areas found in "standArea" cant exceed 10,000 hectares. Can anyone help me figure out an SQL query for this, or any other suggestions on how to proceed would be greatly appreciated, thanks
Greg

View 2 Replies View Related

Totalling Records Based On A Filtered Field

Oct 19, 2006

How do I calculate a filtered total from a field in a subform. It needs to be filtered according to [agreementID] and I would like to total the records in another field.

[agreementID] [assessment]
01-pmg18 $20,000
01-pmg18 $15,000
02-pmg18 $13,000
02-pmg18 $21,000

eg: I would like the total assessment for all 01-pmg18's and the total for all 02-pmg18 etc. Can I automate this so it recognizes and totals all assessments according to each unique agreement id group. Customers can have many 'batches" of agreementID's belonging to him. The 18 also refers to his ID number it is typed in. Thanking you in advance!

View 1 Replies View Related

Deleting Records Based On A Field Content?

Nov 16, 2004

Hi all!

I'm rapidly beginning to get a little confused... I'm trying to mass-delete records based on the content of a field.

I want to run either a query or a button on a form (or anything, for that matter) that will delete any records when there is an X in the N_Disp field.

Any ideas?

Thanks,

Barry

View 3 Replies View Related

Searching Records

Feb 23, 2005

I have a parent table called patient info. and a child table called fills. The database in the future will become very large, and will be difficult to find a particular patient in the parent table to update fills in the child table. How can I search through the parent table with ease so that I can update fills?
Thanks for those who help!

View 1 Replies View Related

Help With Searching Records

Oct 16, 2006

hi.. i am currently creating an employee database.. and there is a form on
the database that needs to search on records.. i am planning to create a form
with a single textbox (for the keyword entry) and a search command button
that when clicked, the search results will be shown in a seaparate continuous
form.

now here is my query:


SELECT tblEmployee.EmployeeIDPK, tblEmployee.LastName, tblEmployee.FirstName,
tblEmployee.MiddleName, tblCompany.CompanyName, tblDealer.DealerName,
tblOutlet.OutletName, tblPosition.PositionName
FROM (tblOutlet INNER JOIN (tblDealer INNER JOIN (tblCompany INNER JOIN
tblPosition ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK) ON tblDealer.
DealerIDPK = tblPosition.DealerIDFK) ON tblOutlet.OutletIDPK = tblPosition.
OutletIDFK) INNER JOIN tblEmployee ON tblPosition.PositionIDPK = tblEmployee.
PositionIDFK
WHERE (((tblPosition.PositionName)=[Forms]![frmSearchEmployee]![txtSearchFor])
) OR (((tblOutlet.OutletName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR
(((tblDealer.DealerName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblCompany.CompanyName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.MiddleName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.FirstName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.LastName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.EmployeeIDPK)=[Forms]![frmSearchEmployee]![txtSearchFor]));

but when i click search, the results are empty. what could be wrong with the
query? is it the query? what should be the record source for the search form
and the result form?

if you want to see the actual ms access file, here it is:
http://www.gigafiles.co.uk/files/636/human%20resource%20info%20system_2006-10-11.zip

the name of the form is frmSearchEmployee and frmSearchResultEmployee and the
name of the query is qrySearchResult.. thanks a lot and God bless..

View 1 Replies View Related

Searching Records

Oct 29, 2004

I would like to put a search tool on a form that does pretty much the same as the find dialogue box, with out it being a dialogue box...

I need to be able to search specific fields on the form and all the fields. It needs to "Find Next" so to speak, as in not just finding the first match.

Any help is GREATLY appreciated...... Joe

View 1 Replies View Related

Searching For Records

Jun 26, 2006

I wanted to see if it is possible to pull a query by just entering say the first three letters of a project name and the query displaying everything from the table that begins with those three letters. Any help with this is greatly appreciated. I am just completely stuck!

View 2 Replies View Related

Tables :: Auto Populate Field Based On Answer Of Another Field In Same Table

Feb 28, 2013

Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.

I am not quite sure how to lay this out. I am using Access 2010.

View 8 Replies View Related

Insert X Records In Table Based On Value From Another Table

Jan 9, 2007

Newbie question: I have a database where we track dispatched with two tables, one called Slots, the other called Dispatches.
In the slots table there are 2 fields, one called Week (which hold the Monday date for each week in the year), the other called Available (in this field we want to manually enter a number that will tell the system how many dispatches we can do that week). We want to automatically take the number from the Slots.Available and enter X number of blank records in the Dispatch table with the monday date.
How can this be done?
Then we will create a form that can be filled in with the balance of the information.
Thanks

View 3 Replies View Related

Selecting Records Based On Part Of A Text Field

Oct 12, 2006

I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"

I want to run a query against the table to select records for any one country, but not sure how to do this.

Any suggestions please?

View 4 Replies View Related

Queries :: Count Records Based On Part Of Field Only

Mar 26, 2014

We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.

Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.

I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:

4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002

I tried formatting the source field with Left -4, but it only shows the first four characters in the result:

4084
4084
4081
4078
4078
4078

How can I count the number of records each day based on only the first four characters? Example:

4084 = 2
4081 = 1
4078 = 3

View 6 Replies View Related

Make Field Auto Populate Based On Value Of Field In Another Table

Mar 4, 2008

I'm creating a database that keeps a track of questions and scores.

The questions in the database need to be dynamic and are changed frequently.

I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.

I want the question field in my scorecard table to populate with the value in my question table.

I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:

SELECT tblQ1.Q1
FROM tblQ1;

This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved