Noob Need Search Guidance
Dec 11, 2007
Hey everyone,
I'm new to access, I have been using Excel for quite a while and I am familiar with VB and macros.
I have never been much of a book learner, mostly hands on, which means a lot of reverse engineering to figure out why/how things work.
I have a scenario, and found a DB that had some functionality that I liked and I had reverse engineered it to do a lot of what I want.
Where I need some guidance:
1. on my opening form (Clients) I would like to be able to type in Client Number and have it return the correct info.
2. in my Who Has It is there a way to do data validation (or dropdown box) I mean I only want them to be able to enter valid names not crazy stuff like MickeyMouse.
Thanks a bunch!
View Replies
ADVERTISEMENT
Apr 25, 2006
I think I can achieve what my boss wants with just one table.
I work in a legal department and we track employee contract complaints/issues. Simple data entry is used and one table (Complaints Table) has been created with fields like: employee name, emp #, what union rep issues the complaint, who from our department responded, when the complaint was sent, when the response was sent, two memo fields to describe the problem and the resolution, and ... perhaps most importantly... contract section.
It is possible that one complaint could touch on multiple contract sections.
The data-entry form I've created has the following fields:
contract section 1:
contract section 2:
contract section 3:
Now I need to create a Search Form and I'm having all kinds of headaches trying to understand how to make this work... trouble with setting up the right macro, too.
I'd like the main form to contain the following search fields:
Rec # (which is set as an autonumber in the "Complaints Table")
Employee name
Employee #
Contract Section
...or perhaps just a "contract section" search form...
1. If a contract section # is entered, I want to push a button and have every record of that contract section # filtered from the database and show exactly as the data-entry form beneath the "search" portion of the main form.
(did that make any sense?). I know how to create a general form and subform, but the subform always shows up as a table object rather than a form object. How can I fix that?
2. Some contract sections might be numbered: 3.A.2. ....others might have a longer name like 22.C.3.a.vi.(b). ....
Obviously, the filter/search needs to catch all records which include the contract section specifically, regardless of which of the three "contract section" fields from the main data-entry form contain data.
What's the easiest, non-VBA way of setting that up?
I just know nothing of VBA.
Any help is greatly appreciated...
tango..
View 3 Replies
View Related
Nov 5, 2007
Working on a database that tracks codes, descriptions etc.
One of the items they would like is a 'change' report.
On this report, they would like it too list 2 things. They would like it to list if any entries that refer to a expired edit type (determined by the end date field).
Then if the code was updated to a new edit type it would display this too.
So the report would look something like this.
If the code that refers to a expired edit type, and has no update it would say
code# removed <then list the edit type that expired>
or
If a new entry for that code was created with a new edit type, it would say something like
Code# Changed from <old edit type> to <new edit type>
Is this possible?
I'm not even sure how to visualize it, so hard time moving forward with it.
View 3 Replies
View Related
Sep 25, 2005
I need to be pointed in the right direction if you don't mind. I understand underlying database structures pretty well, but I am not sure how to create a form in Access so the user can enter new data. I am tring to create a form based off of the classes table first and then go from there. I was hoping to be able to allow the end user the ability to enter all information from one form.
I have attached the relationship structure that I have setup but I am not sure where I need to go next. Should I create queries and then insert the queries to my form?
Thanks for help
Steve
View 4 Replies
View Related
Dec 1, 2006
I have two tables in my database.
Table 1 contains telephone numbers and rates per minute
Table 2 also contains telephone numbers and rates per minute
I am trying to check the rates in one table against the other.
The problem I have is that the telephone numbers in each table are of differing lengths.
For example ......
Table 1 contains telephone number 01234 567890
Table 2 contains a rate for numbers starting 01234 5
I have split the numbers in Table 1 in to 0, 01, 0123, 01234, 012345, 0123456, 01234567, 012345678, 0123456789 and 01234567890 but I am stck on how I can now use this to match to data in Table 2.
In 'words' I would like the database to 'say' ...
0 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
01 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
012 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
0123 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
01234 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
012345 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
etc etc etc ....
The numbers in Table 2 are varying in length.
Can anyone provide guidance on database design and / or help with writing queries ?
Many thanks !
View 2 Replies
View Related
Apr 18, 2007
A simple personal database created by me is to be deployed on a home network having two PCs. I did splitting to FE and BE. The BE shows only tables. I would like to have the guidance of experts in this forum for the following:
1. Whether BE to be copied first in the host PC?
2. FE to be copied in the other PC?
3. After copying the Access DB how to link FE and BE between the two PCs?
4. Whether data entry, edit, search etc. possible from both the PCs?
5. If I test with dummy records, how do I delete dummy records, from BE or FE?
Shall be grateful for help.
View 2 Replies
View Related
Jun 20, 2006
Good Morning,
I am new to Access and I welcome expert guidance on the relationships of my database. The database will be used to track employee training and although I've enjoyed limited success so far I'm hoping that eventually I'll get it right.
The database has to do just a few things so it doesn't have to be very complicated. It basically has to store employee, training event, and completed training event information. The attachment shows the tables and their relationships.
The are basically two types of training that the database needs to store. The first is training that applies to everyone and the other is training that only applies to certain job specialties. These types of training are identified by the ProjectMandatoryCode and the JobCode respectively.
The ProjectMandatoryCode is a default value that is automatically entered everytime a new employee is entered into the database (EmployeeInfo table) and it is also a default value when a training event is entered into the (TrainingEventInfo table) that applies to everyone. The employee JobCode is a selection when a new employee is entered into the EmployeeInfo table.
When the user wants to enter a training event that is job specific, they are able to select a JobCode from a combo box/option group and this code is written to the record. Selecting one button from the option group writes a default value (ProjectMandatoryCode) to the TrainingEventInfo table while selecting the other button enables the combo box so that a specific JobCode can be entered against the event.
Everything works fine except... so far I've been unable to produce a report that lists every training event that pertains to the employee whether it be ProjectMandatoryCode or JobCode related. I can query and produce completed events or a list of what events are required but nothing that is complete. Ideally, I should be able to produce a complete training plan for the employee which shows all necessary events and where the employee has completed the task I should be able to show the DateCompleted.
This failure has led me to doubt my table relationships since all my efforts at various query joins have failed. Is there anyone who could enlighten me so that I could learn from this, complete this task, and apply this new knowledge to future databases? To the person with the answer, thank you in advance for your assistance. Respectfully,
Dale Gagnon
View 5 Replies
View Related
Dec 28, 2005
Hi everyone,
First off sorry for jumping in and asking questions on my first post. I have been searching the net templates and answers and while it has got me so far I still cant find specifics I'm stuck on and would appreciate any help.
I have an access design project to do for uni. To give you some background I've done some VBA programming in excel and have a good grasp of office and windows but haven't really used access before.
I have got Ms Access Inside out (bit over my head) and Ms Access Step by Step and have been working through their tutorials which have helped my understanding of access but dont arnt really specific enough to my project (or maybe i'm missing something)
The Brief is as follows:
"You have been instructed to design and develop and information system to capture students module choices for the business school. The system will run on MS Access and be supported by programs written in visual basic. Spec is as follows:
The system should be designed so that students can automatically select their options for semester 1 and 2 from a screen which will indicate to them whether they have broken any of the following constraints:
-> choose 30 credits in both first and second semester
-> business 2 cannot be selected unless Business 1 is
-> Programming 2 cannot be selected unless programming 1 is
-> Decision making and Data analysis share topics so students cant do both
-> Bus Finance and Corp Finance share topics so students cant do both
There is a table with 7 module options for each semester and their credit weight, either 10 or 20.
The outline is deliberately vague, 20% of marks will be for supplementary features that will enhance the systems functionality and usability.
------------------
Based on the examples I have worked through, I see it like a basic ordering system:
3 tables
Customers (Students)
Products (Modules)
Orders (Chosen Modules)
I need a database that will let me input customer information (that I can do)
Then allow me to Create an order for the customer Using a form that lists the products available from the products table
When Creating the order, with the form, rules need to be enforced to ensure the order is valid (ie certain products are not chosen together, the products value adds up to 30 for semester 1 and 2).
Once the order is complete a report is generated to show the products ordered (modules chosen)
Is this the right way to look at the database design?
I see the module choice form as having all the modules listed and tick boxes, however the only order forms I have managed to find have drop downs, any pointers here?
Am I right in thinking it would be VBA that will allow me to set and enforce the rules on module selection? If so any pointers on how?
Is there a way to get VBA to validate the inputs on the order form in real time, eg grey out one selection if another is selected, not allow more module to be ticked than 30 credits. I guess the code can be looped to do this? or is it easier to check the inputs after ok is pressed then give a warning box if its incorrect.
Again apologies for coming in on my first post and asking a torrent of probably basic questions, but I have tried my books and the net and would appreciate any guidance
Kind Regards, tom999
View 1 Replies
View Related
Oct 30, 2007
I'm kinda lost in here so I'd appreciate some help in pointing me in the right direction please. I've no doubt the answers are out there but I'm having trouble finding what I need, probably because I don't know how to frame my questions!
Basically the story is this - there are three guys covering various aspects of the business, they each visit three separate locations - all guys visit all locations. We have made a "big plan" that has 14 key deliverables in the locations as a "region" .
For each Key Deliverable there will be Actions, some Actions will be done in all locations, some might only be needed in one location. Each owner will describe the Action and link it to the Key Deliverable it supports.
What we want to do are things like
Take Location 1 and Guy A and show all the things he is doing to support Key Deliverable X.
or
Show all the Actions in Location 2 that are being done to deliver Key Deliverable Y by all Guys.
That sort of thing - progress reporting and identifying where we have nothing happening to support a Key Deliverable or too many actions in one Location etc
We have all our plans in Excel spreadsheets at the moment, each guy fills in a sheet and at present the idea is that we copy and paste it into the mother of all spreadsheets to present to the Gods - it's not happened yet because it's clumsy and well, it's my job to present it and I thought "there's gotta be a better way!" I can import the sheets into Access but then it all gets a little tricky and I run out of tallent.
OK so if you've read this far you're wondering if I have any idea what I'm talking about when it comes to Access! Well I am pretty much self taught and rusty but I can see that Access could help us get organised and
My limitations - I'm using standard Access 2007, I doubt corportate IT security would allow me to add in any useful plug ins or upgrades etc.
Am I using the right tools? I'm limited to MS Office really so I mean should I stick to Excel or carry on pursuing an Access solution?
Thanks for reading this far :-)
View 14 Replies
View Related
Feb 13, 2008
Greetings Access Geniuses,
I am currently in the midst of conversation over at utteraccess.com about a large database project I am working on.
I will post the most recent updates here, but if you want to download the database for your own review, you'll have to go over to the other forum. By the way, this is NOT an advertisement. I am a real person with a real, significant, and immediate need of as much assistance as possible for this project.
Before I go on, a few things about me. Though I am in the legal profession, my technological background is very strong, including a high level of proficiency with MS Office apps with the exception of Access. I know my way around the program (the result of its homogeneity with the rest of Office), but have not made a real database in the past. I also do not know any programming languages.
I have recently indulged in an Access crash course of sorts, including some book and online study on things like planning, design, and normalization, but I am still having difficulty wrapping my head around making it work.
That said, the link to the other discussion is: [W W W DOT]utteraccess.[ADD DOT COM]/forums/showflat.php?Cat=&Number=1590364&page=0&view=collapsed&sb=5&o=&fpart=all&vc=1
Feel free to gloss over it to see how things have progressed. Below is a paste of my most recent substantive post. Any help anyone can give me is of great value to me and I really, really appreciate it.
_____________________
Hi Everyone,
Attached is the most updated version of my database project for your review. I have also attached a sample of the output we would like to have for each product. This sample is not based on actual data, but it clearly shows what we are trying to achieve via a form of some sort. More on this in a minute.
The following changes have been made to the DB:
-Changed tblTrustProspectusVersion to include the appropriate data, based on our business model.
-Added descriptions to all non-PK fields.
-Created relationships to illustrate how things fit together. Note that these are NOT the actual relationships, but are for illustrative purposes to help everyone here (including myself) further understand how things fit together.
Our Business:
I am part of my company's legal department. Our team handles a number of different things primarily associated with Securities and Exchange Commission (SEC). filings. We have two major product lines. Everything that happens with one in this DB happens with the other as well.
Each product within a product line has certain features and other necessary information we need to see when doing our filings (see the attached sample output). These features and necessities include, but are not limited to, various statuses, various numbers associated with the SEC, various important dates, etc.
In addition, each product has a certain "fund lineup" associated with it. These funds are made up of two components: the name of the fund (aka "portfolio") and the name of the subadvisor to that fund.
Futher, each of these funds is associated with a certain Trust. The SEC requires us to send prospectuses to clients based on these Trusts, which, as I mentioned, are comprised of the said funds.
Basically, we need to be able to select a product from a drop-down list and have all of the aforementioned information populate instantly.
Before I close, one question with respect to my "tblProductFeatures". Like I mentioned, each product has a certain set of features associated with it. Each feature has a certain fee associated with it. These features come in four basic categories: Living Benefits + fees, Death Benefits + fees; Maintenance fees (just short list of the possible fees); and 12b-1 fees (another short list of fees).
Since the features can be so easily broken down, should I add them to their own tables?
Please consider this as you give your advice on how to acheive my desired goal.
I hope this makes sense.
And again, thank you all so very much for your help thus far.
Best,
Gilbert
View 12 Replies
View Related
Jun 7, 2005
Hey everyone,
I currently have an Excel Spreadsheet w/ 20 columns and 800+ rows. The columns are such things as Product, Product Code, Description, etc. It has clearly grown to big for an Excel file so I created an Access table from the spreadsheet called All.
Goal - have employees open a form and select a product from a dropdown box and then place check marks next to the info they would like to see for the product they chose.
I created a seperate table called Product, and a query for table All, and a report for the query. I created a form w/ a combo box that lists table Product and has a submit button. I added a criteria in the query under the column product that looks at this combobox. When you run the form, select a product, and click submit it opens a report that shows the results of the query for that product.
The part I cannot get is the check boxes that determine what columns are show in the report. I added a checkbox to my form and then went to my query and unchecked show and added a criteria to look at the checkbox in my form. All that did was erase the column from the query permanently.
I'm sure I'm going about this is the wrong way, but this is my first hack at Access and I'm trying to learn as I go.
thanks for any suggestions,
-Eric
View 5 Replies
View Related
Jan 29, 2006
a a aa a a a a a aa a a a
View 4 Replies
View Related
May 27, 2007
I'm a complete newbie to MS Access, and I'm working on my 2nd database.
Just a small and probably retarded issue: I have a table which includes a field, which contains four numbers. In a query I want my users to be able to enter a number, like 6, and the three numbers after that will have to be wildcards.
Example: The field has these entries:
8243
8184
6423
When the user enters "8", it should show the first two records. When the user enters "6", it should show the third record.
How can I accomplish this?
View 7 Replies
View Related
Dec 1, 2007
Hi Guys
i have created a table and am trying to run a qry that counts specific data, i have been searching but cannot find the answer. I have a standard qry that has the criteria of "staff name" and dates between ## And ##. Now when that information is returned, how do i get a count figure for the results. So for example if there were 15 entries for september it would say september = 15 (ish). I have been told to assign a recordset but no other information was forth coming. Can anybody help with code or qrys for creating a recordset.
Please remember the noob status when it comes to access - lol
Cheers Guys
Anthony
View 1 Replies
View Related
Feb 1, 2006
I have taken over the Access DB from Hell.....
Recently our system crashed and so I fixed compacted and repaired, and all seemed to be good to go. However, next day a bunch of random fields that our reps were once able to add information to are no longer able to add info to. They seemed to be locked out, however, I go into the various forms that were set up and enable is set to Yes, and Locked = No.
Any ideas?
This also effected a Check box field of 4 also.
Thnx for any help
View 1 Replies
View Related
Nov 19, 2006
Hope nobody minds but I don't really know Access and I have a table that needs sorting pretty desperately.I have a table with 5 columns that I need to filter out so just the remaing records are left over.http://img208.imageshack.us/img208/7328/untitled1wb8.th.jpg (http://img208.imageshack.us/my.php?image=untitled1wb8.jpg)The column 'fax' is the column with the records in it and the following columns need the record removing if the box is ticked.TimFPS, OurFPS, OnFPS, NeedsToBeFPS, however the column 'NumberWeHave' I'm not too sure about so I'll ignore it for now (I may need to take them out later)Sorry to be a complete noob and if anyone can help it will be muchos appreciated.
View 2 Replies
View Related
Apr 10, 2007
I have this program that adds information to a ms database file in a very odd manner.i cant change the way the information comes in but is there a way to move it into the correct location in a new table. information is put in as a group of 19 records.
whats happening is this:
these are the colums
primary key,group id,info,info,info,group order,info,info,info
i need to information from 4 of the colums into a new table so i have 19 colums and 1 record.
is this even possible.
Frustrated net admin
View 1 Replies
View Related
Nov 8, 2005
I am trying to make a query in which I want to get the data from the following:
i put in order ID number like this:
=(214)
and I successfully get the data from order id in data sheet view
but I want to get data from 214,220 and 200 at the same time how should i enter in the design view criteria field?
I tried =(214,220,200) but it did not work
thanks in advance
View 3 Replies
View Related
Apr 5, 2006
Hello im new to this and i been working since yesterday on a database. Basically I have 1 table that holds names and numbers asociated with and also the current state. that would be "ACTIVE" or "INACTIVE" and after a week or so (actually after i run a report) should change to "ACTIVE Z" and "INACTIVE X".
Now I have all the querrys and tables and forms and all. but what i want to do is to automate something im doing manually.
I run a querry that shows me only the records that have the state "ACTIVE" and i need to change them to "ACTIVE Z". Now what i am doing is, after running the querry i select all and go Control H and replace "ACTIVE" for "ACTIVE Z".
Is there any way to automate this.
cus i tryecd to do a macro that 1st open the querry and then executes the replace command but that just opens the replace pop up. I still have to tipe "ACTIVE" and "ACTIVE Z" in the fields to replace it.
Is there any way to automate this??
I would apreciate the help
THANK U
View 4 Replies
View Related
Dec 10, 2007
As the title suggests i am brand new to Access (about 2 days). I am trying to create a query that will group the fields according to their names and then add the income for all orders for each individual customer. After that i simply have to sort them. I want to sum the total income per customer and put them into descending order.
Here's a small sample of the list to get the idea:
Customer NameIncome per Order
Sanchez, Cindy$1,326.00
Lizzack, Mark$1,326.00
Patel, Mitesh$1,092.00
Sanchez, Cindy$1,000.00
Patel, Maria$780.00
Mui, Sylvia$780.00
Patel, Mitesh$500.00
And what I'd like the results to be:
Customer NameIncome per Order
Sanchez, Cindy$2,326.00
Patel, Mitesh$1,592.00
Lizzack, Mark$1,326.00
Patel, Maria$780.00
Mui, Sylvia$780.00
Thanks immensely for any help you can offer. I realize there is probably a very simple solution to my question, but after 5 hours of searching the help button, and various online forums, I'm at my wits end.
View 3 Replies
View Related
Jan 5, 2006
Hi. I just started using databases and I am at a loss with some of the definitions. I could do with some help as these are part of my school work! Thanks.:D
What is the role of a 'form'
1.to hold data
2.to provide a 'form' format for reports
3.to provide a tool which sorts the data and prepares it for export
4.to provide a user-friendly data input 'front-end', which can additionally validate user input..
Thanks for any help....much appreciated:cool:
Jane
View 9 Replies
View Related
Oct 30, 2007
Hey all,
I'm an access noob, but decently experienced with Filemaker. I learned quickly that they are two very different animals with such a differing vocabulary, I may be in over my head with even the simplest concepts.
My problem: I am creating a quicky book order database for some professors. I have a value list (drop down menu) that references another table's column of professor names, but would like to create another value list dependent on the professor's name that would list the classes he/she teaches.
Table 1
Columns: Professor, Class 1, Class 2, Class 3, etc
Form 1
a) Professor field drop-down menu/value list populated by values in the table 1's professor column
b) (need) A drop-down list that displays which classes the selected professor teaches.
View 6 Replies
View Related
Aug 29, 2007
Noob alert.
What I am trying to do is write either a 'yes' or 'no' to one of my access tables based on the criteria of a linked table. When I build my query, I input the following into the field:
Expr1: IIf(([5WellInformation]![LowPH]<5.5) Or ([5WellInformation]![HighPH]>10) Or ([5WellInformation]![Temperature]>75) Or ... , "Yes", "No")
My query type is 'Append Query'
The problem I am encountering is that I require 39 different criteria to come up with either a Yes or No. If anyone of those 39 criteria fail, than a Yes is written.
When I place the entire expression into the query, half of it is cut off because the expression is too long.
Any Ideas? Am I doing this right... or is there a different way I should approach this.
Thanks in advance to anyone who can help!
View 10 Replies
View Related
Jul 26, 2005
Hi,
I apologize first of all if this question is bounced up and down through out the forum all the time, and if there's already a topic on this, then it'd be perfect for me. But im not having any luck.
I do not know how to upload my database in a way that it will work when i try to use a page through microsoft frontpage to display results from it.
What are the accurate steps into creating a database connection? Thanks a bunch
View 3 Replies
View Related
May 30, 2006
How can i make all my forums fill my screen? so dont have to recreate them? so they are not just tiny boxes. as originaly designed? I looked in hope that there maybe have been something in the startup drop drop down, but i was't so luck. also tried to select whole forum and group and name the whole thing bigger by dragging the resize command. but this just messed the whole forums up so i did't save changes.
Thanks im sure this is a really stupid question.
Shrew
View 7 Replies
View Related
Aug 12, 2005
Hello everyone,
I'm quite new to access (used to php mostly) and for usual task it's ok but now I have trouble for a quite simple query I think.
I have to compare the values from 1 field and select only the rows just before the value becomes smaller and also the very last row.
example:
if I have a row with 1, 2, 3, 4, 1, 2, 3, 4, 5, 1
I need to put 4 and 5 in an other table
View 1 Replies
View Related
