Working Days Query
I was looking to work out which of my reports were late by 60/90 days and
someone kindly suggested to use < Date() -60 And < Date() -90 in the
criteria. This is great, however i am looking for a way for it to pick up
working days and (missing out weekends), is there anyway of doing this?
If you search the forum there are threads on help for missing out weekends.
> I was looking to work out which of my reports were late by 60/90 days and
> someone kindly suggested to use <...MS Query and SQL
I am trying to write a query where the following columns are present:
The problem is that when I set up the joins in MS Query, the records that
don't have an associated supplier assigned are not returned.
When I try to change the join type on Item.SupplieID-Supplier.ID, it says
outer joins are not allowed with more than two tables in the query.
Any ideas on how I can get it to return all records even if there is no
I'm not familiar with MSQuery, but if it will let you insert the SQL
command ...Calculation query, how to avoid extreme values?
I have 1 table with a few fields (A, B, C) that I'm using for various
For various reasons are some of the input "weird" and I want to leave that
out of the calculation (average).
For example, Field A requires > 0 AND < 100, B < 400
Now, if one record doesn't fullfil A requirement I don't want to use it for
A's average calculation BUT I want to use it for B's average calculation (if
it satisfy B's requirement).
This means that if I use the query criteria A: >0 AND <100 it will filter
out all other records an...use a results in a Query to compare with information in a table
This is what I am trying to accomplish: I do a Query on one table that
returns a result of the combined time and number of parts produced for that
time. Then I get an expression that results in number of parts produced per
hour. This is all good but now I need it to compare the “part number”
returned in the Query with the “part number” in the Parts table and compare
the “parts per hour produced” with the “parts per hour estimated” and return
as % efficient. How do I that information from the parts table? Thanks for
any help. Access 2007
We're not there, we can't see your...How do I use same date parameters for different queries and report
I'm having the same problem as Gavelle described below.
That is: I've followed the advice and set up a form to have my queries and
reports pick the dates from the form. But the queries/reports are still
asking me to
enter the beginning and ending date parameters for each query/report.
Also, when i set up the form it runs the query correctly. But when i try to
run the query (or report based off the query) I only get a general parameter
box stating Enter Parameter Value "forms!FORMNAME!StartDate and another for
the end date. Everything in my query criteria is typed correc...Date() frustration within query builder
I have created an application which scans barcoded mail items and
injects results into a.n.other table.
As part of v2 I'm trying to offer even more usability/funtionality.
Basically at the end of the day when the operator produces his/her
'dayend' tasks I want to offer them a button on the main form which,
when clicked runs a query which shows a total for today of pieces of
mail type "blah" - in this case, there's only a choice of two mail
types. goneaways & prizedraws.
http://pastebin.com/m2f6bec45 - this works fine when manually entering
the date in the...Can we create multiple pivot tabel views for a single access query?
For an access query there is an option to design pivot table using pivot table view. Now I want to create multiple such views for the same query. Is it possible or do I need to copy this query again into new query to create different pivot table view?
A Womand Told Me
A query can "remember" the last pivot view that you created, just like the
So, I'll agree that to help solve your problem, create a seperate query,
then tailor the pivot view for each instance.
Fwiw, I've tried to find where the pivot setup info is stored, but nob...Multiple Values in a single field with Microsoft Query
Is there a way to have multiple values in one cell that will pass into
a Microsoft Query.
What I'm trying to do is enter multiple numbers (4567, 1234) into cell
B3 (they won't be sequential so can use >,< etc.) and get the query to
I've posted the SQL below for my query. Just wondering if this was
possible or not without having to purchase add ins.
SELECT VDN_Data.Date, VDN_Data.dvdnVDN, VDN_Data.`VDN Name`,
VDN_Data.`Inbound Calls`, VDN_Data.`ACD Calls`, VDN_Data.`Aban Calls`
FROM `TeamStatsv1`.VDN_Data VDN_Data
WHERE (VDN_Data.Date Between B1 And B2) AND ...Problems Query with MSQuery
I try to Query from CSV file to Excel worksheet using MSQuery
One of the fields contains both numeric and text data but MSQuery return to Excel only the numeric data
What can i do to fix the problem
The following article on Dick Kusleika's weblog may help (from a post by
> I try to Query from CSV file to Excel worksheet using MSQuery.
> One of the fields contains both numeric and text data but MSQuery return to Excel only the numeric data.
> ...Running an access query
I have a table with multirecords, I would like to put the data in one row,
for example, I have a customer who has address 1, address 2 and address 3,
each address creates 1 record, how do I run a query that return the customer
name and address 1, address 2 and address 3 on one line?
So your table looks like:
Bob Bob's Address #1
Bob Bob's Address #2
Bob Bob's Address #3
Are there any other fields in the table?
"Armele look" wrote:
&...ADO: Help build this query in MS Access
I have a very large table in a MS Access database. It has various
fields in it. (Eg. Field1, Field2, Field3, Field4, Field5)
All I'm trying to do is to run a query that concatenates the values in
3 fields and returns a recordset.
In other words, I want a recordset which has only one field and that
field contains the values as follows field2+field3+field4.
Field1 Field2 Field3 Field4 Field5
1 AA XX PQR 112
2 BB XX STS 234
3 CC XX QQQ 234...new web query ( import data )
I knoticed that you can download tables into excel from a URL or web site.
mutiple tabples can be selected, however when I selected tables from a
different part of the URL during the same web query it only downloaded one of
the selections not all of them.
Is there anyway you can download mutiple tables with out conducting many new
...Database Queries/Date Formats
I am importing columns from an Access table using MS
Database Query. The date format in the database is YYMMDD,
so the query brings the date into the spreadsheet as the
same. (ex. 880321). When I attempt to format the cells as
a date in Excel, the formatting doesn't work, and the date
stays in the same format. Why? And how can I get around it
using Excel? Thanks.
Check the Access table, is the Date field set to a Date/Time datatype?
My guess is not. You can remedy this in Excel or in Access with a date
"daver676" <email@example.com> wrote in me...can't get query to group
here is the sql.
can someone help me determine why the query isn't grouping by GoupNumber?
The GroupName is the same for the GroupNumber. ID is unique.
SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName
GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID
HAVING (((Accounts.ID) Like "01*"));
ID GroupNumber GroupName
010101 01 red
010102 01 red
010201 02 blue
010301 03 ...Query combined with formula
I have made a query, that gets the parameter from my cell A11. There
is always only one result, which is written i cell B11.
Now i wish to copy that formula from A11 to the area A12:A3000, so it
picks up the parameter from the row, where the formula is placed. The
result should be, that a query request data based on A11 and spit it
out in B11; another query uses A12 and spits out in B12 (...)
Now I need it for now in 3000 rows, and that might expande drasticly
over time, so how do I easy copy it down?
Regular copy/paste only copy the formula or value of the cells, not
...Export query from a switchboard
I'm somewhat new to Access. Thanks for the assist in advance.
I have a database where I would like to graph the results of a query in
EXCEL. The Graph does not appear well within the Chart function of a report.
At present I run the query and copy and past into EXCEL and then create my
I would like to place a command on my switchboard menu that will export the
query to EXCEL. I know if I can create a macro or a VBA module, I can so
this from the menu I've created. I have unsuccessfully attempted to create a
macro and I don't know how to code in order to do export. I w...MS Query
Hi thanks for any advice you can offer.
Attempting to build a pivot table using external data, connecting to a
Microsoft SQL Server. I paste the SQL that I use in Enterprise Mgr directly
into MS Query. When I execute the query, the dataset is displayed in MS Query
- all 107,000 records. When I ask MS Query to return the data to Excel, I get
this message: "Problems Obtaining Data."
Which version of excel are you using? All versions except 2007 have a
limit of 65536 rows and cannot present your data that exceeds 100000
On Oct 26, 10:07 pm, gtgtgt <g...Using Option Group to select a range of Queries
I'm trying to set a form up to select a range of columns in which I'd like to
search for a specific string using presaved queries.
I'm thinking of having 10 text boxes, each displaying a single line of the
received data. I was then going to have to columns of radio buttons to
signify the start and end of the search. The user would then be able to
select 'Start button' 3 and 'end button 5', which, after clicking on the 'go'
button, would run the 'search column 3', 'search column 4' and 'search column
5' queries. If the Star...program to make Tables and Queries becomes READ ONLY
Anyone out there knows how to write a code to make the Tables and
Queries to be "READ ONLY" in a database, so when we convert that
database to a "MDE" mode, then everthing is secured?
Your help is much appreciated.
No, there isn't.
If you use only queries, and have DISTINCT in each of the queries, you won't
be able to update the data through the forms. However, you'll still be able
to get to the tables by linking to the database.
Note that your application should be split into a front-end (containing the
queries, forms, reports, macros and modules), li...help with 2 queries to compare and delete from same source table
(This may sound complicated, please bear with me...)
I have a quiz results form that shows the number of times each incorrect
answer has been submitted via a query.
The query gets 'incorrect score results' from a table called
'tbl_Final_Points_Test'. It uses the unique ID for each answer as a COUNT
total which gives me the total number of times each Incorrect answer has been
chosen, and it uses the criteria '0' for displaying only incorrect answers.
This all works fine, except as time goes by, the number of each total
incorrect entry increases (as it..."Totals" button in query
While trying to figure out a vacationing coworker's query, I find that
he's got a "Total" row in the quey grid, and I discovered that by
clicking on what looks like the greek letter sigma on the toolbar,
that it toggles this row on/off.
Suboptions are Group By, Sum, Avg, etc. but I can't tell from
executing the query that they do anything.
All his "Total" values are "Group By."
Thanks for any insight.
A "Totals" query is a Select query that is doing some aggregate calculations
on the data selected: Sum/Average/Count the number of rec...Need help!
I am trying to extract some data from an Access query using MS Query while
in Excel. The access query will extract the data from the data source
without any problems. (I can even cut and past the data from the access
query to the excel spreadsheet.) The problem arises when I go to refresh the
data in the excel spreadsheet using the MS query. I get the following error
"[Microsoft][ODBC Microsoft Access Driver] Invalid character value for cast
specification on column number 4 (SumofSumofTransValue)"
Can anyone shed some light on this problem? Everything wo...Basic Select Query Duplicating a single end user's records
I am stumped with a particular problem. My table consists of hundreds of
records that are directed to their associated end user. This is my simple
Select query shown here:
"SELECT [Name Tbl].Mgr, [Name Tbl].Name, [Data Tbl].Survey_CNT,
FROM Manager INNER JOIN ([Name Tbl] INNER JOIN [Data Tbl] ON [Name Tbl].ID#
= [Data Tbl].Name) ON Manager.OM = [Name Tbl].Mgr"
Inside the Data Tbl, one Name shows 4 physical records. However, in this
simple query it has duplicated one Name's records so now there is 8 for that
one particular name.
The other Names show a correct m...sums on a query
I’m building a query that I would like to sum quantities of a part numbers
used on multiple Purchase Orders
PO Item # qty sum
16 100 2 5
27 100 1 5
39 100 2 5
19 200 2 7
26 200 5 7
Use the 'Totals' button in the query designer (see toolbar in query, look for
Then group the field you want for instance the 'item' field, just above the
criteria field use the arrow for the group by..
> I’m building a query that I would like to sum quantities of a part num...how to transfer a T-SQL query to a semantec query and create the M
How to transfer lots of T-SQL input from users or files to a semantec query
used in Reporting Service of SQL Server 2005 and create the MSDL file