Beginner Needs Help With Searching A Range Of Values
Aug 9, 2005
Hi,
Im a web designer by trade and know pc's fairly well, im trying to help a friend who is pc illiterate build a database.
It would help him out and be some practice for me (always wanted to have a bash at access but never had a reason to do so)
Im totally new to Access and im trying to do the following.
He is an estate agent and wants a database where he can enter his prospective clients looking for commercial properties to rent, one of their criteria will be the size range of the property they want. Then when he gets a new property on his books he wants to be able to search the client list by the size of the property and display the results of any clients that the properties size falls within their required size range (phew hope that makes sense)
In an ideal world hed like the results to include addresses so he can mail merge the results and send out letters to any clients who might be interested.
So, ive managed to create a table of all his client details but now im stuck on how the query should work, ive read the help files in access (which seem to be written in martian) and im just not sure how i should try and do this. I started to create a parameter query but ive gotten very confused with all the options.
I can see that i could put fields in my table of Min Size and Max size but im not sure how to design a query that searches a single value between these 2 fields
Ive had a look through this forum but cant see anything that covers this, im hoping someone might be able to point me in the right direction, either with some advice or a link to some online help that might help me understand what i need to do.
Thanks for taking the time to read this post, apologies if its in the wrong place or a totally noob question.
Any help would be very much appreciated
Many thanks
Dave
View Replies
ADVERTISEMENT
Jun 9, 2014
I built a custom search form that uses unbound text boxes to set the criteria for a query. For example, I have an unbound text box, LastName on my search form. Then in the query, I set the criteria to Like "*"&[Forms]![SearchForm]![LastName]&"*". This returns all records if the text box is left blank or returns those records that match the text box if it isn't.
I would like to do the same thing but instead of searching a text field, I want to search a number field. And I want to search for a range of values aka >10 instead of just a single value. I still need the query to return all records if the text box is left blank.
View 8 Replies
View Related
Jan 22, 2007
Hi. Please do not lecture me on database normalization, as this truly is not in my control.
I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)
If I design a query showing all 2 dozen tables and their fields...
Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)
If I am only as clear as mud, please let me know, and I will try again.
Or, by asking if I can search for field values querying by field name, am I any clearer?
Russ
View 14 Replies
View Related
Mar 6, 2008
Hi,
I've graduated from counting records to summing values now!!
I've got the data set that is shown below
caseNo source Amount Vat Total Date Paid
1 ted £50 £7.50 £57.50 01/01/08
I've actually around 3000 records going back to 2004. What I need to do is make a report that will show the following.
Source Amount (financialYear) Vat(FY) Total(FY) Amount(thismonth) Vat..
ted £50,000 £10,000 £60,000 £5,000
Bob .....
I've created a function in the module that returns the financial year that the record is in called getFN() which works fine.
I use 2 text boxes to input the start and end date of the reports and use one of the text boxes as the input for getFN()
Basically, I can get a report that shows the values for each source in the financial year ok, and I can do the same for the values for the month selected but I can't join the two queries up in a single report.
I'm not sure if I need to have a blanket query that joins them. I'm a little stuck.
Any help would be really appreciated.
Steve
View 1 Replies
View Related
Oct 2, 2014
In a table, i have a field called operating draft (ships). I would like to be able to put an interval of data in this field, for example between 3 meters and 6meters. Indeed, for few ships the draft is flexible.
What kind of data type should i select and how can I set up an interval data.
View 1 Replies
View Related
Sep 3, 2013
I have been stuck trying to write an expression that will calculate the Yes values in a range of 16 Yes/No fields.
I have attached a screen print of the report I am working on.
View 5 Replies
View Related
Mar 11, 2013
I'm working on a loan database for my personal use and obviously I'm new to access
See attachment ...
View 5 Replies
View Related
Jan 28, 2008
Hi everybody!
First of all, I didn't know how to search for the precise info regarding my question, so, If it is repeated, feel free to close this thread or delete it.
So, here it is my question:
I have a table with values taken once monthly, that is, for each person on that table, I would have a maximun 12 records per year. In that table I have the Id of the person, the date field where I store a date when I get the record, a field where I store a value and a control number field. The values on the control number field go from 1 to 20 (or less).
What I want to achieve is, given a range of dates, show the last two controls of every person, something like:
id, last control date, last control number, value of last control, previous control date, previous control number, value of previous control.
As you could see, I want to show in the same row the last two controls for each person, only if the happend to have two controls, if they have only one, it wouldn't be necessary to show them, so, how can I do this?.
Thanks in advance! :).
View 2 Replies
View Related
Sep 7, 2005
I have created a new Access database and need a few additional things that I would like to add. I created a database for inventory within my company. Only a few people will have access to it so security will not be an issue. However, I have a couple questions. Is their a way to have a popup when you open the database to insert let's say the serial number of the computer (already in my forms) and then it will automatically bring up that information. Also, I do not want any of my forms edited. I want this done strictly through the tables. So this leads to my next question. Is their a way once you have a form up that you can enter a different serial number and the information will automatically populate. If not, can you add a search query or option or what needs to be done. Thanks in advance for your help.
View 4 Replies
View Related
May 4, 2006
Hi
I'm not sure if I have posted this in the right section, but i'm sure someone will tell me.
I have 2 questions, first is about concatenation.
I've looked endlessly & have tried a few scripts, but all to no avail.
eg db:
Name: Dpt:
Tom sales
John sales
Jerry mgm
Albert sales
Keith mgm
eg report that I want
Sales - Tom, John, Albert
MGM - Jerry, Keith
What is the best way to do that?
2nd Question.
In the same db, what i'm wanting to do is if I change the data in any record, it will track the changes.
eg
Albert has changed departments from sales to management (mgm as above), and so when you view albert's information, you can do something to pull up any & all changes made to that particular person. Any ideas there! Once again, i've searched the net and can't seem to find the best result.
Cheers in advance!
View 2 Replies
View Related
Jun 14, 2006
Hi,
I am just a new greenhore learning ms access and I am planning for a warehouse/inventory storage database.
My question :
1) I have 4 product division (Do you think it is good to have 4 tables):confused:
2) The calculation field. (How does the mechanism work when I take the the item out from the inventory, it will show as total storage -1 (eg, the storage have 5 units and I took 1. it show balance left 4).:confused:
About question 2, is it that I have a to do separate table again.
Temporary, I have these 2 problems and hope all access expert can help.
Regards,
Richard
View 1 Replies
View Related
Mar 14, 2008
I am fairly new to access and am trying to put together a project for work. I need to be able to create a list of all of the employees and which team they are in. So no problems with this.
I also need to be able to mark in their records where they can advertise and the quota they have. Each employee may be able to advertise in more than one location.
What I would like to create is a form for each team that displays the employees and where they can advertise. So far I have managed to get a subform to show this for each office but each employee has record for each job board. See Picture1.http://i112.photobucket.com/albums/n170/nharri/1-1.gif
Ideally I would like something like picture 2.
http://i112.photobucket.com/albums/n170/nharri/2-1.gif
Is this possible?
View 1 Replies
View Related
Feb 22, 2006
Hello!First of all I am new to these forums and I'm also a beginner with Access so have patience :) and I don't know if this thread is in the correct part of the forums so if a moderator feels like it's not feel free to move it.Me and a friend are interns at a company in US and the company assigned us to gather data from several excel sheets and build a completely new database. This database contains several tables.This isnt the correct names of the tables, it just to give you an idea how it looks at this moment.Customer information. (key: Customer ID)Resellers (Key: Reseller ID)Newsletter subscriptionsProducts (Key: Serial Number)Software (Key: Licence) Product2 (completely different product from the others) (key: Serial number)Invoices (Billing information, debit, credit, etc) (key: Invoice ID)OK, that explains how our tables looks like. And ofc in the tables we have different fields.Our problem at this moment is our form. Because we want to use a form to enter the data. We believe that it will be the fastest way and the easiest.I have a picture for you to show you our problem.It seems like the FORM is looking in the same table. But we want all the "fields" in the form to represent its own table. We tried to gather all tables into 1 Query and use the query when we created the form, the problem dissapears BUT a new one comes along. If we use a query in the form we can fill out all the fields but we can't create a new "record" with empty fields to enter more data hence leaving us with only one record of information. This is realy starting get on our nerves. Can someone help us?
View 1 Replies
View Related
Apr 3, 2006
Hi there, Heres my situation
I have a basic form for a customer shopwing all general details the top field is Customer ID in which I would like to enter a customer ID and for the rest of the data to appear for that customer in the remaining fields, I have a small amount of knowledge with access and believe I need to a Dlookup but I am unsure where and how to put this expression i'm thinking maybe in a query. Any help would be great, My field names are listed below to make any help a little clearer
Customer ID field = txtCustomerID
Linking to = tblCustomers
to find = txtSurname
txtForename
txtPostCode etc
Many Thanks
View 1 Replies
View Related
Jun 30, 2006
Hi this should be very simple, but it's not for me.
I am creating a database logging machine error's.
Each error has a recorded start and end time, which I enter.
I want the database to enter the elapsed time into a third field, 'ElapsedTime'.
I see how to do this in the builder, just subtracting the two fields. But I know not to make it a condition of my query, that doesnt make sense. Where do I put my expression so that my table updates itself with the new field?
Thanks!
View 3 Replies
View Related
Nov 6, 2004
I am creating a database concerning products sold.
I am a beginner of using Access Database and would like to know if this is possible...
At the designing stage of tables and relationships is it possible to link and construct, so that the stock quantity of each product is kept up to date. so, when an order is made of produc '123', product '123' updates it stock quantity be deducting the quantity ordered.
attached are the tables/fields/relationships.
Thank you very much.
View 1 Replies
View Related
Jul 27, 2007
I need to figure out how to program an expression that will reset a value on a form to zero if the product of it's calculation is less than zero.
I am figuring out sales commissions for listing Ebay items and there are some instances where the item doesn't sell, so it's a net loss because of the fees Ebay charges to list the item. I have programmed the form to take 10% of whatever the net gain is and use that as the salespersons commission. But in instances where the item doesn't sell, it actually takes money away since 10% of a negative number is a negative value.
In cases like this, I need the expression to just show a zero, if the number is negative.
If anyone could help, I would be greatly appreciative.
View 1 Replies
View Related
Nov 30, 2005
For a couple of years now I have been keeping a spreadsheet record of UK football match results and statistics - I use this as the basis for gambling on future matches. I now want to migrate this information into a database so that
1) It is much easier to enter the new data(via picklists)
2) I can generate reports on particular teams to help me on future betting decisions
Being almost completely new to Access I am struggling with the design. The information I'm capturing is:
Match Date
Competition(table including five)
Home team/away team(table including many depending on competition)
Home formation/away formation(table with standard list)
Home goals/away goals(numerical)
Home corners/away corners(numerical)
Home bookings/away bookings(Numerical)
Referee(from a standard list)
All the above is in a match results records table with relationships to each data element table.
For the form for data entry, I want pick lists for all non-numerical values - eg team, formation etc. I would like to get a little cleverer and for the team pick list to only contain teams that are in the competition I've selected(eg Uk or European).
I've created some tables and relationships, but when I create a simple form with combo-list boxes, the list itself comes up blank even though I've linked the field to a particular table of options(eg a competitions table).
Can anyone help on suggestions for the overall design(should I have separate match results tables and team tables for each competition?) and with this particular combo-box problem please?
View 3 Replies
View Related
Jan 5, 2006
Hi all. Had a quick peruse and this place seems really helpful, lets hope you can help me out here.
I have a project to do which roughly described is engineers doing jobs on aircraft. Three main tables:
Engineer
Booking
Job
The job table denotes the work required on an aircraft, with a unique barcode for the "job card". The Engineer table contains information about our engineers, with a unique ID.
Now here's my problem. The booking table needs to store information from both of these tables, but store it seperately. So I need to take some information from Engineer (ID, name, trade etc) and some from the Job table (barcode, description etc) and store it again in the "booking" table along with a few other "new" fields unique to the booking table. When this "booking" is made, it must record the start time, furthermore when the record is returned to later and closed, the duration of it being "open" must be worked out (total job time).
Ideally I'd like to create a form whereby I drop down a combo box and select an engineer (or type and autocomplete) it fills in the rest of his details, I then drop down another box and select a barcode (or again autocomplete) and that completes the rest of the job details. I then type in the new fields I also need, and this all gets stored in the "booking" table.
Any help greatly appreciated :)
I'm working with A2K3 for clarification, and can post/email my current database if required.
Thanks :)
View 7 Replies
View Related
Jan 11, 2007
I've searched and could not find what I was looking for.
I have NO database experience but need to learn quick
I have an html page with an iFrame (flash menu on left - iFrame on right)
When I click a product image on the left this is what I need to happen.
- Flash send a variable
- The iFrame to load an HTML page that will populate with an image, 2 text fields, and 3 hyperlinks dependant on what product is clicked in the flash.
I am using access since it is what i have at work, and i can just add all the products in a table pretty quickly and easitly. I set up a table in access with the image, hyperlinks, and text that needs to populate in the html page. The HTML page is set up in tables if that makes a difference...
I just don't know where to go from here to make my connections and how to set it up.
Any help would be appreciated.
Thanks in advance.
View 2 Replies
View Related
Feb 8, 2007
I downloaded a database of historical baseball stats recently and having been playing around with Access for a bit although frankly, I haven't been able to learn or do much. Anyways, here's my question:
I have one table that is a master list of all the players. The two main things I'm worried about are the playerID and the birthyear (year of birth). In another table I have batting stats from the players from each year they played. However, it is missing the year of birth which I need to calculate the age in that year. So, I need to get the birthyear from the master table and put it into the batting table next to the corresponding ID. Most players played for multiple years so then for everytime a playerID occurs in the batter table, I have to insert the corresponding birthyear from the master column. Hope that makes sense.
As I said, I'm completely new to this kinda stuff so any help or tips would be greatly appreciated.
View 5 Replies
View Related
Oct 18, 2007
Hello all. Would anyone be so kind as to help me out with this beginners problem. I have a lot a books which i'd like to sort into categories. I'd like to be able to select a category from a combobox on a form, and for relevant books in that category to appear in a listbox. Upon clicking on a particular book on the listbox, relevent info should appear on the form eg. date purchased,author etc.
Is it also possible to have an option to view all books from a particular genre in spreadsheet format embedded in the form, and an option to view every book regardless of genre. I'd then like to be able to print reports on each/all. I'm sure i'd be able to manage if someone could kindly get me on the right path with an example.
Thanks,
Richard.
View 3 Replies
View Related
Nov 29, 2005
Hi all,
I am a novice database user, having only ever used simple databases.
I need to produce a database which stores the path to images.
I want to design a database with a table of Items with fields such as :-
ItemID
ItemName
ItemDesc
ItemCategory
ItemPhoto
each item may have many photos of the item, but I will not know the exact amount, although probably less than 10 photos per item.
Photo will have details (fields) such as :-
PhotoID
PhotoName
PhotoPathURL
PhotoDesc
I realise that each item can have many photos, but a photo can only have one item.
Would I need a table to link these two tables?
Your advice greatly appreciated.
View 6 Replies
View Related
Nov 28, 2006
Could someone please help me with the following.
I have a form that gets some basic information...
My table looks like the following..
NewApplicationID
ClientName
ClientAddress
ClientPhone
ClientEmail
later on down the road, we will be inputing other fields, is it possible to have another form for specific information, that would be linked to this....
thanks for any help
Josh
View 1 Replies
View Related
Oct 18, 2005
I apologize in advance if this question is completely obtuse, but I'll pose it to the group nonetheless.
My task is to analyze accruals versus actuals for the past quarter (i.e., July-September). What is of particular interest are the variances between the accrued and actual amounts. I have several fields of data, but the common field is Shipping Unit (SU). My initial plan is to upload the tables with the accrued and actual records separately and query SU's to pull in all instances in which there are matching accrued and actual SU's. This is where it gets a bit fuzzy for me. I would export the resulting queries into Excel, unless there is a means to produce the analysis directly from Access.
I haven't used Access in ages, so I don't recall how I might best proceed. Please advise with your thoughts. :confused:
View 1 Replies
View Related
Jun 14, 2006
Hi everyone, i 'm a begginer at access so forgive me. i have a table that looks like this:
polygonID tree species other.atributtes
1 a ....
1 b .....
1 c ....
2 b ....
3 a ....
3 a ....
3 a ....
4 b ....
5 a ....
5 c ....
what i want to do is to create a query or querries that will show me the polygons that have only one tree species. for example in the above table it would be polygon 2,3,4. thanx in advance
View 1 Replies
View Related
