Couple Of Newbie Access Questions
Jul 14, 2006
Okay, I have two questions.
First, if I have a table, and I want to combine two records together, how would I accomplish this? For example, say I have the following table.
NAME SALES
Bill 2000
Jim 500
Ted 1000
And by some miracle of science, Bill and Jim are able to fuse together to become Jill, combining their sales together. So the new table would look ilke this.
NAME SALES
Jill 2500
Ted 1000
My second question is, say I have an extended list of the same table with 30 names and corresponding sales. However, I want to find out Ted's percentage of sales of the TOP 15, not all 30. How can I accomplish this?
Thanks so much in advance for helping out a complete newbie. :)
View Replies
ADVERTISEMENT
Mar 9, 2006
Hi all,
I've played a bit with MS Access 2003 the last week. I've made a form wich should contain information about stores (phone numbers, locations e.g.).
I'm trying to imagine myself (what could be a real) scenario, that way it's easier to give myself practice and tasks :)
As I'm new to MS Access, I'd like you to take a look at my forms. I want to know if there's better ways to do what I have done, and if I have done something wrong.
I also got a couple of questions;
Lets say I want to print one post by clicking on a button, is that hard to implement?
Is it possible to make a button, which exports the form to an .xls -file by clicking on it?
I did make these forms on a computer that had the resolution to 1280/1024, when I tried to open the form on a computer with 1024/768 resolution, the forms won't be displayed in the center of the screen :confused:
I think that's all.. Thanks! :)
Here's the access -file (http://www.home.no/f00b/stores.rar) (180kB) (use winrar to extract it)
I've tried to transalate to forms into english, sorry for the bad english :o
View 2 Replies
View Related
Jun 13, 2005
Hi all
I am new to Access and have just designed a new database and was wondering if anyone could help with a few questions.
1/ I am setting up a Macro to send an e-mail on the change of a form. When the e-mail is generated by Access I get a message from Outlook stating that "A program is trying to automatically send e-mail..." and asks you to confirm that it is ok to go. Is there any way to bypass this check or alternatively send the e-mail from a specific e-mail address rather than the database user.
2/ I have changed the background colours on a number of my forms but the Record Selector's and Scroll Bar stay the standard Access Grey. Is there anyway to change these?
3/ I have used the User Security Wizard to setup users, groups and access but I can't seem to see a way to limit the number of simulateous logins a person can have i.e. I can log in more than once with the same user when I only want to be able to login once. Any ideas?
Any help would be greatly appreciated.
JC
View 4 Replies
View Related
Feb 8, 2007
Hi all,
I'm currently stuck with 2 issues with my database.
1) I have Team (TeamID), Player (PlayerID, CurrentTeamID) & Transfer (TransferID, PlayerID, PrevTeamID, NewTeamID) tables. I got this addTransferRecord form to add transfer records. The Player table has a currentTeamID attribute which is a foreign of TeamID from Team table.
What I'm trying to do is when adding a transfer record via a form and after selecting a particular player from the combobox, I want the prevTeamID combobox.Value to automatically reflect his currentTeamID. Next, after choosing his newTeamID from the combobox, I want the value to be stored in his currentTeamID.
*CurrentTeamID, PrevTeamID & NewTeamID are foreign keys of TeamID
2) I have this LeagueStandings (TeamID, Wins, Losses) table. I've added an expression into the query to calculate the PCT (those basketball fans would know what i mean :) ). The thing is I've been trying to find a way to sort the position and assign rankings to each team where PCT is descending.
Thanks for the help in advance :)
View 2 Replies
View Related
Nov 4, 2005
Hello everyone, im new here and i have some basic access knowledge, little knowledge about VB. Im looking to create a access database using access 2000 -- I know what I need and how to create most of it, however, I do not know how to do a few things, input for improvement or recommendations are more than welcome... Heres the design
Im doing a fairly simple inventory control database, it will have about 8-10 fields:
FAX# (date)
DATE (date)
NAME (text)
EMP# (number)
UNIT (text)
SIZE (text) (this is static and will be combo box drop down)
GENDER (text) (same as size)
POSITION (text) (same as size)
SENT (date)
AMOUNT (number)
RECEIVED (number)
PO# (text)
I need all of that in a form, which I can do, what i also will need on the form is the current amount which be determined by RECEIVED - AMOUNT, which I need to some how show up on the form (this i dont know how to accomplish) ive been trying to put a formula into a text box like =SUM([RECEIVED]-[AMOUNT]) ... it works okay, unless i copy from excel to the table which the form uses, then it doesnt see the items i copied into the table, only the items that were typed in using the form. I also I need to put like a box below the text boxes which will display a query (which i dont know how to do) -- this box will display a query (ie. if i wanted to filter out all , and only see SIZE = xl GENDER = male and then the box will show up in 'table' form all the people who meet that requirement.)
==============================================
= Gender [M/F] Size [XS/S/M/L/XL] Position [K/C/W] =
= Name [ ] Unit [ ] Emp# [ ] =
= =
= Date [ ] Sent [ ] Fax [ ] =
= =
= PO# [ ] Received [ ] =
= ------------------------------------------------------- =
= Search [ ] Amount Left: X =
= ------------------------------------------------------- =
= || || =
= ||---------------------------------------------------|| =
= || || =
= ||---------------------------------------------------|| =
= || || =
= ||---------------------------------------------------|| =
= ------------------------------------------------------- =
==============================================
Thats the best ascii drawing i can do of what invision...
:) Thanks for any help in advance.
AD
View 1 Replies
View Related
Oct 18, 2005
Hi All,
I have finished up a database that I will be using to store code snippets. But I have a couple of nagging questions that have been bothering me.
http://img63.imageshack.us/img63/7752/untitled1cg.jpg
In the right hand portion of my dialog I use a subform to display key words for the code. Is there a way to turn off the column and row headers for the form? Also is there a way to have a transparent background for the subform?
In order to add key words I need to open up another form, enter the words there, close that form and then select them from this dialog. It would be nice to be able to add keywords from this form. FYI, there is a many to many relationship between the key words and the code.
Is there a way to show just my dialog when the database is opened and not the rest of the Access application space? I have unchecked everything in the startup options already.
The last thing is I would love to be able to format the code section so that certain pieces of text are formatted. I am going to start messing around with the some of the free rich text controls but is there an out of the box solution that can handle this task? I was thinking about formatting the code in html and then displaying the html. Is this possible from within Access?
Thanks all,
Steve
View 3 Replies
View Related
Feb 25, 2005
Hello All, I was wondering when you use a find command, if you can make it so it searches two tables from the form your currently viewing, not just the data from the table that is tied to that current form.
Second question is I am thinking about maybe taking some "1 day" classes on access. My question is how many people have taken these & are they worth the money, or is it easier just to buy a book & if buying a book is better what is a good access book to buy.
View 1 Replies
View Related
Jan 4, 2006
Hi there,
I'm a newbie to access and I need to build a database with the following specs:
Be able to click on a specific "skillset" button - 13 Skillsets in total.
This would take you to a list of people who are in that skillset
Then you would need to be able to click on a person and it would take you to the current day, with a list of about 5 stats. Eg. No. of calls taken, Duration of calls. (Data can then be entered / viewed)
How difficult would this be to do if it is even possible? Would anybody be kind enough to point me in the direction of a tutorial or give some advice.
thanks...Tim
View 12 Replies
View Related
Nov 22, 2006
Hi,
I have a few simple questions:
I have a form for employees that contains their salaries. How would I go about creating a macro that would cause a warning box to display any time a salary greater than $100,000 is entered?
On the same employee form I have a textbox to input the email address. After you input the email I want a lostfocus event to check if it follows the same format as "username@email.com" If it wouldn't match that format an error message will be shown.
View 4 Replies
View Related
Sep 14, 2006
Howdy all,
Recently I've been charged with the task of setting up a database for the company I work for. Not a big problem I thought, I sat down and started playing. I've figured out most all of what I wanted to do with one glaring exception.
I have 3 fields of data that I want added together and automatically spit out into a 4th field. If the 4th field was also unable to be altered by the user, that would be all the better.
For exampled I'm looking to add [Field1] [Field2] and [Field3] and have them spit out into the field [Output].
My coding knowledge is limited, and my access experience is almost as poor.
Surely Access is capable of doing this, without having to build some elaborate script or macro or whatever it's called.
Thanks!
View 4 Replies
View Related
Jan 16, 2007
I'm trying to teach myself access. I'm having some difficulty, so let me explain my requirements.
I need a database that allows entry of reservations from multiple locations, and that can track how many of a particular item is being used.
Example: I have 32 devices, but only 7 paths that I can book to these devices. How can I set it up so that when someone attempts to book an 8th device, the system will alert them that there are no available slots for them to use. I would like to make the database available for editing for future expansion also.
I hope someone here won't tell me to take a hike, I know I'm a newbie, but once I dissect an idea, I catch on real quick.
engineerdave
View 4 Replies
View Related
Mar 5, 2008
hi everyone,
as a network engineer, it administrator, etc etc people expect to know absolutely everything about pc's. so here i am.....
a company whose network i look after are using excel spreadsheets on a weekly basis to take orders, wages, running costs etc from their remote sites (currently about 20 sites)
the spreadsheets are accessed via terminal services and are moved via a script at the end of the week so the remote site doesn't have access to the old records and replaced by a new sheet. so lots of spreadsheets floating about.
you can imagine how hard it is to pull past records & compare etc.
now, i'm new to databases and have order a book to gen up on the subject. the sites should only be able to edit the last 7 days of records but be able to see the last years. i can see how a query and subforms can achieve this etc.
each site shouldn't be able to see the other site's records and the head office should be able to run reports etc on each site or as a whole.
i can narrow the time down that everybody is accessing the data. the sites may grow to 50+ but the head office is unlikely to have more than 5 people access the dabase at the same time.
my question is..... (access 2007.... can't see any user security).....
am i better to do an individual database (all exactly the same apart from name, titles etc) for each site that sits in their individual folders via terminal services? then let the head office access each seperate database via linked tables?
my thinking for the above way was security (they can't see each other) and resilience (1 database gets corrupted, the others are still workable) and speed (only a few users accessing each database)
the downsides are obviously the amount of files although this is nothing compared to the excel files at the moment.
any ideas or help would be appreciated.
thanks
louis
View 3 Replies
View Related
Aug 22, 2006
I'd like to first thank you for any help you can offer on any of the following issues.
Situation: I've been tasked to streamline our in-processing requirements for new employees and told that the Access program can help tremendously. In order for this program to work I need to make it as easy to use as possible for some of my more computer illiterate bosses.
Tasker: Design a one-stop-shop program that is in-depth and both easy to input data and easy to show multiple outputs if we want. For example: If Jon Doe is coming into our business then I might need his prior work experience, social security number, date of birth, name, address, phone number, supervisor name etc. I know how to do some of this but I don’t know how to:
1. Make a drop down menu in a specific field to allow only the pre placed answers to work.
2. Allow a supervisor to see a 90 day forecast of only the employees who will require an evaluation. (this would require an easy to navigate program that allows the supervisor to click on the desired output and time frame to see the info)
3. Make it a clean and professional tool that will be relatively simple to maintain for future employees.
Thank you again for any help or reference material you can offer. Have a great day.
AFEric
View 3 Replies
View Related
Jul 27, 2006
I have a database that will be used by a few users who are not very comfortable with Microsoft Access. I have a login screen and a introduction/splash popup. I was wondering if it is possible to bypass the Microsoft Access pop-ups that ask the user if they want to "block unsafe code.." or The security popup that says "This file may not be safe" so that the users go right to the login screen I created within the database. I am worried that there will be variations in answering/clicking these Microsoft Access questions thus causing a variety of complaints when trying to access the database. Thank you
View 1 Replies
View Related
Feb 8, 2007
Hello,
I'm new to Access. I've recently started helping here at a non-profit literacy org. They have a small library and they were keeping track of it in an Excel spreadsheet. I moved it over to an Access database which seems much more appropriate and better in a lot of ways... Anyway, I'm not sure where to look or even exactly what I need to ask but here goes.
I think what we want to do is something like an invoice. The current database has one record for every book in the library and each record has fields like: book name, checked out (yes/no), date due, and borrower name.
It was nice and easy to write a little query with "<date()" on the due date field to generate a list of overdue books, then we used Word to merge the data and generate letters to send to the people with the overdue books. The problem is that most people have checked out more than one book so our simple merge ends up making one letter for every overdue book.
The first thing we'd like to accomplish is: how do we make a table? querry? report? which makes a new table with one record per borrower then each of the books that borrower has checked out?
It doesn't matter to me which way we do it, just so long as we can end up making just one letter per borrower, with that letter having each of the books listed. This seems kind of like an invoice to me, so maybe if I looked at invoice examples that would help? But that might just be more complicated and confusing?
If it's not too hard, there's a 2nd thing we'd like to do, and that is to, either from a 2nd database, or from data in the same database, but in a different "table"(?) get the person's address and merge that into the letter too, based on the borrower name.
Sorry if I'm being an annoying NOOB ~ if you think there are some things I should read first before asking these sorts of questions, perhaps you can point me too them? I'm at the stage where I don't even know enough to know what I don't know yet.
Just for reference, I do have tons of general PC & software experience, and have learned the C/C++, BASIC, Pascal, FORTRAN (77), 65816 assembly/machine code, and even a little LISP.
:)
Christopher
View 10 Replies
View Related
Jan 1, 2008
Alrighty -
After learning that 2007 has no User Security roles, and not having Sharepoint or a SQL server, I decided to work starting with Bob's Simple Login script located here (http://www.btabdevelopment.com/main/AccessSamples/tabid/54/Default.aspx).
I've got it functioning fine and incorporated some of the options also made available here (http://www.databasedev.co.uk/login.html).
You'll see the code below used to store info in a hidden form that is holding the username and permissions level. I'm looking to try and store this information into a global variable instead of a hidden table.
I know that I could define it as a variable right here in the code, but how do I define it as a Global variable so I can use it later in the application in the VBA?
Private Sub cmdLogin_Click()
Dim strUser As String
Dim strPWD As String
Dim intUSL As Integer
strUser = Me.txtUser
If DCount("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") > 0 Then
strPWD = DLookup("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
If strPWD = Me.txtPwd Then
intUSL = DLookup("[SecurityGroup]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
Forms!frmUSL.txtUN = strUser
Forms!frmUSL.txtUSL = intUSL
Select Case intUSL
Case 1
DoCmd.OpenForm "frmHome", acNormal
Case 2
DoCmd.OpenForm "frmHome", acNormal, , , acFormReadOnly
Case 3
MsgBox "Not configured yet", vbExclamation, "Not configured"
Case 4
MsgBox "Not configured yet", vbExclamation, "Not configured"
End Select
DoCmd.Close acForm, "frmLogin", acSaveNo
Else
If MsgBox("You entered an incorrect password" & vbCrLf & _
"Would you like to re-end your password?", vbQuestion + vbYesNo, "Restricted Access") = vbYes Then
Me.txtPwd.Value = ""
Counter = Counter + 1
If Counter = DLookup("[OptionValueNum]", "tblOptions", "[OptionsID]=1") Then
MsgBox "You have entered an incorrect password too many times. This database will now close!", vbCritical, "Wrong password!"
DoCmd.Quit
End If
Else
DoCmd.Quit
End If
End If
End If
End Sub
View 4 Replies
View Related
Jul 24, 2006
Hello again,
I created a form from a query that before loading all the controls asks for the Item ID (parameter). This form has two subforms that depend on another two queries; since I want them to show the info for the same item, all three ask for the Item ID, which can be kind of annnoying and does not look too professional. Is there a way I can get rid of two of the pop-ups and still have the three forms display the info of the same item?
And my second question: After the Item ID is entered 3 times, all the info is displayed on the forms. But, if I click the arrow on the bottom of the form (to supposedly go to the next time...but it should not be possible to do this), a "Enter parameter value" window pops-up (one just like the Item id one), and I have no clue where it came from. I cannot find it in the code; and I really want to get rid of it. I could use all the help I can get.
Thanks in advance.
View 6 Replies
View Related
Mar 21, 2007
Hi,
I'm designing a database for work and am using buttons instead of the navigation features already standard with access.
I need some form of code?? for my button. I want a user to be able to click this button when finished entering data; access will then save the data, close the form and return the user to a main menu, like a switchboard.
Any suggestions or indeed code, lol, would be very helpful
Regards
Chris D
View 3 Replies
View Related
Oct 11, 2005
Ok, I've been given a task to create a hierarchy diagram for my company, unfortunately the diagram has to be created using one excel spreadsheet. I would prefer it if the database could be created using the smallest amount of user input, so all he user has to do is import the spreadsheet to access and hit a button then the hierarchy is created.
I'm currently only going one step at a time, trying to sort the data from the spreadsheet using queries. But this is the problem:
Raw Data table
ID, Firstname, Lastname, Status, SupervisorFirstName, SupervisorLastName
Table1
ID1, Firstname1, Lastname1, Status1, SupervisorFirstName1, SupervisorLastName1
The idea is that the hierarchy diagram should start with the boss and move down to the mere siblings like me, and everyone will be connected. But for one reason or another this isn't the case, so at the moment I am trying to move the persons record if their supervisors name is not in the tree, so the SupervisorFirstName and SupervisorLastName are not in the Firstname and Lastname column.
I have been using the following SQL
INSERT INTO Table1 ( ID, LASTNAME1, FIRSTNAME1, Status1, SUPERVISORLASTNAME1, SUPERVISORFIRSTNAME1 )
SELECT ID, LASTNAME, FIRSTNAME, Status, SUPERVISORLASTNAME, SUPERVISORFIRSTNAME
FROM RawData
WHERE (((RawData.SUPERVISOR_FIRST_NAME)=FIRST_NAME) AND (RawData.SUPERVISOR_LAST_NAME)=LAST_NAME);
When I change the LastName to "SMITH" I know how many records I should get and it works, but how can I go through both columns finding any matches??
I may have described this quite badly so if you need any more info please let me know.
View 3 Replies
View Related
Jun 29, 2006
Hi all,
I'm just having a dabble in VBA for Access so I'm fairly newbish!
I have a field [Reference Number] that increments in the table by 1 everytime - but the user has to enter this manually (its not auto-incrementing). How can I do the following: when the user selects a new row in the table it will automatically look in the table for the last entered [Reference Number], increment it, and put that value in the Userforms TextBox??
Hope you understand this,
TIA
View 5 Replies
View Related
Jul 12, 2006
I hope this is the correct place for this type of question.
I have two tables, let's call one the "record table" that holds each record of interest. Let's call the other one the "rule table," as each entry is a "rule." The two fields of the rule table are rule and description.
I have a form with a list box and a text box. I'd like the user to be able to choose a rule from the list box and this would control the "rule" field in the current record. However, I'd like the text box to display the description corresponding to that rule, by looking it up in the rule table.
I know I've probably made it sound very complicated, but I assure you I'm trying to do something very simple here. Thanks for any help anyone can give me!
View 10 Replies
View Related
Oct 5, 2006
I am fairly new to Access, so please bear with my hopefully easy to answer questions. I am trying to create a database for a university where we input all of our student leaders information (name, email, student ID number, etc) along with the groups they are involved with so we can track and see who is involved with alot, and so we have easy access to their contact info if we need to do that. Anyway, I have already created a database to take in all the information and spit out the reports. It is probably pretty messily put together, but like i said, this is my first time working with Access on this scale.
My question is, when I created a report to display all the students involved with a specific activity, I ran into a bit of a problem. There are two students with the same last name, and for the activity in question, they both should be shown. However, it only shows one of their names (I have it set to show the last, first) but it shows both of their activity status. What am I missing to be able to split the names show its shows both names?
Here is a link to download the access file, and perhaps someone can tell me how to fix it.
http://us.f13.yahoofs.com/bc/440a2a54me2cd2c62/bc/access/Involvement+DB.mdb?bf8mTJFBardSN6ge
All of the sensitive information has been removed. It will still work without it though.
Thanks!
View 3 Replies
View Related
Apr 27, 2007
I am trying to learn first how do i lock a db so that it will keep users from tampering with the forms and queries, second i am trying to learn how can i transfer the form, queries, etc. to a new ms access database without having to recreate them all over again. I recieved updates from a source of mine and it contain new information, the old database is archived. And the new db with new information is keep separate from the old information in the new database so i had to recreate the form everytime.
View 1 Replies
View Related
Aug 23, 2014
I'm trying to build up a database for Multiple choice questions quiz in MS access 2010.My requirement are as follows
My quiz consist of 15 questions. Each questions carry some weightage. Suppose a question carries a weightage of 5 and there are two correct options like A & C. If user selects option A then he gets half marks.Multiple users can access the test at the same time.
View 1 Replies
View Related
Aug 6, 2005
Hi all,
I'm building a db to catalogue my prints collection. I'd like to group them in categories. With the examples I found on this site I've been able to include cascading comboboxes in a form and they cascade beautifully (3 categories: maincategory, Subcat1 and Subcat2). Problem is though, I cannot get them to work in combination with a unique record! I have tried all sorts of combinations to link the cascading boxes to the print but they either don't change at all, or my qry returns 0 records. I must be doing something simple wrong but I'm not very experienced and just don't see the solution. I've spent all evening so far and I think it's time for some help.
I've got these tables (there are more tables and fields, but these are the main ones)
Tbl_prints
PrintID
PrintTitle
Tbl_Category
CategoryID
Category
Tbl_SubCat1
SubCatID
SubCat1
TblSubCat2
SubCat2ID
SubCat2
PrintID
The tables are linked by the printIDs.
Now, when I fill in the details for print #1 and then advance 1 record to print #2, (or #3 etc) the comboboxes don't follow.
What would be the solution for this?
Thank
View 3 Replies
View Related
Mar 23, 2006
I have a table (Master) that contains columns such as MsgLocation, MsgName, MsgScript, and MsgTxt. I want to create a form where the user would have an area to type in a 'string' and when they hit enter (or select a button), a (background) query would execute a lookup on master "where MsgTxt LIKE %string%"... For example, if a user wants a list of all MsgTxt values that contain "Please hold" they would type in a box "Please hold" and the query would use %Please hold% in the search criteria.
I don't have a clue on the first thing to do to get this done. And I'm new at this...
Thanks!!!!
Mary :)
View 9 Replies
View Related
