HELP with queries!!!

Hi all,

I am a new user to Access and need some help with setting up a query. I am 
building the framework to an inventory database. Currently I have one table 
that has the following information:

serial #
Status (checked in or checked out)
equipment type (pc, network printer, standalone printer, scanner, and monitor)
EE# (equipment number)
model
manufacturer
work order#
department (where the equipment is going)
equipment location (the room number)
relocation date
comments

I understand how to run the query, but I am trying to include drop down 
menu's in the query instead of having to type it in. For example, the query I 
am trying to run I'm checking to see how many monitors are checked in. 
Instead of typing in "checked in" and "monitors" my boss would like to have a 
drop down menu to choose from. 

#2 When I try to perform a wild card search, what do I need to do so that it 
will show everything that is in the entire inventory, even if I do not enter 
any information into the parameter boxes? 

#3- I am trying to set an automatic date so that when one of our PC guys 
fills in a form, today's date will automatically pop up in that box without 
them having to type anything, BUT if they would like to change the date they 
can. 

Any help or advice that you can give will be helpful. Thanks!!




0
Utf
11/17/2009 4:02:03 PM
access.gettingstarted 618 articles. 1 followers. Follow

2 Replies
510 Views

Similar Articles

[PageSpeed] 9

confused with access wrote:
> Hi all,
> 
> I am a new user to Access and need some help with setting up a query. I am 
> building the framework to an inventory database. Currently I have one table 
> that has the following information:
> 
> serial #
> Status (checked in or checked out)
> equipment type (pc, network printer, standalone printer, scanner, and monitor)
> EE# (equipment number)
> model
> manufacturer
> work order#
> department (where the equipment is going)
> equipment location (the room number)
> relocation date
> comments
> 
> I understand how to run the query, but I am trying to include drop down 
> menu's in the query instead of having to type it in. For example, the query I 
> am trying to run I'm checking to see how many monitors are checked in. 
> Instead of typing in "checked in" and "monitors" my boss would like to have a 
> drop down menu to choose from. 
> 
> #2 When I try to perform a wild card search, what do I need to do so that it 
> will show everything that is in the entire inventory, even if I do not enter 
> any information into the parameter boxes? 
> 
> #3- I am trying to set an automatic date so that when one of our PC guys 
> fills in a form, today's date will automatically pop up in that box without 
> them having to type anything, BUT if they would like to change the date they 
> can. 
> 
> Any help or advice that you can give will be helpful. Thanks!!
> 

Nice clear question.  I'm going to respond in reverse order (simplest 
first!).

Q3: All you need to do is add a default value to the date/time field in 
your table, and set that to =date().  The result of the "date" function 
will be entered into that field whenever a new record is created, but it 
can subsequently be edited.  The now() function returns both the date 
and the time: dates/times are represented as integer/fractional parts of 
a single number.

Q2: You're using a "parameter" query, which triggers Access to prompt 
you for the missing value.

All you need to do is put an asterisk (*) in the field and it'll match 
everything.  Your Criterion needs to use the Like operator, rather than 
'='.  Then you can also search for things like "*abc*xy?"  where the '?' 
matches a single character rather than any number.

Q1: You can't put drop-downs in a query - you need a form.  It's true 
that you can have "Lookup" fields in a table, but many people regard 
this as bad practice, and I avoid them as it obscures the table design.

There's an art to searching based on a form.  You might like to look at 
this page for an extended account:  http://allenbrowne.com/ser-62.html

You can also develop a basic search form using the "Query by Form" 
technique:  http://support.microsoft.com/kb/304428
It can be tricky getting the hang of referring to a "box" where a user 
will enter a match-string.  This (scary) page will help:
http://www.mvps.org/access/forms/frm0031.htm
... but if you can get the hang of the Expression Builder that will make 
this much easier.  See:
http://office.microsoft.com/en-us/access/HP051866381033.aspx
http://office.microsoft.com/en-us/access/HA102549021033.aspx (demo)

However, it's worth pointing out that Access comes with "searching" 
facilities out-of-the box - "Filter by Form" - which might be all you 
need.  See: http://support.microsoft.com/kb/304259
Filtering is very similar to using a query with a criterion, but Access 
hides the query from you.  You'll find that described in Help.

Hope that helps.  We're all learning!

Phil, London


0
Philip
11/17/2009 4:48:58 PM
Your users deserve a form with controls for all interaction. You can set 
defaults, use combo or list boxes, check for integrity, etc. Your query might 
have a criteria under [EquipmentType] like:

Forms!frmSearch!cboEquipType or Forms!frmSearch!cboEquipType is Null

-- 
Duane Hookom
Microsoft Access MVP


"confused with access" wrote:

> Hi all,
> 
> I am a new user to Access and need some help with setting up a query. I am 
> building the framework to an inventory database. Currently I have one table 
> that has the following information:
> 
> serial #
> Status (checked in or checked out)
> equipment type (pc, network printer, standalone printer, scanner, and monitor)
> EE# (equipment number)
> model
> manufacturer
> work order#
> department (where the equipment is going)
> equipment location (the room number)
> relocation date
> comments
> 
> I understand how to run the query, but I am trying to include drop down 
> menu's in the query instead of having to type it in. For example, the query I 
> am trying to run I'm checking to see how many monitors are checked in. 
> Instead of typing in "checked in" and "monitors" my boss would like to have a 
> drop down menu to choose from. 
> 
> #2 When I try to perform a wild card search, what do I need to do so that it 
> will show everything that is in the entire inventory, even if I do not enter 
> any information into the parameter boxes? 
> 
> #3- I am trying to set an automatic date so that when one of our PC guys 
> fills in a form, today's date will automatically pop up in that box without 
> them having to type anything, BUT if they would like to change the date they 
> can. 
> 
> Any help or advice that you can give will be helpful. Thanks!!
> 
> 
> 
> 
0
Utf
11/17/2009 4:56:09 PM
Reply:

Similar Artilces:

update query
I am trying to update a field but get an error message that some of the records were not updated due to key violations. However, some records are updated. The field that I want to update is a text field that is not the primary key. Any ideas? Is the field you are trying to update - a foreign key in a relationship to another table? Does the field you are trying to update have any validation rule? Are you attempting to update only one field or several? Have validation rules for any of the fields changed recently and you are attempting to update records where the current value in a fiel...

Calculating Percentage in Query.
I have a query with the fields Quantity (Count of ID's), Product, company. My issue is to calculate the percentage for each company in a query When I put Quantity/Sum(Quantity)) I am getting subquery warning. How can I accomlish this in a query. That is , I would like to get Quantity, Percenage: Qty/Sum(Qty), Company. Any help really appreciated. Thank you Create a totals query to get your SUMs for the equation. Join the query with your table in that output query to get percent. -- KARL DEWEY Build a little - Test a little "Lin" wrote: > I have a query with the ...

Vlookup Help
I have a source sheet with 3 columns and 28 rows Column 1 contains numbers (formated as text (i.e. '1400) Columns 2 Long Description (Toronto) Column 3 Short Description (TOR) In another sheet I have a cell which lists the name of the tab re: formula =REPLACE(CELL("Filename",A1),1,FIND("]",CELL("filename",A1)),"") If the tab is named 1400 my vlookup works, if it is named TOR it does not. I have formated the vlookup table in text but I can still not get this to work.. I have to beleive it is a formatting issues but since I have form...

Drop Down Menu Help
I have a drop down menu, and I was wondering if there was a way put value in there so it would let me type a value not on the list. Fo example, if the dropdown menu gives the user the choice of selecting A b, c, d, or E. Maybe the user wants to type in F, G, or H or just an unforseen value that is not available in the dropdown. Can this b done? Thanks in Advance Miles Ree -- mcr ----------------------------------------------------------------------- mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1549 View this thread: http://www.excelforum.com/showthre...

outlook 2000 query
Any idea how to display the envelope icon on the system tray for outlook 2000. My icon disappeared and can't seem to get it back. ...

Query for non unique record
How can I write a query that returns to me those records whose SequenceNumber and Symbol are not unique ? (have more than 1 records whose combination of SequenceNumber and Symbol are the same) Thank you CREATE TABLE [dbo].[TickData1Day]( [SequenceNumber] [char](13) NOT NULL, [CommodityCode] [char](10) NOT NULL, [MonthYear] [char](2) NULL, [Symbol] [varchar](50) NULL, [OpenPrice] [decimal](16, 4) NULL, [HighPrice] [decimal](16, 4) NULL, [LowPrice] [decimal](16, 4) NULL, [ClosePrice] [decimal](16, 4) NULL, [Volume] [numeric](18, 0) NULL, [Date] [datetime] NULL ) ON ...

trouble querying date in access xp after 97 conversion
when querying in access xp after converting from access 97, the month part of date is confused. When querying for Dec data it results in Feb and Dec data. When querying for Nov data it results in Jan and Nov data. what is your criteria statement to pull the records? Is the field where the date is located a text field or is it a date/time field? "PamP" <anonymous@discussions.microsoft.com> wrote in message news:069901c3d956$711ebac0$a101280a@phx.gbl... > when querying in access xp after converting from access 97, > the month part of date is confused. When querying fo...

Access DB has become troublesome
Have done gazillions of databases in Access 2000 & mostly in Access 2003 (2000 file format) and Access has always been very reliable. Any problems could always be fixed with compact and repair. Now I have an DB which is continuously misbehaving, and on different computers. (just one .mdb file that I move around) Most problems are with a tabbed "Menu" type form (with lots of buttons to open forms and queries) although I have deleted and rebuilt the form and the problems persist. Compact and repair has not fixed. I think that the only things I did dif...

Help with more than three conditions when formating.
Is it possible to have more than three conditions using conditional formatting? I have a list of five products in a column. I want to use a different color cell for each product. Product 1 would be yellow, product 2 would be blue, product 3 would be green. I know how to use conditional formatting to format each but I only seem to be able to set up three conditions. When you can't use Conditional Formatting (more than 3 formats), the next step is to use an Event Macro. See http://www.mvps.org/dmcritchie/excel/event.htm#case look at top for instructions to install, right click on...

Scheduling using Query-based distribution group
Whenever we use a particular Query-Based Distribution Group to set up a meeting to be attended by all the members of the group the person setting up the meeting gets an "undeliverable" message saying that a particular email doesn't exist any more. The email is for a former employee whose account was long ago removed from our systems. If we just send an email to this Query-based Distribution Group there is not undeliverable - it only does this when using the group for scheduling. Seems like this user's email is somehow exists like a ghost in the group, but only when using it ...

[Urgent] Need Help on COM AddIn registry entries
Hi, I am having 2 COM AddIns for the TenderEnd hook... Both of them works well indivudually. But when I have both of them together, the AddIn that was added first is getting invoked and the second AddIns is not invoked. When I exit the POS and the registry entry of the second AddIn vanishes... Both the DLLs are registered... The registry entry is as follows... Hook Number: 000 Caption: AddInCaption1 Description: AddInDesc1 HookType: 15 ObjectName: AddIn1.class1 Parameter: 1 Hook Number: 001 Caption: AddInCaption2 Description: AddInDesc2 HookType: 15 ObjectName: AddIn2.class2 Parameter: 1 W...

Need help with a formula #2
I need to subtract 2 different sets of dates to get a total amount of days. Eg: (a-b)+(c-d) = e I then need to take the total (e) and multiply by 3 different integers if the total (e) is greater than >6 days,>10 days and >31days. This helps me keep track how long my company trucks are out of a Rail/Ship Yard. I can't seem to get the dates to subtract and get an integer(regular number). and I'm dead lost on the greater than stuff. Of course the boss drops this on me on my first day. Any help or comments will come highly appreciated. Thank you One way =A1*VLOOKUP(A1,{0,0;...

Help Please
I would be grateful if somebody could advise if there is a simpler way of deleting duplicated cells in a spreadsheet Spreadsheet Add1 Unique Add2 Add3 Add4 Populated via If Statement (Thanks to Frank) PCode Vb Code tagged to the end of a macro Range("B2").Select If B2 = E2 Then E2 = ClearContents Selection.ClearContents End If Range("C2").Select If C2 = E2 Then C2 = ClearContents Selection.ClearContents End If Oddly this works and the duplicated cell is deleted but my problem is that I would need to enter this code per cell and colu...

Pass through query speed
I have written a pretty convoluted pass through query to a SQL Server database. A parameter is collected using a simple form that uses code to modify the query definition before running it. The first time I run the query with a revised parameter it takes about a minute to return the required data. If I then run it again without changing the parameter it takes around 3 seconds. Obviously I prefer the 3 second response time. Can anyone explain why it takes so long the first time? Is the data being cached by SQL Server or Access? Is there some kind of compilation process going on invisib...

Coding Help: Copy Checked Rows to Another Sheet
Dear Excel 2003 Users, Has anyone ever done the following: On a worksheet, have a series of rows, where there is a place in Column A to place a checkmark. The user would go through and check the rows that are needed to create a "client to-do checklist". Somewhere on the sheet, there would be a button that would copy only the lines that are checked and either put them on a new worksheet or into a new workbook. If it is easier to delete the unchecked rows, that would also be great, I would base the initial document on a template to keep it intact. It would be greatly appreciated ...

Re: Help with copy pasteselection
Please Help. I am attempting to write a macro, which will copy and paste special from one worksheet to another. Date is a group of merged cells and is meant to be constant. I want the macro to search for the next available empty row thru range a11 thru a55 and then copy paste to this active cell, but my formula will not work and I am not sure where I am with it. Thanks in advance, Cy Sub Select_and_Activate() Worksheets("3294-C,1 of 4").Range("Date").Copy Worksheets("PS3368-P").Select ActiveCell.Offset(1, 0).Range("A11:A55").Select Worksheets...

Help in Creating Relationships in a table
Hi This is a follow to a previous question concerning relationships in a table. These are my tables I have in my database. Students StudentsID LastName FirstName HomeroomTeacher (ohter info relating to the student) Cases CaseNo_ID <PK> StudentID <FK> Categories CategoryID <PK> Category (e.g. School, Community, Individual, Peers, Family) Issues IssuesID <PK> Issues (text, e.g. lack of role models) CategoryID <FK> StudentIssues CaseNoID <link to Cases Table> IssuesID <Link to issues Table> ...

Help with simple formula
This is a multi-part message in MIME format. ------=_NextPart_000_001C_01C7832C.9F7EDE80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the following formula to generate a full product number =3DCONCATENATE(B13,"-",C13,"-",D13,"-",E13) My dilemma is that I need to modify it so that the final - and E13 will = only be associated if E13 contains a value. At least I think that sounds = simple, just not sure about the exact string I need. Mike K ------=_NextPart_000_001C_01C7832C.9F7EDE80 Content-Type: ...

accumulating tasks PLEASE HELP
My users have tasks accumulating and I cant stop them. Some I cant even remove without renaming them. These tasks have nothing in them, They start at #1 and go. I found a user to have over 100 of them. Please I need help on this one. Windows 2000 Pro/ and Xp Pro. Outlook 2002 Exchange Server 2000 BlackBerry (BES) for the hand held NEXTEL/Blackberry ...

Labans' PrintLinesClass help
Hi, I've used Labans' PrintLinesClass database to create lines about the detail section of my report. I have my fields set to can grow, so using this method draws the lines all the way to the bottom of the detail section. It works great when I preview or print the report. However I am also using Labans ReporttoPDF to export the report and when I do this the PrintLinesclass puts a thick black line down the right hand side of the report. I cant work out of to get rid of this line. can anyone help??? cheers ...

Need help with a query
Let me start by saying that I have very little experience in Access. I've learned what I know by "tinkering" around in it and a lot of the terminology I've seen on these boards is above my head. Here's the background to my question. I created an employee database that consists of one massive form with several tabs and on each tab there are several subforms. The main form was created from the Employee Header table and the subforms were created from queries from tables. The idea is when you open the form, you see an employee's basic info on the top ...

help. outlook 2007. not listing contacts
Hi all, i have a small problem in that some of my contacts are not listed in my contacts, example: in address bar type 'b' get a list of all my 'b' contacts, barbara, ben, bill, etc but none of them appear in my address book, or contacts. only listed if i add them to address bar. My question is: where are they stored cos I want to delete one or more. I think they are addresses i have only replied to and never 'created' but have used for new messages, by just typing the first letter and selecting from there. Help please. Many thanks in advance. Lepak Lepak Nera...

Bar chart help wanted
Hello to everybody ! I have this kind of data: TIME VALUE 0:00:00 0 0:00:34 1 0:00:35 1 0:01:00 0 0:01:30 0 0:01:33 1 0:01:55 1 0:01:59 0 0:02:00 0 0:02:10 1 0:02:15 1 0:03:00 0 0:03:05 0 0:03:07 1 0:03:55 1 0:08:00 0 and I need to create bar chart that would have a bar of the hight equal to 1 at each time value where the "value" in table (Y-axis) equals one (and also between "one"s values). In all other time values the Y value is zero ! Excel bar chart seems to omit all values that are not in the dataset ! I need to include also those time values that are not expli...

Pivot table help #11
I needed help with a Pivot table. In the attached excel sheet i am trying to use the Creatiion date (coloum X)as one of the filters in the pivot table. Could someone please guide me as to how i can filter with the Creation date as one of the pivot tables. Thanks Sammy ...

Help with profiles .pst files and roaming profiles
Hi all and thanks in advance for the future answers; Well, we have a enviromente that goes like this: 1 Domain Controller 1 Mail Server running KERIO MAIL SERVER 2 Terminal Servers running CITRIX PS 4.5 1 Server running the citrix console The problem: We have like 50 users now that connect to servers A or B and have their roaming profile working fine, all files and configs come and go, with the exception of the OUTLOOK profile. When i had to add SERVER B to the system, i had to copy users folders from server A and apply the same permissions one by one, otherwise outlook would not work and ...