Hi,
I'm new to queries. I've managed to build a very simple query which joins two tables. How to I save these results so that I can export a tsv file from them?
(I'm using Access 2000).
Thanks very much for your help...
I have a form that carrys out some calculations, I know it goes against the rule, but can I save the results of the calculation into a table. When I try to do it at present it puts the rusults into a drop down list in a table. How can I overcome this please?
View 2 Replies View RelatedI have a database of around 15,000 users and I'd like to create a query that I can run on a weekly basis and save the results to an Excel spreadsheet. The results need to be logical and understandable by my coworkers.
Unfortunately, the actual results of the query are not (in their raw form) logical or easy to interpret.
Let's say I have a table called "users" and within that I have:
Surname
Forename
FieldA
FieldB
FieldC
FieldA has a value of either NULL or a 12-digit number
FieldB has the values are "ENABLED", "DISABLED" and "N/A"
FieldC contains a value of either "1" or NULL
This means nothing to my coworkers who want each user to be sorted into a "category". As I'm running this on a weekly basis, I'd like this query to do the work for me, so I don't have to manually assign everyone to a category in Excel. Plus, of course, there is no chance of human error if the query does this for me.
Sooo... I'd like my query to categorise for me as follows:
Category1 = FieldA IS NOT NULL and FieldB="ENABLED"
Category2 = FieldA IS NOT NULL and FieldB="N/A"
Category3 = FieldA IS NULL and FieldB="ENABLED"
Category4 = FieldA IS NULL and FieldB="N/A"
Category5 = FieldA IS NOT NULL and FieldC = 1
... etc.
I'd like the final column in the query results to simply list the category name, so I can simply copy and paste the data into an Excel spreadsheet and be done with it, safe in the knowledge that it makes sense to all.
The database has three tables:
Equipment: 3 columns - ID, Nomenclature, Location
Procedure: 3 columns - ID, ProcedureNumber, ProcedureName
PMHistory: 4 columns - ID, Nomenclature, ProcedureNumber, CompletionDate
The PMHistory-Nomenclature is linked to Equipment-Nomenclature so that when an entry is made into PMHistory-Nomenclature there is a drop-down window showing all the equipment available. PMHistory-ProcedureNumber is similarly linked to Procedure-ProcedureNumber.
Assume four entries into the PMHistory table:
Equipment1, Procedure1, 3-September-2006
Equipment1, Procedure1, 19-September-2006
Equipment2, Procedure1, 4-September-2006
Equipment2, Procedure1, 20-September-2006
Equipment3, Procedure2, 5-September-2006
Equipment3, Procedure2, 21-September-2006
How to write a query so the result will only contain the latest date for each Equipment/Procedure pair.
The desired result is:
Equipment1, Procedure1, 19-September-2006
Equipment2, Procedure1, 20-September-2006
Equipment3, Procedure2, 21-September-2006
Regards,
Hardin
Hi: Can any one please help me how to i save values in '1','2','3' after users enters values in a textbox like that 1,2,3
WHERE ((table1.field1) In (SELECT Cstr(Nz([field22], "")) FROM [table2]));
table1.field1 is type text and values are store in like 1,2,3
table2.field2 is type number
The SQL "in" will not be work until i put the values in field1 like '1','2','3'. So i want when user done with entering values i will be change them into '1','2','3','4'. If i use a listbox, does listbox save values like that i mean in quotations automatically?
Thanks.
I have a query that calculates the number of games won and lost over the course of a season.
I would like to export the query results as a report in Excel format.
What I need to do is take the won/loss field in the query
WonLost:([GamesWon]&"-"&[GamesLost])
and export the result as text.
When I do it as above, the results in Excel are numeric.
Example: Games Won = 12 Games Lost = 3 it exports it as 9
(12 subtract 3).
have a table i am creating for a subform to calculate a price for a line total.
[item] [amount] [weight] [yeild] [linetotal] *[need coverted price from a query]
the converted price is in a query and i would like to load it with its selected item in a table (or form?) so i can multiply for a line total in the subform.
is this easy?
We have Cardiac Rehab patients that come 3 times a week (M-W-F). Once they are on program they stay there for 3 months. There are classes given hourly from 8am thru 6pm. There are 12 available slots per class. Right now this is being handled in Excel, but I thought I might improve things by putting it in Access.
The Excel "form" I built has fields for PatientName, MedicalNotes, DoctorName, and Notes. Because the names rarely move (a patient gets a slot and stays there) the user opens Excel, reviews the form to make sure no one cancelled-is out sick-etc, then prints the schedule. When the day is complete I have a macro that takes this information and saves it in a tablelike manner on another sheet. A dynamic pivot table references this info to give the user quick answers as to daily volume, etc.
The thing I can't get my head around is how to make this as easy in Access. My thought was I could have a PatientT, listing all of the fields mentioned above. I could have DailyF open, bound to PatientT, with additional unbound textboxes to add "today's date", cancellations or no shows, and then Save all of this in another table and print out the daily schedule.
Access 2010:
The database I'm working on stores label images, or better stated, it stores file paths where the label images are stored. These paths are used in reports and forms to view the label images within the database. What I'd like to do is to create a "save" or "email" button that will allow the user to select a location to save just a copy of the image in jpg format. I realize that it is possible to imbed the jpg image into a report and export it as a pdf. What I'd really like to do is find a way to save the image (perhaps by copying it from the path in the database and simply relocating it) and preserve the jpg extension if that is possible.
I am also aware that there are third party applications that will convert pdf reports into jpg files however network restrictions keep me pretty limited in my ability to download third party software.
Hi all,
newish to Access so please forgive if this is really easy.
I have a database where people have answered questions to a survey. The returns are
Yes
No
Don't Know
I want to find a way of showing how many of each entries are in each question.
At the moment I am exporting to excell and using pivot tables, but I am sure there must be a way to do this inside Access.
All help appreciated.
Thanks,
Chris.
I have a query which pulls up results based on the value of a text box. All works well except one field has a DOB (date) and if I search say for " /1976" I get no results, however search /76 and all the people with that year of birth show then show up. The date is stored in the dd/mm/yyyy format.
I'm using the like [forms]![clients]![searchfield] in the criteria of the query to bring up results.
I have created database but I now have 3 questions. I havce changed a subform to view as a pivot chart but I am unable to split and group in a similiar waay as in excel, is this just something I have to accept because otherwise my "lets have a works database" theory is shot.
2nd I would like to create a switchboard how do I do this.
and lastly some of the data I would like to utilise for mail merge document ( name and address stuff) are these easy to set up??
thanks very much
Andy
I am completely new to Microsoft Access but otherwise consider myself to be a Tech Geek. I have made a very simple and small database. It contains the name of my customers and the orders they have placed with my company over the past three years.
Now i want to generate report which when given the name of my customer would give me all the orders placed by that specific customer. I know how it can be done query but i want to do it through some kind of access page so that my subordinate who is very technically challenged can manage that.
Though i know it is a very basic question but i tried everything and could not do it....can somebody please please please help me.!!!!
Hi. I have a problem. I want to make a search form that will collect info from several tables and show the results in a list box, like a list(for example with the same name but with different other attributes ). I know it is possible and it is not hard but i'm novice to access. Thanks in advance :)
View 1 Replies View RelatedI'm trying to set up a database from scratch and have never used Access before. It's being set up to hold information gathered from a survey of roughly 2000 people on their fave Manchester songs and artists.
It's going to include their name, email, city and 1,2,3,4,5 choices of fave songs. The aim is to have everything in a simple datebase where I can work out who has voted for what in each section, how many votes a song got a position 1 and so on. But I also want to be able to work out the total number of votes over the five catagories.
Is there a way of doing this? I can bring up information by using query but can I be specific as to what information the query brings up?
This is probably a stupid question but any help would be very useful. I'm using the Idiots Guide to Access and as I said this is the first time I've ever used the program only installed it yesterday!!
Ta
Nick
www.PrideOfManchester.com
Hello, this is my first post. I am very much a novice and have come across a problem that I'm sure is simple to solve but so far the solution has eluded me. All I want to do is subtract a field in one record from the same field in the next record in a query or report but I have no idea how to do it. Can anyone help please ?Thanks in advance.
View 1 Replies View RelatedHI all,
I am a beginner at access, please help me.
I need to create a customer id filed that has letter and number together, i.e. ZH72773. And the id must change incrementally, but no number 00000. Also it should change the letters after the number reaches 9999, i.e. ZH9999 --> ZI0001.
Would anyone tell me how to do this, please.....thanks in advance
Songrit
Hi everyone, heres a question I hope is easy to answer:
I have two tables, one containing about 1500 entires another with about 43,000.
I want to find out which entries in table1 are also in table2.
I really appriciate the help, thanks so much!!
I am quite new to access and am not sure if what i am trying to achieve is quite simple or needs some VBA Code written.
I have a large table (about 10,000 records) with a varitey of different fields, including "Member ID", "Banker Name", "Location" etc. I would like to develop a form where I can filter on a field such as "Banker Name" or "Location" (using a list box) and below (maybe in a subform??) for all of the records to appear that fall under that banker or location in a datasheet view.
Any help would be greatly appreciated.
Sam
I'm brand new at Access and would appreciate your help.
I have an existing database (table) and would like to make a duplicate table so that I can modify the new one without messing up the old one. How do I duplicate the table either w/ or without the data?
I've created a name tag. I'd like to create another name tag that's the same format except for a different group of people pulled from the database. How can I do this without having to re-create the name tag all over again?
Thanks for your ideas!
Hi,
I work in the quality control business and recently purchased a system which on first inspection seemed like the way forward, however I have since discovered that imputting the data is very slow.
I wondered if Access could be programmed to create a better database.
When a sample is tested, I would like the numberical values to change colour depending on whether it is inside or outside the specifications. (Is this possible?)
The main problem I can see is that samples of different substances will be analysed and each substance has different specifications. (Is it possible to set this up?)
I hope this question makes sense to you all
Any advice would be greatly appreciated
Thanks
Jimmy
In the interest of speed in my current project, I'm working with bound forms, where in the past I have developed mainly using unbound forms. Hence, the word novice in my subject line.
Is there a way to do the following, and is it typically done?
The layout: A typical, record by record data entry main form, with a linked datasheet subform. The underlying query contains person's names and other associated data. The subform is a datasheet with an alphabetical listing of the person's names.
My goals (2 of them):
1. To have the datasheet subform go to the record selected on the main form.
2. To have the main form also go to the record selected in the subform.
The forms' On Current events conflicting with one another, as the subform's Current event fires whenever the main form's does.
could anyone advise how to get a date written to a field when a check box is changed to "yes"? i'm useless at VB!
thanks.
Hi everyone,
Basicly I am doing an Access Project for my school coursework, but I am really confused and my teacher isn't that great.
My coursework is based on a video rental company.
The database (in breif) has to be able to bring up a customers details, and add a loan to/under that customers details. (as you would expect from a video shop).
Now, to be honest, I dont know if I have the correct items in each table, but was wondering if someone would be so kind to have a quick look to tell me what to change and where to put the relationships for the database to work correctly.
I have taken a screen shot of my current database:
http://img259.imageshack.us/img259/8216/accessproblem4de.th.jpg (http://img259.imageshack.us/my.php?image=accessproblem4de.jpg)
If you would like any other information, please let me know.
I am really grateful for this!
PS65
Hello all,
I wouldn't call myself a beginner at Access but I am nowhere near a skilled programmer. I have a database I have to finish in the next several days and have some challenges that I have searched and searched the forums for answers to and can't seem to find the answer. Maybe I am not searching the correct terminology but I just can't find the answer(s).
#1 - I have a subform in which we enter quotes. Our quotes are for repairs to train cars and like automotives, they are done part by part (line by line). Each job per car can start out with say 5 lines and be modified several times (estimate and supplement) and wind up with as many is 80+ lines per job. To handle this, I have created main form with the job data and a subform containing the ob detail with a checkbox on each line so that we can select only the lines that need to be printed per quote and/or modification as they have to be submitted to the customer independently. This works great HOWEVER, since there are sooooo many lines and the check boxes have to be checked and unchecked for various reasons what I need is a check box or control that will check all or clear all of the checked boxes. Can anyone help accomplish this? I can't figure it out.
#2 - Based on that same form and checkboxes we another form/subform that has 2 columns, 1 for estimate approval and 1 for supplement approvals. These are set-up to feed into our customers required billing format. I have a pop-up form that pops up the lines as they are approved by the customer based on those same check boxes and what is being approved. This is working great as well, HOWEVER, again, these can be as many as 60 or so lines long and the approval number has to be entered into the appropriate box over and over and over. Is there a way to add a text box that has estimate approval and another that has supplement approval and auto populates the appropriate box (estimate or supplement) based on the value typed in the box? I have this working and it populates the fields but isn't saving them in the table.
#3 - I can't figure out how to set form size. For example....I have a form that opens to full size and I want the subforms to open to a smaller size. I have tried setting this but they seem to all still open to a full size form.
Thanks so much for any help you can give!
Dianne
Hello, I am pretty new to Access. I am able to create simple forms that will interact with tables, reports, etc.. I am trying to go a little farther and customize a form that will automatically be displayed when the user opens the file. I have read that you must have 'modal' properties set to yes. I have done this and when I enter then .mdb file, I still see the default menu.
Do I have to write an expression within the properties? And if so, on which command?
Also, I would like to become more familiar with customizing forms. Is anyone familiar with a web based step-by-step tutorial out there that will walk a person through the process? I am not satisfied with the Help menu.
Thanks for the help.