A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Search Forums
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Showing results 1 to 25 of 100
Search took 13.97 seconds.
Search: Posts made by: Jerry Whittle
Forum: Running & Setting Up Queries May 28th, 2010, 07:54 PM Posted to microsoft.public.access.queries
Replies: 8
Views: 392
Posted By Jerry Whittle
Joining 3 Queries That Count

My bad! I forgot that you must declare the data type for a parameter query
if it's going to be used in a crosstab. This is something that started in
Access 2003 if I remember correctly. The very...
Forum: General Discussion May 28th, 2010, 04:54 PM Posted to microsoft.public.access
Replies: 10
Views: 677
Posted By Jerry Whittle
My last post in this forum

Hi Wayne,

For some reason Microsoft Answers is blocked at work so I won't get there
much. UtterAccess might be my place to be of help to others.

Hope to run into you again.
--
Jerry Whittle,...
Forum: Running & Setting Up Queries May 28th, 2010, 04:44 PM Posted to microsoft.public.access.queries
Replies: 8
Views: 392
Posted By Jerry Whittle
Joining 3 Queries That Count

I'd create a union query like below. Then use it as the record source for a
crosstab query.

SELECT [Master Table].[SUPPORT MGR],
"Completed",
Count([Master Table].[MERCHANT ID]) AS TheCount
FROM...
Forum: Running & Setting Up Queries May 27th, 2010, 10:56 PM Posted to microsoft.public.access.queries
Replies: 5
Views: 491
Posted By Jerry Whittle
Blank Fields in NOT Null Query Criteria

There are four things that can cause a 'blank' field. Nulls as you already
surmised.

Next come Zero Length Strings. Basically they are just "". That is a text
string with nothing in it....
Forum: Running & Setting Up Queries May 27th, 2010, 10:43 PM Posted to microsoft.public.access.queries
Replies: 4
Views: 352
Posted By Jerry Whittle
aggregate calculation works in select query but not an update

I'm very surprised that the query below is updateable when attached to a
form. There's multiple Right and Left Joins and some functions which would
keep those fields from being updated....
Forum: Running & Setting Up Queries May 27th, 2010, 10:42 PM Posted to microsoft.public.access.queries
Replies: 2
Views: 284
Posted By Jerry Whittle
min query

SELECT TOP 3 dbo_CON.SC,
dbo_CON.SN,
Min(dbo_CON.SQ) AS MinOfSQ
FROM dbo_CON
GROUP BY dbo_CON.SC,
dbo_CON.SN
HAVING dbo_CON.SC49
ORDER BY Min(dbo_CON.SQ) ;
--
Jerry Whittle, Microsoft Access MVP
Forum: General Discussion May 27th, 2010, 10:08 PM Posted to microsoft.public.access
Replies: 0
Views: 237
Posted By Jerry Whittle
Form from a query

First go to the Relationship window and join the two tables with Referential
Integrity enabled. If it won't enable, fix the problem records.

Next create a form based on the first table. On this...
Forum: Running & Setting Up Queries May 27th, 2010, 09:56 PM Posted to microsoft.public.access.queries
Replies: 4
Views: 352
Posted By Jerry Whittle
aggregate calculation works in select query but not an update quer

QryBOMDetails2

We probably need to see the sql for it. There's many reasons why a query
isn't updateable.

BTW: Check out my signature line!
--
Jerry Whittle, Microsoft Access MVP
Light....
Forum: Running & Setting Up Queries May 27th, 2010, 09:34 PM Posted to microsoft.public.access.queries
Replies: 1
Views: 355
Posted By Jerry Whittle
unique records Access 2003

Something like the SQL statement below should work. If you aren't use to
using SQL, do this:

Create a plain old select query based on the table and bring down the two
fields in question.

Next...
Forum: Running & Setting Up Queries May 27th, 2010, 09:33 PM Posted to microsoft.public.access.queries
Replies: 1
Views: 336
Posted By Jerry Whittle
How to Union Crosstabs...

Also, when crosstabing, I noticed one of my 5 customers don't show any data
for a week because the days taken were less than 4 that week. Is there a way
to get results for each week even when...
Forum: General Discussion May 27th, 2010, 09:29 PM Posted to microsoft.public.access
Replies: 5
Views: 236
Posted By Jerry Whittle
Table security

You need to implement User Level Security. That way you can restrict user to
certain forms and reports plus not allow them to even see the tables.

However this is a LOT of work both to set up and...
Forum: Running & Setting Up Queries May 27th, 2010, 09:13 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 357
Posted By Jerry Whittle
Parameter Error for Year 2010

Forms and reports based on crosstab queries can be a problem. A crosstab
could produce many fields with different names and a form or report is
expecting just certain fields and names.

If you know...
Forum: Running & Setting Up Queries May 27th, 2010, 09:04 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 357
Posted By Jerry Whittle
Parameter Error for Year 2010

I've found it best to put any parameters and criteria in another query then
base the crosstab on the first query.

Also I'd have to wonder about using Text for the parameter data type. I'd
rather...
Forum: Running & Setting Up Queries May 27th, 2010, 08:58 PM Posted to microsoft.public.access.queries
Replies: 1
Views: 355
Posted By Jerry Whittle
unique records Access 2003

SELECT valveID, Max([Date])
FROM YourTable
GROUP BY valveID
ORDER BY valveID;

If you have a duplicate valveID/Mas of Date combos, it will return all ties.
You could try changing SELECT to SELECT...
Forum: Database Design May 25th, 2010, 08:17 PM Posted to microsoft.public.access.tablesdbdesign
Replies: 6
Views: 360
Posted By Jerry Whittle
how to set a certain format

It looks like that job number has different 'parts' that have meaning. If so
you'd be much better of having them broken down into 3 or 4 different fields.
Then recombine them afterwards. Then you...
Forum: Database Design May 25th, 2010, 05:09 PM Posted to microsoft.public.access.queries,microsoft.public.access.tablesdbdesign,microsoft.public.access
Replies: 1
Views: 278
Posted By Jerry Whittle
Trying to Siplify a Query in MS Access 2003

The 7 queries for the spouse and 6 children is the problem. If nothing else,
there are families with more than 6 kids!

Instead of these 7 queries, you should try creating a single Dependents...
Forum: Running & Setting Up Queries May 25th, 2010, 05:09 PM Posted to microsoft.public.access.queries,microsoft.public.access.tablesdbdesign,microsoft.public.access
Replies: 1
Views: 277
Posted By Jerry Whittle
Trying to Siplify a Query in MS Access 2003

The 7 queries for the spouse and 6 children is the problem. If nothing else,
there are families with more than 6 kids!

Instead of these 7 queries, you should try creating a single Dependents...
Forum: General Discussion May 25th, 2010, 05:09 PM Posted to microsoft.public.access.queries,microsoft.public.access.tablesdbdesign,microsoft.public.access
Replies: 1
Views: 126
Posted By Jerry Whittle
Trying to Siplify a Query in MS Access 2003

The 7 queries for the spouse and 6 children is the problem. If nothing else,
there are families with more than 6 kids!

Instead of these 7 queries, you should try creating a single Dependents...
Forum: Running & Setting Up Queries May 20th, 2010, 09:34 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 468
Posted By Jerry Whittle
Union Query Access 2007 SP2 MSO

It's not a reserved word problem. It would need to be a plain old Date
without brackets around it for that to happen. However your Type field could
be a...
Forum: Database Design May 20th, 2010, 06:19 PM Posted to microsoft.public.access.tablesdbdesign
Replies: 2
Views: 271
Posted By Jerry Whittle
Relationship

The client will not show up automatically in the other tables and for a good
reason. You could have a new Customer that hasn't created an Order yet.
Therefore you don't want a record in the Orders...
Forum: Running & Setting Up Queries May 20th, 2010, 06:08 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 468
Posted By Jerry Whittle
Union Query Access 2007 SP2 MSO

Also on A2007, I just ran a simple union query on 3 tables that totaled
2,244,414 records with no problems. Going to the last record using the record
selector took a couple of minutes. These tables...
Forum: General Discussion May 20th, 2010, 04:47 PM Posted to microsoft.public.access
Replies: 2
Views: 285
Posted By Jerry Whittle
Counting number of records based on criteria

The 999 needs to go in the criteria for the query driving the report or the
filter for the report.

You could also group by Field1 if you want to see the count for the various
data in Field1.

You...
Forum: General Discussion May 20th, 2010, 04:38 PM Posted to microsoft.public.access
Replies: 4
Views: 130
Posted By Jerry Whittle
How to protect myself without a condom

If you make the database an MDE file, that effectively hides the code in
modules, forms, and reports to where mere mortals can't get to it. Tables,
queries, and macros (for the most part) can still...
Forum: Running & Setting Up Queries May 20th, 2010, 04:34 PM Posted to microsoft.public.access.queries
Replies: 7
Views: 468
Posted By Jerry Whittle
Union Query Access 2007 SP2 MSO

Are any of these field Memo datatypes? I've run into strange things with
corrupt memo fields before. If any are Memos, try removing those fields from
the SQL and see if the union query then...
Forum: New Users May 19th, 2010, 09:26 PM Posted to microsoft.public.access.gettingstarted
Replies: 3
Views: 275
Posted By Jerry Whittle
I am using Window 7 and Access 2007

In the VB window, type the following in the Immediate pane:

Debug.Print Trim(" 1 " )

If you get an error, then it's probably a References issue. Go up to Tools,
References and see if anything is...
Showing results 1 to 25 of 100

 
Forum Jump

All times are GMT +1. The time now is 07:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.