Multitable Field Selection Query

Apr 21, 2007

Hi Gurus,

I have a database newbie question. I am writing appli. in asp. i have 2 table 1 MsgMst and 2 UserMst UserMst has fields ID and UserName and MsgMst has fields ID, UserID (which is ID from UserMst), MsgDate and Msg. I want to list UserName, MsgDate and Msg in 3 columns of table. Can I get all three (USername from UserMst parallel to UserID in MsgMst Table) and MsgDate and Msg fields from MsgMst table in a single Recordset? What should be SQL Query?

Thank you in advance!

View Replies


ADVERTISEMENT

Query Selection - Multiple Choices, One Field

May 19, 2005

I have a database which keeps track of a trucking business. Each truck has a trailer number. I have no problem having a query prompt the user to enter a single trailer number to query on. What can I do when the customer needs to select more than one trailer number to query on?

View 4 Replies View Related

Queries :: Using A Form Selection As A Query Field

Oct 24, 2013

I've inherited a database that has a table with a large number of Yes/No fields. Apart from normalizing the database, I was wondering if the following was possible:

ContractTable has Yes/No Fields for rights granted for each order (television, dvd, internet etc).

Using a form, the user selects the specific right they'd like to run a report on - selecting this right from a drop-down menu, this is labelled "RightsCombo21" on the form.

When the button is clicked, the right selected is now the "Field" section of the query.

The SQL query right now looks like this (shows all rights sold for the territory):

WHERE ((([Contract Table].[Contract Cancelled])=False) AND (([Contract Table].[Contract End Date])>Now()) AND (([Contract Table].[Contract Type])="License Agreement") AND ((ContractIDAndCountry.CountryName)=[Forms]![TerritorySpecificRightsChooser]![TerritoryCombo7]));

I'd like to add something to this WHERE statement:

AND (([Contract Table].RightsCombo21)=True)

Not sure if it's a syntax issue, or I'm attempting something that isn't possible.

View 4 Replies View Related

Modules & VBA :: Combo Box Query Field Selection

Jul 31, 2014

I'm trying to write a query to allow the user to search through records.

To make the interface simple I just want a combobox to select the search field and a text box to enter the search string.

My problem is trying to build a query where the value of a combobox on a form is used as a field in the query.

View 3 Replies View Related

Tables :: Auto Populating A Field Based On Selection Of Other Field

Jun 27, 2014

I (will have) a form which a user fills in to enter new data.

I have two fields,

"Branch" and "Branch ID" (Branch ID will not be on the form, just in the table)

Branch will be fed by a combo box with seven choices. I would like it so that when "Branch" is populated it autopopulates Branch ID with a code which relates to the branch, so for example

BRANCH BRANCH ID
Braintree BRA
Colchester COL

View 4 Replies View Related

Forms :: Limit Form Field Selection Depending On Another Field

Feb 5, 2014

I am creating a training database and first I have tables in relationship to the courses:

tbl_Courses
Course ID (Primary Key)
Course Title
Objectives

Instructors (lookup field allowing multiple instructors to be selected, meaning they are skilled to teach the course)

tbl_Instructors
Instructor ID (Primary Key
Last Name
First Name
Full Name (Caluclated to put Last Name, First Name)

[code]....

What I am trying to do on a form to create a new event is once the user selects the Course from the Course ID combo box, then I need the Instructor combo box to only display the instructors who are skilled to teach the course which are selected in the tbl_Courses.

I can get all Instructor ID's, but not the names and the class that has multiple instructors show on one line.Should I have not put the Instructors field in the tbl_Courses? Do I need another table for instructor skills or something?

View 1 Replies View Related

N Per Field Selection?

Dec 29, 2006

Hi

I have a db of about 10,000 records or so. This is a list that will be used for emailing purposes...and I want to limit the number of records per company that are emailed to.

I need to figure out a way to limit the number of records per company....any suggestions?

Thanks

View 1 Replies View Related

No Field Selection Available In Wizards

Mar 23, 2007

I'm somewhat new to Access, so I'm using the wizards to get me started to make forms and reports.

When you first click on the "Create Form by Using Wizard" it asks you to select the fields you want to use from the selected Table or Query. Unfortunately no fields show in the "Available fields" box.

I know I have fields in each table or query. I even tried to make a dummy table using their field names incase mine contained invalid characters.

I've tried it in both the Form wizard and the Report wizard.

The fields of all the tables and queries used to show up when I first install Access.

What am I doing wrong? Should I try to reinstall the program?

Thank you for your help,

Pam

View 6 Replies View Related

Field Selection In SQL From Form.

Nov 29, 2007

Hi

I cant find this for the life of me... this is the second day trying to figure it out.
Anyway enough crying:D

Question:
Either using a SQL statement or query builder, how is it possible to make the field selection come from a form, ie a combo box, text box etc. or a variable?

Reason:
Im trying to make a dynamic searching form which will allow the searching from customers via a few of many columns which reside in 4 different tables also. Using a cascading combo box system to eventually narrow down the correct one. The above is my first hurdle and I think once I get past this brick wall I will be back on my way.

View 10 Replies View Related

Problem Of Date Field Selection

Nov 19, 2004

I have a table to store the new append records from a SQL statement. In this table I have defined a "Status Date" in field type date/time with format dd/mm/yyyy. However, when execute the SQL append statement, the system has appended the record with format of dd/mm/yyyy hh:mm:ss.

The problem is when I run the report with a date range input, the report cannot select any records with such date range. I have examined the table format is correct, but I found the data actually included the time in it. Do anyone know how to overcome this problem?

View 3 Replies View Related

Autofill Field From Selection In Combo Box

Jul 27, 2005

Hi all,

I am fairly experienced with Access but am stumped. I have a table with a primary key ID field, product name, and product price. On an order form, I want to select the product from a combo box and have that product's price automatically populate a text box beside it for further calculations. Any help would be much appreciated, thank you!

View 14 Replies View Related

Updating Another Field Upon A Selection From A Combo Box

Feb 28, 2008

Hi,

Im trying to get one of combo boxes to update a field automatically.. There are two choices in this combo box "Gatwick" and "Woking" what i need is when one is selected the JobNo field will update itself to GWO-(the first Unused JobNo) Ive used some VBA to make atleast the GWO- or WWO- appear and it works, but i have no clue as to how to get the next unused order number to appear after it.

This is what i have so far:

Private Sub JobLocation_AfterUpdate()

If Me.JobLocation = "Gatwick" Then

Me.JobNo = "GW0-"
Else
Me.JobNo = "WWO-"
End If

End Sub

Any help would be greatly appreciated.

Cheers

Marc.

View 4 Replies View Related

Modules & VBA :: Time Field Selection

Jun 11, 2013

I have a table with several fields including the following 3 fields:

dtmAppoint Date = stores appointment date
dtmAppointTime = stores appointment time
lngAppointDoctorID = stores doctor's id for specific appointment

When I enter a new appointment, I want to know whether a specific combination of these 3 fields already exists. The doctor is actually a dentist and maybe another appointment has already been placed for the same dentist on a different chair. Creating a recordset as indicated below works but I am having some problems with comparing Time (dtmAppointTime). The following works but I need to extract Hour, Minute and Second and put it into string variable strTimeDum.

Code:

rstSearch.Open "SELECT * FROM tblAppointment " _
& " WHERE (clng(dtmAppointDate) = " & CLng(rst!dtmAppointDate) & " )" _
& " AND (dtmAppointTime = #" & strTimeDum & "#)" _
& " AND (lngAppointDoctorID = " & lngAppointDoctorID & ");", CurrentProject.connection, adOpenKeyset, adLockPessimistic

Any way to retrieve the records that correspond to the specific time or a simpler way to access the records of interest?

View 10 Replies View Related

Multi-Value Field Does Not Submit Selection On OK

Sep 2, 2014

I know many are opposed to multi-valued fields, but in this case it works out nicely for my purposes.. I pick a couple things from a table to put into a field in a separate table via multi-value lookup, and when I hit ok, nothing happens. Literally. Tried the enter button, tried a mess of different keyboard mashing, and nothing. URL....

Row Source:
SELECT [Cage Codes].[CageCode], [Cage Codes].[Company], [Cage Codes].[Division] FROM [Cage Codes] ORDER BY [CageCode];

View 4 Replies View Related

Help With Combo Box Selection Being Determined By Entry In Another Field

Feb 7, 2008

first of all, i'm new to access. the only experience i have with it is reading the "microsoft office inside out", access section, and doing some research on the web.

i work for a school district and my boss has asked me to make a database with all our assets. i used to have everything in excel, but he prefers access, so here i am. what i'm wanting help with is making a combo box that lists manufacturers like hp, dell, etc., and then having the next field show a list of models depending on what was selected in the manufacturers field. so if i select dell, i want only dell models to show up in the combo list for models.

i can make the combo lists, but i don't know how to make it so that the entry in one field affects the list in another field. i've tried putting in lookup values, i've tried doing it with expressions in queries, and a few other things. i've tried searching google, and the forums and i've found things that are kind of similar to what i want, but doesn't do exactly what i want. so here i am...is there a simple way to do this? this isn't required of me, but now it's driving me crazy because that's the way i'd like to have it but i can't figure it out.

i'd appriciate any help with this.
thanks.

View 4 Replies View Related

Filtering A Dropdown Selection Data Field?

Mar 1, 2005

I'm trying to filter my table so that i can make it a query. The data in the Facility is linked to another table with FacName and FacID. When i try to filter the data to create the query from the table, it says type mismatch. It's really bothersome....btw is there another way to create a filtered query?

View 2 Replies View Related

Populating Into Another Field Based On Drop Down Box Selection

Sep 26, 2005

I need help to populate a certain selection from a drop-down (combo box).........OK I have a field named REP ID/REP NAME....(Rep stands for contractor)....Each Rep is assigned an ID and the first two numbers in the rep ID correspond to the territory in which they sell products...So for example 5830 John,Smith the 58 would be Northern California....Now what I want to create is a field named TEAM which would consist of Team A through Team D....Every Team is associated with different territory numbers to lets say I click 5830 John, Smith I would want Team A to automatically be populated in the TEAM field....Someone please helpp....And I hope I described it correctly

View 1 Replies View Related

Populating A Text Field From A Combo Box Selection.

Apr 28, 2005

I have a combo where the list is populated with a bit of VB code to create a two field list. When the user selects the item they want from the list it displays the item in the combo box and should populate a text box in the same row with the value from the second column.

This combo is part of an orders subform and the user is selecting a product from the list which displays the product_name and the product_id.

The problem is that the product_id is not filled in and access returns the following error:
Run-time error ‘3101’:
The Microsoft Jet database cannot find a record in the table ‘product_table’ with key matching field(s) product_id.

This is the VB code that populates the list :
[code]
Private Sub comboCatagory_ID_AfterUpdate()
Dim sProd_description As String
sProd_description = "SELECT products_table.product_id, products_table.prod_description " & _
"FROM products_table " & _
"WHERE products_table.prod_catagoryID = " & "'" & Me.comboCatagory_ID.Column(0)
Me.comboProd_description.RowSource = sProd_description
Me.comboProd_description.Requery
End Sub
[end code]

What am I doing wrong?

Thx
Kev.

View 5 Replies View Related

Forms :: Subform Field Selection By User

Jun 8, 2013

I need to have the user select what fields he wants displayed in the subform, (this i think would be best to have a selectionform open and have a list of fields that can be placed in the sub form i was thinking of using checkboxes for this), then i would like to open the mainform and the subform would display only the fields the user selected.

View 5 Replies View Related

Forms :: Nomination Form - Combobox Selection List Not Updating After Each Selection

Oct 27, 2014

I am designing a nomination form (web database so no vba macros can be used).

The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.

The form is bound to the tblSubmit table where the submissions are populated.

I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.

This is the select statement to populate the job level combobox:

SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));

The select statement to populate the combobox for the nominee combobox:

SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));

The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"

How to get the checkbox selected for each corresponding staff.

Sample of the database has been attached.

View 4 Replies View Related

Field Values Based On User Selection In Combo

Dec 9, 2005

Hello,

I'm stuck! I want to create a form which has a combo box where you select
someone's name, and their address appears in another text box on the same form. The names and addresses are in a table called "cardholders".

I'm doing separate combos for first name, surname cos I think that's the only
way you can do it.

The fields in the table are credit_add, credit_name & credit_surname. The combos are called "cardholder name" "cardholder surname" etc. This is the code in the text box where I want the address to appear:-

=DLookUp("[credit_add]","cardholders","[credit_name] = '" & [cardholdername] & "' And [credit_surname] = " & [cardholdersurname])

Can anyone help?

Thanks.

View 3 Replies View Related

General :: Default Value Of Text Field With Selection Of Combo Box

Sep 9, 2014

In the database attached I have a form called from Candidates.In this form is a combo box where a user can select what company the candidate belongs too.

However some candidates are Private Individuals (do not belong to a company).Therefore, I want the Position field to be set to 'n/a" by default if 'Private Individual' is selected.

View 9 Replies View Related

Forms :: Multiple Field Population On Combo Box Selection

Nov 4, 2013

I'm building a test registration form, and I want to populate 2 additional fields based on the TestID ComboBox selection. This same TestID table has a Requirement and Expected result field, that I want to display for the end user, to make sense of the test in question. Multiple fields in the ComboBox does not work since you cannot select a single testID; you can click on the relevant testID, but the table remains in view, rather than displaying only the relevant testID.

View 7 Replies View Related

Forms :: Available List Based On Previous Field Selection

Jul 6, 2013

I have a form whereby a user for my database can be created. The fields required are listed below:

Payroll ID
Forename
Surname
Location
Manager

The last two fields are related to two other tables; tblLocation and tblManager, which are related to one another.Each location (there are three) can have more than one manager, however, a manager can be associated with only one location.I would like whomever is creating the user to only be able to select a Manager that works at a particular location, currently, I can select LocationID 1 and ManagerID 3, but ManagerID 3 works at LocationID 2.

How can I base the 'Manager' field in my form on the previous field's selection, e.g. 'Location'.Also would need to restrict the order in which the form's fields are enter, i.e., 'Location' before 'Manager'.

View 13 Replies View Related

Forms :: Calculate Field Based On Second Column Of Selection

Nov 20, 2014

I have a form, where one field is a combo box

The combo box is two columns linked to at table,

task and sla

You pick the task, and it displays the task

What i need is, another field, to equal the SLA part of that task.

View 5 Replies View Related

Forms :: Hiding Field Depending On Selection Of Combo Box

Jan 28, 2014

I have a form which has a combobox where the user selects a record from another table,I want to hide the forms fields [managercontact] and [managertelno] depending on the value of the combobox (manager) from the table details, but its not the value shown in the combobox its the value of another field [type] from the same table called details. Its one of two values being 'conc' or 'ob'.

View 2 Replies View Related







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