I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.
Any pointers in the right direction would be greatly appreciated.
I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.
What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.
Does anyone have any suggestions of how I can do this? :confused:
I am trying to group records in a query and count them. I have records containing ABC12345
ABC67890 ABE12345 ABE67890
Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.
Given a table field that is a hyperlink type. I need an Update Query to set all records of that table so that the Displayed Value part of the hyperlink field (not the Address part) is set to a particular value. Any ideas how? Thanks.
I tried to word the title as accurately as possible. What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.
I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.
There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code: SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU FROM Calls GROUP BY Calls.SKU HAVING ((Not (Calls.SKU) Is Null)) ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
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?
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) ) However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
In one table I want to limit the options of one field depending on what is chosen in another field.
Field Type has two options: Receivable and Payable.
I want field Sybtype with this limits:
If Receivable is chosen in Type field you will only have CreditCard & Lockbox available in the Subtype field. If Payable is chosen you will only have Cheque & Transfer to choose. Shall I have two separate tables? or only one table with Type and SubType as below? but then how can I make the above work?
Type SubType Receivable CreditCard Receivable Lockbox Payable Cheque Payable Transfer
Apologies, I have no idea if this should go in reports, queries, macros or modules and VBA as I'm a bit stuck but hopefully it's fairly simple.
I've been teaching myself access and it's been going well. What I have now been asked to do is produce a report that generates the shift patterns for everyone in the office as an HTML document. Now, the data is all exported from another program and I've had no problem getting the data into access easily. The problem I have is the format some of the data is in.
The major one that I need to solve is showing what time people are meant to take their lunch break each day. The field for break is filled in in the following format.
07/04/2008 12:45:00
Now, I have the date from elsewhere so I really don't want the date to show up so I need something that removes the date from this field. Is this going to be easy to do? (Ideally I'd like it so that the above example actually just returned 12:45 but if it has the 00 on the end that would not be the end of the world)
Apologies if this is in the wrong part of your forum.
I'm working on this database in which I have 2 forms:
one form has personal information (PersonalInfo) and the other one is a search form (Search)
In the search form I have a field in which I want to type any part of the Name field in PersonalInfo form and I want to retrieve all the records that have this part of the name.
Here is what I tried:
In the PersonalInfo form, I want to the criteria of the NameField and typed the following:
I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.
I have a table with names in a field called [Name] in the format Mr John Smith I need to append the names to another table but to 3 separate fields for title, initial, surname I know the format for example Left([Name],1) to take only the first letter, but how do I tell it to take from the left up to the first space for the title, how to take between the first and second spaces for the first name etc? Any help gratefully received
Hi all, I am trying to update the value of a field from say, "Word" to "Test120+". I am using the SQL code as below....
UPDATE tblTable SET tblTable.Field = "Test120+" WHERE (((tblTable .Field)="120") AND ((tblCurrent.Field02)="TT"));
But the end results are that the value has been changed to just Test120 !! The plus (+) sign has been excluded. Is there anyway I can force the plus sign to be used in this update query or has Access some sort of bug because the plus sign is used as an arithmetic character that prevents it from being used as a value in a query ?
I have a table with 84000 records in I need to fined all the records that have the same First two starting characters and the same last two characters but are different between the first two and last two characters.
Ie. 30123456757 302356757 301234567
I just want the ones that have different number in between . I have tries ever Select statement I can think of and still coming up with every thing but what I need.
I want to ba able to search for only part of the field in a query. I want to run a reort from a query where the user only needs to enter part of the information from a product list ie the items listed may be as lisetd as make model in the same field. the user won't be able to get to the query to alter the criteria. i have tried: Like "*" & [Enter Product Name] & Like "*" Which I found on a previous post but this returns a syntax error, and highlights the second Like
I have a table - RDC/NDC - that holds details about various warehouses, including a field - [Short_Code] (TEXT) - to hold the warehouse short code or ID.
I have an import table - PickDataImport - that contains details of goods picked, including a field - ToAssignRef (TEXT) - that is made up of 3 elements; Type-Short_Code-Date (EG GREEN-MAN-210215).
I am trying to create a query that will return the warehouse name from RDC/NDC by looking up the depot short code in the ToAssignRef.
The Type element of ToAssignRef can be any length of text.
I am thinking i need to use HAVING or IN (or a combination) but everything i try fails - either blank or errors.
I have a fairly simple database that looks like the attached. It is the beginnings of a school database. Unfortunately, the MS template doesn't quite do it.
We have around 700 pupils in 5 year groups. Each year's intake is assigned the year of that intake (e.g. 2002, 2003 etc).
The youngest pupils are in Year 7 whilst the oldest are in year 11.
Each Class is assigned based on the intake year but given the name of the schooling year - (e.g. year 7, year 8 etc). And there are 6 classes per year - hence names 7.1, 7.2, 7.3 etc.
My "Students and Classes" table should combine both the classes and pupils. However, I want to be able to firstly select the class to put the pupil into (and hence infer the induction year), then just show in the next filed only the pupils that belong to that induction year and hence go into the selected class.
However I design my lookup or query, I can't seem to figure out how to do this, or if this can even be done.
I hope you'll be able to help since I think it is going 2 B a tough one. I'm looking for a code that will do the following:
I have a table (lets say customers) with a lot of names and instead of typing again and again the same name I would like to have a kind of an auto complete mechanism which will find the name from the table and use it to input it back to the table itself and if it isn't there then it will use the new name as an input, exp: in table customers I have the following names: ACC, ABC, ABD, EFG ..... lets say I'm typing A then the ComboBox will show all the names that starts with A (e.g. ABC, ABD & ACC), if I will continue typing A=>B then only the names ABC & ABD will be shown then I can choose one of them or continue typing ABD which will cause only the name ABD to show up then I could use this as a name to input to the table or if I create a new name like ABDG then this one will be used.
I hope I didn't mess things up and explained myself correctly. Thanks in advance Sean
