I am looking for some advice on how to tackle querying some data.
I have two tables, table one with around 10,000 records per month and
columns that give me (for each record) a start date, start time, end date and
Table two details a percentage for each hour and day of the week (so 24
hours * 7 days = 168 records). This has three columns, the day of the week
(Mon to Sun), the hour (00:00 to 23:00) and a percentage.
What I want to do is sum the total of the percentage column in table 2 where
the day of the week and the hour of the week fall within the st...Query question.
I need to know how to format a query to produce columns of
"best fit" width. Specifically in a Xtab query.
Please help, Frank
What do you mean by 'best fit width'?
The width of a column is really a display issue on whatever form or report
will show the results.
If you are saying you need to know the maximum length of the data in any
column of the entire table, you'd need to take a 2-pass approach where the
first pass calculates the max length of each column and the second pass
formats the data.
"Give someone a fish and they eat fo...Help creating updateable query
Trying to create an updateable query, but, I keep getting "Operation must use
updateable query" message when trying to do the following:
Table A: Table B:
LOC VALUE LOC VALUE
A 10 A <blank>
B 40 B <blank>
B 20 C <blank>
...Microsoft query does not recognize tables in Excel spreadsheet
Using Excel 2007 12.0 SP 2 on Windows XP
I have created an Excel spreadsheet, C:\Temp\PracDbase.xlsx with two tables:
Both of these are on the same worksheet, sheet1. The first table occupies
the range A1:B3, the second occupies the range D1:E3.
Both of these ranges have been converted to tables via Home/Format As Table.
If I look up names under Formulas/Name Manager, both of these
table names are there and they refer to the correct range.
ie, I am certain that the tables Table1 and Table2 exist in
and...Help with macro looping and color query function
I have been struggling to get a macro together to perform some tricky
coloring functions. With the kind help of a number of people in the 'Excel
Worksheet Forum' I have got close to achieving something which works but need
help to get the macro to run. Here is where I have got to:
The following is an example macro, kindly provided to me by Paul who has
been helping me in the worksheet forum:
' Macro1 Macro
' Macro recorded 19/05/2005
mycell = Range("A1").Value
mycolor = Range("C1").Value
Sheets("Sheet2...Save DB query results as XML file
I am trying to query a SQL Server database, retrieve the results as XML, and
save them to a file. I was trying to use the SqlXmlCommand,
SqlXmlCommandType.XPath and an xsd to query the database, and load the
results into a XmlTextReader. This works fine. But then I get stuck. I
don't want to DO anything with the data besides putting it into a file.
Should I be loading the results into a stream, instead? Then load the stream
into a string, to an XmlDocument, then .Save(...)? Or might a XmlTextWriter
have a role in here somewhere?
Can anyone give me a hint on how I should appr...iif, len, replace queries
I am somewhat of a beginner in regards to my Access skillset.
I am trying to create two separate queries and am in need of assistance. I
have been trying to use iif, len and replace but am struggling.
1. Text Field (numeric and alphanumeric): If the length of the string is not
equal to 10 characters I need to set that value to "9999999999".
2. Text Field (numeric and alphanumeric): I need to remove all "-" and all
spaces. From this result I need to display the first 10 characters. If there
are less then 10 characters then no value is to be displayed.
Thank you...Multi Query report
Hi All, I have a report that is produced from a query, it is running fine but
i would like to add an additional text box on the report to pull the result
from a different query column, is this possible?
If both queries have something to key on. Like maybe an ID number or a part
number, something that matches. Then use a subreport and link them using link
master and child fields.
State of Arkansas
> Hi All, I have a report that is produced from a query, it is running fine but
> i would like to add an addi...update query
How can i build a query where all the rows having -. for example -1, or -2
etc, to be set to o ?
Message posted via AccessMonster.com
On Fri, 29 Feb 2008 06:43:35 GMT, "peljo via AccessMonster.com" <u19312@uwe>
>How can i build a query where all the rows having -. for example -1, or -2
>etc, to be set to o ?
Create a Query based on your table.
On the Criteria line under the relevant field, put your criterion - for
instance just -1 or
IN (-1, -2)
if you want all records with either of ...Monthly Query
I'm not sure how to do this but I know it is possible. If I have a table
that gets created every month with table name NOV 07 and the following month
table DEC 07 and so on.........is there a way to have a query to extract the
data from NOV 07 table and when its the next month run the same query but for
the DEC 07 table. The queries will then be used to create monthly reports.
How can this be done?
First point. You design is incorrect. There should not be a situation where
each month is in a different table. Use a field in your table to identify to
which month it belongs. Yo...Inability to edit or view queries
I have a spreadsheet on a network, that queries an Acess database, on the
same network. When I try to view/edit the query the option is greyed out.
Why can't I get into the query? I've got read and write priviledges.
Any help/ideas would be appreciated.
...Multiple Tables one Query
Ok, I've read several articles, help files, yadda yadda but I can't seem to
make this work. What is the proper way to get information from two tables
into one Query or form?
help me to set up an email address
"Matt" <Matt@discussions.microsoft.com> wrote in message
> Ok, I've read several articles, help files, yadda yadda but I can't seem
> make this work. What is the proper way to get information from two tables
> into one Query or form?
On Fri, 29 Feb 2008 12:34:02 -0800, Mat...MDE File
Is there anything you can do to a MDE file to prevent users from
chnaging/modfying queries or tables?
I wrote some of my reports, etc. based on queries and am worried that they
might accidently alter the query.
Any advice how others have handles this?
It is possible to block people with Access Security, though this is probably
overkill. Also, I can't recommend that approach, since MS has removed it
from the new file format in A2007.
One simple idea is to use attached tables. This has lots of advantages, one
being that users can't just change the table: they have to figure out w...Query using list of parameters
Is it possible to limit query results by a list of data or an aray rather
than a single parameter?
You may want to import the entire table into excel and then use Advance
filters in Excel to filter the table.
A query uses SQL language which Microsoft call the command Text part of
the query. I'm not an expert on SQL but know a lot about SQL language.
I haven't seen any way of automatically getting data from a table but
somebody else may be able to answer that part of your question.
A SQL statement can be very long and contain multiple WHERE phases. If
you were to crea...Reference a Date and Time in Query
I am trying to use a query to filter the table results by Date and
Time. The table data is in the format:
mm/dd/yyyy HH:MM:SS *M
I already have a query that selects the data between two dates. But I
also want to be able to select two dates and two times. Such as
Between 3/15/2007 9:00 AM and 3/15/2007 10:00 AM, or use two different
dates and times, etc.
I have gotten close by using:
Between ([Forms]![RC_WithTime]![Calendar1]+TimeSerial(9,0,0)) And
but as soon as I try to convert the TimeSerial to link to forms it
says that the...Query Design Help Needed
I am having difficulty designing a query and would like some help. The query
should return only ONE row per SalesOrder. The query should SUM the
receveibles from a table Receivables where SalesOrderNo is the foreignkey
linking to Parent SalesOrder table and the receivable was paid within a
specified date range. Currently I am getting a row for EACH receivable
within a date range. Payables are summed regardless of the date range. Just
fetch all costs for the given SalesOrderNo. Any help? Do I need to be more
It would help if you posted the SQL of the quer...Query join Date\Time confusion
I have a table that is produced from a download from a Index telephone
It stores the date and time data in one column in the format:
The switch also produces an agent table tha shows their login, log off,
free, busy status. However this stores just the Time in
Now I need to list the agent status against the long date time format.
However I join the two tables by the date columns but access doesnt match
due to the Date being on the left column.
I've tried various methods, I.e. creating a query that looks like this
Date: =date() ...Proc cache consumed with one-time queries
In investigating memory usage os our SQL Server 2005 SP3, I noticed that
about 4.3GB of our 32GB phsyical memory was consumed by the procedure cache.
In looking at what was in the cache, I found that the overwhelming majority
of the stored plans were from queries that will never be reused. In fact
4.1GB of the 4.3GB were plans with usecounts=1.
Most of these turn out to be statements generated by Sql Server itself:
there are many that are statements generated by SqlAgent, running scheduled
tasks. Many more are the backup statements generated for log shipping. In
these cases, ...combo box query 03-20-07
I have a form containing all the books in a bookstore what I want todo is to place a combo box on the form which lists the book categoriesi.e. Science, Language, Travel so that when the user clicks onLanguage for instance then all the books that appear in the Languagcategory appearany suggestions?? "Raza" <email@example.com> wrote in message news:firstname.lastname@example.org...>I have a form containing all the books in a bookstore what I want to> do is to place a combo box on the form which lists the book categories> i.e. Science, Langua...Delete Records from Table using Query Results
I have a table named "Records" that contains a field "Customer Number". I
need to delete all the records in table "Records" if the "Customer Number" is
also in the table named "Transfer". Is there a simple way to accomplish
this? Thank you!
Use a delete query
WHERE [Customer Number] in
(SELECT [Customer Number] FROM Transfer)
In query design view
== Add the table Records
== Select the customer number into the list of fields
== Select Query: Delete from the menu
== Enter the criteria under the custo...Query and view multiple projects
Assuming that I've defined a user field (text1) as "Task Type"...
Can a user assigned to the executive group (or any group for that matter)
perform a query/grouping like:
Show me all projects where Task Type="foo" and start date is between
01/25/10 and 01/30/10.
Yes and No...
1st, the task text1 field will need to be an Enterprise Text Field, and this
field will need to be added to the OLAP cube. If the task has work on it,
then you could at least show the start date of the task, and from that you
could get the list of projects.
Ano...Can publisher 2000 merge to an access 2000 query.
I have a database which is in Access 2000 format, and I was trying to pull
information from it into Publisher 2000. I keep getting an error that goes
something like " Publisher cannot open the file, it may be damaged or in a
format Publisher does not understand" The mdb file opens fine in Access 2000
and in XP version (which is on another PC), and is definitely in Access 2000
format. Another mdb on my computer will open and work properly with
Publisher, but the info I want is not in that one, this leads me to think
that there must be something wrong with the database? Any sugg...How change table name including references in forms and queries?
Is there a way to change a table name including references in dependent forms
and queries? It looks like I would have to redesign the dependent forms
using the new table name.
Products to document the database and do a global find and replace. All have
Access 97 and later versions. Some don't have an Access 2003 version, but may
still work with Access 2003.
Shareware (Try and Buy):
Find and Replace http://www.rickworld.com
Commercial (Try and Buy)
Speed Ferret http://www.moshannon.com
Total Access Analyzer http://www.fmsinc.com
Free (For...help with query 03-19-10
sql server 2008 sp1
I can't seem to get this query right. I need a way to read a table
and for each person, show the person's
highest value and the person's last entered value to get a result set
person_id high last
------------- ----- -----
1 40 20
2 30 15
here is a sample schema
create table test (person_id int, enter_value int, enter_date
insert into test values (1, 30, '2001-01-01')
insert into test values (1, 40, '2002-01-01')
insert into test values (1, 10, '...combobox in query
Hello everybody, I have a question concerning the "Enter Parameter Value"
window that opens when I run a query.
Is it possible to have that as a combobox so that a list of possibilities is
Another question has to do with the same query. This asks a date from and to
(in the query criteria >[from] And [to]. I would like the selected period to
be shown in the report. Is this possible (and if so... how can I achieve
Thanks for all the help you can give
Save the Dogs Onlus - www.savethedogs.eu
THINK BEFORE YOU PRINT: before printing this e-mail think whe...