Trouble Restricting Queries Results On 1 Column

Jan 20, 2007

To the point: I have a column which is a combo box (list) consisting of all the departments of company. I don't want 4 of the departments to show up in my query results. In the criteria field I have tried to use the following:

<> "department name", "department name", "department name", "department"

// Note: I've even attempted to place those departments inside parenthesis. And use ; between the names.

Other criteria I have attempted to use without the results needed are:

1.) Not In

Note: some of the department's name consist of a single name; some are two names with a space between the two names. I have entered the names in the same letter case as they were created in the table's combo list.

Suggestions are greatly appreciated.

View Replies


ADVERTISEMENT

Queries :: Updating Column With Various Results Based On Other Record Values

Jul 11, 2013

I have a field called density which needs to be updated to show either 10, 20, 30, 40, 50,60, 70 or 80 depending on a number of variables, for example: If market location is 'hot' and unit type is 'house' and discounted is 'no' then show '10' in the density box.

or perhaps:

If market location is 'cold' and unit type is 'apartment' and discounted is 'no' then show '20' in the density box.

I have tried all sorts of expressions and queries but have really reached the limit of my know how and can't solve it. Is it even possible to do this in Access?

View 2 Replies View Related

Trouble Reporting Aggregated Yes/No Results

Oct 24, 2004

I have a DB with multiple check boxes in each record. I want to show the total Yes responses (i.e., for all records or a group of records combined) for each check box field in a report. I am using QBE, but I'm having problems. What is the best way to do this, or at least one that works. Thanks in advance.

View 1 Replies View Related

How Can I Put The Results Into One Column

Jan 27, 2006

Greetings all,

Below is a query were I can get eveything into tow columns but I would like to have the results in one column?

SELECT tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname
FROM tbl_Data_JobOrder INNER JOIN tbl_Admin_Company_Details ON tbl_Data_JobOrder.CompanyID = tbl_Admin_Company_Details.CompanyNumber, tbl_Admin_Company_Details AS tbl_Admin_Company_Details_1 INNER JOIN tbl_Data_Receival ON tbl_Admin_Company_Details_1.CompanyNumber = tbl_Data_Receival.OwnerID
GROUP BY tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname;

The Results are;


tbl_Admin_Company_Details.Companynametbl_Admin_Company_Details_1.Companyname
Company 1Company 1
Company 3Company 2

What I would like is something like
Company 1
Company 3
Company 2

Thanks in advance

rgmb

View 2 Replies View Related

Concatenate Results Of A Single Column Query

Mar 14, 2007

I have a query which select the records who have a certain criteria.

The result is something like this.
A 0012345
A 0032432

Note that the records that have this criteria will be smaller or equal than 2.

I want the query to give me this answer instead.
A0012345, A 0032343
(in the same field that is).

Is this possible, if so can someone help me. My email address is russcami@yahoo.com

Thanks

View 2 Replies View Related

Trouble With Min/max Queries

Feb 2, 2006

I have a MainQuery with the following fields:
ID-(the patient's ID)
Score
ItemID

I am trying to get the highest score for each patient and be able to identify which item was their high score, so basically I want to end up with the following query results:

ID
(Patient'sMax)Score
ItemID

To do this I was trying the following two queries:
Query1:
SELECT Max([MainQuery].[Score]) AS MaxOfScore, [MainQuery].[ID]
FROM MainQuery
GROUP BY [MainQuery].[ID];


Query2:
SELECT [MainQuery].[Score], [MainQuery].[ID], [MainQuery].[ItemID]
FROM MainQuery INNER JOIN Query1 ON ([MainQuery].[ID]=[Query1].[ID]) AND ([MainQuery].[Score]=[Query1].[MaxOfScore]);

What I find is that Query1 works and Query2 returns more than just the patients' max scores. Can anyone see the problem in my second query?

Thanks!
Carly

View 2 Replies View Related

Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column

Sep 24, 2013

I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.

View 7 Replies View Related

Queries :: Change In Column Based On Base Query Column

Mar 24, 2014

I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...

Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".

The error is because the second layer of query does not identifies Q1 2014.

How do i make access change the column automatically when the Q1 changes to Q2...

View 1 Replies View Related

Queries :: Find Unmatched Contents From The Column By Matching With Another Column?

Nov 16, 2013

i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.

View 1 Replies View Related

Queries :: Add A Column In A Query That Will Give Y Or No To Previous Column

May 21, 2015

I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).

View 3 Replies View Related

Queries :: How To Distribute Different Values Of One Column In New Column Fields

Jan 30, 2014

In my table for duplicate "line no" I have different "contractor" like below.

LINE NO CONTRACTOR

L-0001 C-1000
L-0001 C-2000
L-0003 C-6000
L-0003 C-8000
L-0003 C-9000
L-0004 C-5000

Now I would like to make a query for transposing values like below:

LINE NO CONTRACTOR1 CONTRACTOR2 CONTRACTOR3

L-0001 C-1000 C-2000
L-0003 C-6000 C-8000 C-9000
L-0004 C-5000

how I have to make this query?

View 1 Replies View Related

Queries :: Joining 3 Queries And Displaying Results In Separate Columns

Jul 31, 2013

I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).

My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)

Using this SQL:

Code:

SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION

[code]...

Which returns a table that looks like this:

Final Equipment, Duration

Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004

And so on.What I need the table to look like is this

Final Equipment, 3 Mth, 6 Mth, Historical

Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004

And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.

View 6 Replies View Related

Restricting Access.

Nov 18, 2006

is there a way to pw or restrict access for ppl to a table, form / control page?

View 1 Replies View Related

Queries :: Combining Data Results From Multiple Queries

Sep 30, 2014

I am working on a report that has some special characteristics.

Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.

Each of these tables has a date that the Complaint, Complement, and Termination notice was received.

Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.

The equation used for that is : (complaints/vendors_in_group)*100

It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.

What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.

Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.

2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.

3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.

View 4 Replies View Related

Restricting Access To Informatino

Oct 24, 2006

Hi

I have made up an Access database form for our Sickness Administrators to fill in when someone is off ill. I want the form to only show (on the drop down list of names) the people that that particular SA deals with - for example, Jill would enter sickness records for the Democratic, Legal & Personnel section. I am not sure how to go about this:

a) if I have one table with all of the employees names on it, is there a way of restricting Jill from seeing any names other than the ones that she deals with?

b) if I have separate tables for each section, would that be easier? and if so, do I just make a "switchboard" for them to click on the appropriate list?

Thanks!

Maria

View 8 Replies View Related

Restricting Display Of Information

Jan 16, 2007

I have built a database to be used by several users. One of the tables contains some fields which I don't want some of the users to be able to see.

All users log in to the database with their own username and password.

How can I hide the fields to everyone except a specific group of users?

Thanks,

Gary

View 7 Replies View Related

Restricting Records Per Date

Aug 15, 2007

Hi, a newbie needed help here.

I just wanted to ask whether it is possible to restrict records/rows in a table that has the same values (same date to be exact).

For example, I have an order form and wanted to restrict to only 20 orders per day (could be of any date; not restricted to the current date only). Therefore, if more than that, a message box will appear and no more record will be allowed to be saved.

Whether is it possible or not please justify.

Thank you in advance.

View 2 Replies View Related

Restricting Fields On Import?

Aug 29, 2007

Hey guys-
New here, and fairly new to Access as well (although I can stumble my way around). I am building a database in which I have to import the data on a daily basis (there won't be much, if any, data entry- just manipulating/Queries of the imported data). One of the fields I am importing is a 'category' type field. There's no way to restrict the data entry aspect of it before I import it into my DB- so I have to work with what I got.

The users type in the categories themselves- many all match the same name/spellings, some do not. So I assume I'm going to have a Category table with all the acceptable categories already entered into it. What I need is some way for the ones that don't fit the acceptable Category names to be flagged- once flagged I need to be prompted for each record if I want to ADD the category to the Category Table, or, if I want to match it to an existing category in the category table. If I choose to match it to an existing category, it will always keep that new category name so that queries and whatnot are consistent.

An Example-
The acceptable Category name might be 'Fruits'.
Upon importing the file- there might be entries that match that exactly, or- some might be entered as-

The Fruits
Fruits II
The Fruity Ones

If these three were to be in the import file- I want to be prompted to change the field entry to 'Fruits' or create a new category name, and associate that with the new name.

How would I go about doing this?
Thanks!

View 1 Replies View Related

Restricting Entry To Alphabetic Only

Jul 27, 2006

Hello everyone,

I'm working on a data-entry form and would like to restrict the data that I enter on certain fields to alphabetic characters only i.e. a -z, A - Z and Spaces.

Is there any way I can acomplish this??

Many thanks in advance.


Will

View 1 Replies View Related

Restricting Access Via Comand Buttons

Apr 20, 2006

I have created a db and would like to apply the format of the attached zip file

I cannot seem to work out how it was done cutting and pasting the code is not working either if someone could take a look and give me a simple method of creating the desired solution i would be very greatful

The button to access the password will be called DCC Log In.

This will then allow the Command Button DCC to be Clicked (Previously Hidden as in the example)

This opens the DCC Form (already achieved by =openForms("DCC")

View 2 Replies View Related

Restricting Certain Data When Opening A Form

Mar 24, 2006

What I have is a database that I am using for 3 different sites; NE, NCA, & SCA. What I want to do is have the form ONLY show information for the specific site that the Leader is a member of. I have a security table that contains the persons name, & site location. For example: John Smith works at the NE site; so when he goes to open a form to add/modify/delete members from his site I want him to only be able to see date for NE from the table. So in the listbox NCA & SCA would be filtered.

How can I go about doing this? The forms Record Source is set to the Security Table. Please help so I can do this without having to create a seperate form for each different location. :)

View 3 Replies View Related

Restricting Number Of Open Connections

Jun 10, 2005

I want to restrict the number of times each person can open a database, but give them the option to force close the database on another PC, if they have left it with the database still open, prior to allowing them to have full access / functionality of the database on another PC.

In order to achieve this, I need advice on how to automatically record the computername as each person "logs in" to the database and secondly, send a command to close the database / Access on a PC with the stored computername.

Thanks in anticipation.

View 1 Replies View Related

General :: Restricting Field Entry

Mar 16, 2014

In a table I have 3columns with the primary key of MemberID. Of the columns in question, one is StudentID and the other is StaffID. What i want to do is that if a use enters the information in MemberID, he will not be able to input in the field in StaffID and vice versa.

View 4 Replies View Related

Restricting Updates Of Product Prices

May 28, 2013

I am trying to set up a database to track truck maintenance, repair, and inspection records for a trucking company. Parts are ordered from vendors, and I have forms set up to facilitate the entry of the information regarding those orders. The problematic form is based on a query. In that query (this form and query deals with the purchase details - product, price, extended price), the product ID is gathered from the inventory transaction table, the product name and price are gathered from the product table (I did this so that price would enter automatically).

Of course, when I change the price, it is changed across all related orders, including those that have been saved previously. I know that the problem more than likely stems from having the info pulled from the products table. I'm just at a loss as to any other option to have the price automatically pulled.

View 3 Replies View Related

Restricting Lookup Values In Sub-datasheet

May 12, 2014

I am new to Access and am currently learning it so that I can construct a database of trivia questions.

I have a "Questions" table with question, answer, type, etc. This table is linked to a "Categories" table with a many-to-many relationship using a junction table called "Questions_Categories". The "Categories" table is also linked to a "SubCategories" table with a Categories as the parent and SubCategories as the child. I have attached the database for ease of understanding.

When I enter data into the "Questions" table using datasheet view, I open up a subsheet that shows me the Category and SubCategory fields which have their own lookups.

My problem is the following: I want each category to have its own set of SubCategories. For example, the "Region-Specific" category has "Canada", "London", and "Ontario" as sub categories. However, the movies category should not have any sub categories. The problem is that when I choose Movies in the sub datasheet, the subcategory lookup still shows Canada London and Ontario when I don't want it to show anything. It doesn't make sense to have a Movies category with a London subcategory.

View 13 Replies View Related

Queries :: Export Results From Two Queries To XLSM

Feb 9, 2015

I am a beginner Access user. I’m trying to export the results from two queries "TestData" and "ReportDate" to a saved .xlsm file. These queries are generated from tables linked to a SQL server.

The first query “TestData” should be copied to a sheet named ”TestData” and the second query “ReportDate” should be copied to a sheet named “ReportDate” in the same Workbook ”Test.xlsm”.

Both queries have two parametesr passed from Form1 [Start Date] and [End Date].

Code:
Public Sub ExportToExcel()
'Step 1: Declare your variables
Dim XL As Excel.Application
Dim wbTarget As Workbook
Dim ReportDate As QueryDef
Dim TestData As QueryDef

[Code] .....

View 14 Replies View Related







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