report based on a crosstab query
I've created a crosstab query in which row headings are a project number
[ProjNo], column headings are dates [Date] and each of the data fields has a
number of hours.
I built a report based on the query which worked perfectly until I wanted to
change dates. The date field in the report by default uses the actual date
value as a data source rather than the field [Date], so if the date is
different, the report doesn't recognize the new dates. How can I work around
this? Is there a way to 'reset' the dates in the report?
...make table query with related tables
I have an equipment maintenance database, I would like to put my inactive
(gotten rid of) equipment in an inactive table, and not loose the
relationship with the maintenance table. basically make two tables. I did a
make table query, made my inactive equipment table. I could not delete the
equipment from my first table due to key rules. I then made a relationship
from my equipment table to my maintenance performed table. could not make a
one to many and enforce integrity due to the first equipment table. I am
going to delete the relationships, run a delete query on my inactive marked
e...Highlight keywords in result of perameter query
I have a query perameter "*" & [Keyword Search] & "*" the returns the records
based on the keyword. Works great. Is there a way the keyword to be
highlighted or change color in the results?
Not that I know of. Especially in a query.
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
"Ryan Tisserand" <RyanTisserand@discussions.microsoft.com> wrote in message
>I have a query perameter &qu...Query Question
Please see the sql code below. What I am after is Sum of [RiskCount].
But due to the join, I get double counting and consequently wrong SUM.
Is this an inherent limitaion of Sql? Can be it solved without
breaking up the sql into two parts? Thanks for reading this post.
CREATE TABLE #Location (
CREATE TABLE #Coverage (
INSERT INTO #Location VALUES (1,1)
INSERT INTO #Location VALUES (2,1)
INSERT INTO #Location VALUES (3,1)
INSERT...Option Group Value won't display in Query when code is executed
I have an option group stored on a form with 4 choices and am just trying to
usethe value selected in the option group within a query. Simple enough, I
can get the query to work just fine, displaying information based on the
choice made with the option until I execute the query from code. Then it
seems that the option group value is not available or is ignored and does not
work. What I'm noticing is that if I pause the code at the point where the
query is suppose to run and execute the query manually, no information is
displayed (option group value shows blank). Howeve...query variables
As a test I am trying to use variables in a table query. In the query design
criteria numeric field if I place >10 the query filters OK, but if I put a
variable in the criteria the query asks me in a box for a value for that
variable and if I type >10 it does not work.
What I am actually trying to do is define the criteria using variables from
a form through a forms!xxxx!variable entry in the criteria of the table
query. Variable = "10" works but variable= ">10" does not.
Please help if you can. Thank you.
On Jan 22, 9:51=A0pm, "Wuf...SELECT DISTINCT query 05-14-10
Using Access 2007. I have a query (SQL below) in which I need to select
only those records with distinct FamID's I'm pretty sure I need to use
SELECT DISTINCT, but if I put that right at the beginning it's looking
for a whole distinct record, not just the distinct FamID (if I
understand it correctly). How can I change this query so that I end up
with records where the FamID field is distinct?
SELECT tblFamily.FamID, tblGroup.GroupName, tblFamily.FamLastName,
tblAddress.Street, tblAddress.City, tblAddress.State, tblAddress.ZipCode
FROM tblGroup INNER ...Query
I have two excel 97 files, the record is over a thousand
Invoice no. Amt
Invoice no. Pamt Pdate
4001 2 4-7-04
4012 3 13-7-04
I want to create a file to as follow
Invoice no. Amt Pamt Pdate
How to make it under Excel 97 ?? I already tried to use Query or Pivot Table
If I use Access, I use Query to do it and print it but I do not have a
licence in the office.
Once way is to use VLOOKUP. Try something like:...Dynamic query -How?
what are the types of dynamic queries? how can we use this ?
>what are the types of dynamic queries? how can we use this ?
Message posted via http://www.accessmonster.com
...Query Exchange Users via commandline
first I have to say, that I am not an exchange administrator or
something like that. I am an perl programmer that wants to improve an
intranet information system.
Most of our employees in the company work with the microsoft
terminalserver - via
the "query user" command it is possible to see, who ist logged in - and
of course I could also ping the client PCs to see which are switched
The infomation system I am programming shall find out who is in the
company and is available for email response. If the employee is away
his deputy shall get the mail.
Is there an...date mismatch in query..do not know how to solve
i have a query where the criteria statement has [date#1] equal to [date#2-1].
[date#1] is in a table and formatted as mm/dd/yyyy. date #2 is formatted as
date and time. how do i format date#2 to match the date#1 mm/dd/yyyy format?
On Fri, 21 Dec 2007 12:37:02 -0800, tcek <email@example.com>
>i have a query where the criteria statement has [date#1] equal to [date#2-1].
> [date#1] is in a table and formatted as mm/dd/yyyy. date #2 is formatted as
>date and time. how do i format date#2 to match the date#1 mm/dd/yyyy format?
The format is completely irr...My Access query shows blank in the label merge
When I try to merge the Access query I want into label format, the query
shows blank. I can merge the database table with no problem but not my
query. What am I doing wrong?
How are you trying to do this?
What is the SQL statement of your query?
I assume you mean the report shows blank (i.e., the query returns no
More info, please
Microsoft Access MVP
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement...Multiple Tables in Query not Listing Data.
I cannot seem to get this correct. I am trying to run a query on two
databases one of which is linked to the other. I am trying to get the query
to display a 0 for the total defects counted from one table based on the date
and time from another table. So far when I run the query I get only the
counts from the values that are not null.
Here is the SQL Statement:
SELECT Pro_Total_by_Cell.Cell, Pro_Total_by_Cell.Shift,
Pro_Total_by_Cell.Time, Pro_Total_by_Cell.ProTotal, Pro_Total_by_Cell.Time1,
IIf(IsNull([Counto...Make Table Query
How do you add (in run-time) primary key and indexing to a table (during
creation or after creation) when it has been created through a make-table
query? Either from a designed query or VBA SQL statement.
...Transferring multiple queries in single form
I have three crosstab queries that needs to be placed at the different
ranges in the same excel sheet .
How can I achieve this with do.cmd.transferspreadsheet.
Dim strQName As String
strQName = "7a-query"
Dim strNewName As String
strNewName = "c:\file.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQName,
I have the sample code here.
Now, how can I put the second query(which is 7b-query) on the same
tab,"7a-query" at the range, G2?
If you look at the definition of the TransferSpreadsheet method ...Queries about changing date format
I have a table that has a date in it and it also has the time. I need to get
rid of the time and leave the date. What is the best way to do this?
In a query? Just use format to return the date you want.
DateOnly: Format([YourFieldName],"short date")
Hope that helps!
"T Miller" <TMiller@discussions.microsoft.com> wrote in message
>I have a table that has a date in it and it also has the time. I need to
> rid of the time and leave the date. What is the best w...Append Query Key Violation
MS Office 2003
I am not a programmer
I want to append record from tblContactsV1 to tblPatientV1. tblContactsV1
has autonumber primary key; tblPatientV1 has a single primary key
(Chartnumber). I assume this is why record does not append "key violation."
If that is cause of key violation, how can I best correct?
Show us the structure of both tables -- field names, data types, and which
ones are primary key in each. Is there a foreign key in tblPatientV1 that is
related back to the primary key in tblContactsV1?
<MS ACCESS MVP>
"sh...Macro to check data from excel list against access query and return value back to excel
(I posted this yesterday but am tryign to make it clearer)
I have an excel list with "name" and "title" and there are many erros
and mis-spellings. I havbe an acess quesry that has the correct names
and titles and also some mis-spellings and foreign language spellings
pointing to the correct english name and title.
I would like to write a macro in excel to do a lookup of the excel
value in the access quesry and find EITHER an exact match or close
match and return the value of a specified field back into an excel
Here is a hypothetic example:
PK=Van G...Query Command
Working with a query that shows items checked out, i believe I figured out
how to show just the items for 2007 through 08. However I am having trouble
with showing them items that are blank with no check out date/time.
I am currently just using a Between #01/01/2007# and #01/01/2009#
I figured this will give me all records. However its not giving me the items
not checked out which is what I really need to see. How would I show all
items 2007 though 2008 plus the items without a checkout time/date?
Can you not add a or is null in the date parameter ?
> Wor...MS Query. Can you use parameters with LIKE and % wildcards?
I have (what I thought was) a very simple single table query, but I am
struggling with it in MS Query. I can do it using stored procedures
and ADO, but that would probably be overkill for such a small query and
I will only resort to that if somebody confirms that is impossible to
achieve using MS Query
My problem is that the parameter that the users will enter (location
code) is a substring of item code in the table. There can be more than
1 item code with the same location code embedded in it and this is what
the report seeks to identify. However the location code substring ...Query same category id
May i know how to query all the jobnumber where it is in the same category ID?
Because I want to use this as my list in combo box
Message posted via AccessMonster.com
I'm not clear yet what you are trying to achieve.
One interpretation of your question would be that you wish to group by
category ID. Another could be that you want to find jobnumbers that fall
into a (pre-selected) category ID.
Have you tried creating a new query that includes your conditions
(selection), then using that query as the source for ...Web Query How do I change columns of the MSN Stock quotes table?
I want to add the opening price to the web query MSN Moneycentral Investor
Stock Quotes. How can I adjust which columns are included in this table
If you are talking about the MSN Money Stock Quotes that appears on the data
menu, if you select that option, then Help, you'll get to a web page that has
another link entitled MSN Stock Quote Function Reference (or something
similar). It lists the information that's available and explains the syntax of
On Wed, 20 Oct 2004 06:13:07 -0700, "BKM24" <BKM24@discussions.microsoft.com>
>I want to add the...parameter query
Hi, I need help to finish my Query, I want to let the user put a serial
number to get it or put several serial numbers or collect it all
so I did this ...
(',' & [Enter SN's] & ',') Like ('*[, ]' & [Requisition Lines]![SN] & '[, ]*')
this is work to give a specific serial, but if I want let the users show all
serial numbers by clicking enter without write any number what is missing in
I appreciate ur cooperation
Try entering the following as the criteria:
((',' & [Enter SN's] & ',') Like...Query
The query below is working fine but once I have added this new line before
the last line:
ELSEIIf ([Forms]![frmErrorTyQuery]![cboTyError] = "Quantity Error", (SKF.
An error mssg will appear and I'm unable to save the Query:
[Syntax error in query expression will show]
PARAMETERS [Forms]![frmErrorTyQuery]![cboTyError] Short, [Forms]!
[frmErrorTyQuery]![txtFty] Short, [Forms]![frmErrorTyQuery]![txtStatus] Long,
WHERE IIf((Forms!frmErrorTyQuery!txtFty Is Null) Or (SKF.Factory Li...query for aging report
I need a query for an aging report in am Access ADP. I have a fees table and
a payments table.
[FeeID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[FeeDate] [datetime] NULL,
[DeptNo] [nvarchar](50) NULL,
[ServiceType] [nvarchar](50) NULL,
[Service] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
[FeeAmount] [money] NULL,
[FeeAdj] [money] NULL,
[TransNo] [nvarchar](50) NULL,
[PostedBy] [nvarchar](100) NULL,
[Comments] [nvarchar](max) NULL,
[PayID] [int] IDENTITY(1,1) NOT NULL,
[FeeID] [int] NULL,