I have a query that I try to update to Alphabetical instead of numerical. I am using MS Access. on the row say "Update to" I Enter Alphabetical letter but it doesn't work. I wonder if any way to do so. Please HELP!
Thanks
Hi,
I have a database in which info is imported from a flatfile.
In the database I have a query to select relevant info to appear on a report for invoicing.
Now the imported info adds a number in a column for all rows.
example: it adds 9600 to all invoices, based on the info that is imported.
Now I want the import and export invoices to be separate, and the numbers to display 9600A and 9600B.
Is there something I can add to the query in order to get this result?
Thx,
Lion85heart
Reading this, I think it might be a bit unclear, so pls. ask any questions that you might have.
Hi i have to design a member database in access,
As the membership number i need to use the first letter of the surname,
then have an auto number.
e.g Smith, if the last member was S47 then Smith would need to be S48,
This will be used on a form for a video hire shop, so, the customer details
will be entered, is it possible for this auto number to work just by putting the name in
the surname field?
I only know basic Access so please help.
Thanks a lot
Nick
Hello,
I was hoping someone could help me with a validation rule for a table.
What I require is for you only be able to enter specific data, for example the first character must be a number and be 7, 8 or 9, and the second character entered must be a letter, only letters available are A, B, C, D, E, N and S. For example these will be accepted 9C 8D 7B 7S 7D 8E, other will not (3S, 7G, B8)
Can anyone tell me what it is i enter into the validation rule box or direct me to any online help with this.
Thx
I am trying to use access to connect to SQLServer with an insert query. I need to generate alphanumeric number for each record. The letter will move to the right as the alphanumeric number increase.
For example: A0001 , A0002.... -> A9999
0A000, 0A001 ... => 0A999
1A000, 1A001 ... => 1A999
2A000, 2A001 ...2A999
9A000,9A001 ...=>9A999
00A00, 00A01 ... => 99A99
000A0, 000A1 ... > 999A9
0000A....=> 9999A
Last number will be 9999A
HI!
I have big problem I have telephone number field like this
Tel(XX)XXXXX - X are numbers
I have to split it into two columns and skip brackets like this
Column1 - Tel
Column2 - XXXXXXX
Is it possible?
I have no idea how to manage it...I'd be very thankful for any
examples ? Or Help....I'm beginner
Thanks a lot
in Advance
I am using an MS Access 2010 table as a data source for a mail merge. I would like to update a field in the table with the date that the letter is printed. Is there a way to do this?
View 5 Replies View RelatedHi,
I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.
How can I do that?
Regards,
CS
Hi there
Im working on an update query to add 2 zeros to the front of a field where it is less than 8 digits long. I'm not sure how to go about this, can anyone help me out?
Cheers
I have a stock record database which I have 'inherited' from someone far cleverer than me!
I'm fairly used to basic SQL, but I'm teating by hair out over this particular problem.
I'm writing an Update Query to count the number of [NewModelCode] records WHERE Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes and update a field called CountNew on a table called InstStockReference..
So far I have the following SQL:
UPDATE InstrumentStock INNER JOIN InstStockReference ON InstrumentStock.NewModelCode = InstStockReference.NewModelCode
SET InstStockReference.CountNew = DCount("NewModelCode","InstrumentStock","Status='Available'")
WHERE (((InstrumentStock.Status)="Available") AND ((InstrumentStock.StockType)="New") AND ((InstStockReference.OnlineShop)=Yes));
It partly works, but the result I get is a column count of 939 in every field where the Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes.
I want to do a count the number of [NewModelCode] records.
Something's gone wrong? Any ideas?
I have Access 2000. I want to update all my records in one table so that they only have 2 decimal places instead of the 10 they have now. What is the expression used to do this?
Thanks
Chad
I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.
How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?
I'd like to run an Update Query on a limited number (or percentage) of records from a Button on a Form.
[URL] .....
But instead of opening a Form, I want to Run an Update Query instead.
ok here is what i want.. imagine a search box, just an empty text box that allows the user to type things into it...
there will be a simple table from the database behind it, here is the typical structure of a record
<Country>Poland
<Price1>60
<Price2>85
<Price3>95
in this table there will be say a 1000 of these records...
my user is going to have to quickly search through these records while on the phone to a customer, and quote one of the relevant prices associated with that country
what i would like is this most convenient system..
as the user types in the letter 'P' just below all the records with countries that begin with 'P' are displayed (with a scroll down arrow if needed)..as well as the 3 relevant prices with that record
if they then type an 'O' into the box (which will now hold 'Po') all the records with countries beginin with 'Po' will be displayed (eg Poland)
if they delete the 'o'. once again the displayed records below the box will return to just the countries begginin with 'p'
all that is needed is for the records to be displayed, thats it.. but the adding and deleting of the letters within the text search box will need to instantly manipulate this list..
i have posted on other forums, and have been told this can be acheived in access, is it hard?
Hi,
I'm fairly new to Access. I have few questions.
How to set up database so it searches through my table, finds the name and inputs it into letter in a space I want?
Where does this letter suppose to be located in the file?
Thank you
IOI
Hi, anyone know if it possibe to create a field within a query that looks at a 3 letter number code in a field of a table named "employee master" to see if the last number is an "I" or not. So for example a number code in a field named "Department" is "KTI". I would like to display "indirect" in that field if it contains a "I" in the 3rd letter of the number. And display "direct" if the third letter is a "D" intstead, tried doing IF statements but not sure how to go about it, any help would be great.
View 5 Replies View RelatedHi. I have a load of records, that some of the first letter of a field, are not capitals, otherwise they are ok. the are loads of them though, so would take too long to go through by hand, is there a query i can run, to check it and change it if not?
Thanks
Alex
I am assisting with the design of a db, where depending on two criteria, one of two letters needs to be printed. If one check box is positive, a letter is to be generated. If it is empty then a second checkbox will be checked, resulting in a different letter.
I appreciate any assistance.
:o
All, using access 2010. I am working with an existing database that have a letter programmed using fields in a query. I have to add to it but having a trouble. Merge in Word was not used. I can't find any examples. The letter is designed in a report design. Using the fields from a query and filtered by "IIF Statements".
View 3 Replies View Relatedis it possible to extract the first letter of a surname in an Access Query? Below is my sample data, and below that is what I want to acheive.
Player Name
Hart, Joe
Cech, Petr
de Gea, David
Szczesny, Wojciech
Mignolet, Simon
Reina, Pepe
Lloris, Hugo
Howard, Tim
Begovic, Asmir
What I want the query to return:
Player Name
Hart, J
Cech, P
de Gea, D
Szczesny, W
Mignolet, S
Reina, P
Lloris, H
Howard, T
Begovic, A
I'm trying to create a report for how many "nasty grams" (rejection notices) my company has sent to people who keep sending in paper forms when they are supposed to file electronically.Every letter that goes out has information recorded based on whatever they sent to us - so the only remotely reliable way to count how many each person received is by the address on the envelope (people use different names, different business names, use different telephone numbers on the forms, etc).
I just built several queries that feed into a report that gets sent to my boss on a monthly basis to show the people who've sent in more than one paper form and have received our rejection notices more than once.I'm not the greatest at SQL, but I've been trying to find a way to use DISTINCT Addresses, leave all other fields the same (not DISTINCT), to:
1. Only return people who have received 2 or more letters
2. If at least one of the letters was sent more than 90 days ago
AND If at least one of the letters was within the last 90 days
-If at least one was within the last 90 days, only display the most recent send date of the letter (lots of people get back-to-back letters).
3. Display their names, addresses, telephone numbers, the date of the most recent letter sent, count of the total letters ever sent to that person. (the report will already do this, just need Max date)
My first query counts the number of times each address appears in the main table and simply only has [Address] and [CountofAddress]
My second query has the [Name], the [LetterDated] >=Now()-90, and the qryCountofAddress is linked to the main table by [Address], using [Countof Address] >=2...I have tried Selecting Unique Values in the Properties tab. Yes, I have tried INNER JOIN (but can't get the rest of my fields to display once I make addresses distinct).
791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.
View 1 Replies View RelatedI have an expression on a Query which calculates the remainning stock number.
The stock number is a field stored in a Table. This is subtracted by the quantity of the orders.
Is it possible to update the stock number in the Table? This number is the remainning number.
And how? Please advise
Hello All,
I am trying to make an Update Query that will update a table that has the same account numbers and assign them a value....ie.1,2,3,4 and so on. Does anyone know how I can do that?
Example:
Accout Num Assigned Value
12345 1
12345 1
12345 1
12544 2
12544 2
12568 3
12569 4
This database is for a livestock show that I have been working on for quite some time now. This specific 'section' is for the Supreme Competitor award, in which points are given for the showman's placing in showmanship, ONE highest placing animal in market classes, ONE highest placing animal in breeding classes, and the showman's score on a quiz. I'm having a problem assigning 'points' for a single highest placing animal in market and breeding classes.
I would like to create a query/s that selects all animals shown by an exhibitor in a market class, then take the highest placing animal that the exhibitor had and award (update the livestock table-points field) points for a single animal. This is fine until one exhibitor has MORE than one animal that received 1st place. How do I go about telling the query to just pick one, lol... it doesn't matter which 1st place animal it selects to award the points... just as long as only ONE animal is updated and not all of the exhibitor's animals that received 1st... Then do this again to select one highest placing animal from the breeding classes.
Here's a little outline just for visual sake:
Market Classes
Name Tag No Class Placing <points field update>
Sally Johnson 100 1 1st 10
Sally Johnson 101 2 1st
Sally Johnson 102 3 1st
Kim Smith 200 1 2nd 5
Kim Smith 201 4 2nd
Kim Smith 202 5 3rd
See where Sally received 3 1st places, but only one of them is selected to be updated, and Kim received 2 2nd places (her highest placing) and only one is updated.
Thanks SO much in advance... this has really got me stumped.
Well, here's my database :
Name : String
Address : String
I want to add an automatically generated column, "no.", to show which number it's in. Like this :
No. Name Address
1. A Unknown
2. B Unknown2
The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.
Is it possible to generate query that have "no." column in it ?