|
 |
| Recent blog posts: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Wednesday, 9 August 2006
|
| HELP: Cross-Tabs, Stored Procedures and other Wonders of the World xFiver 01:47:02 |
| | Hey gang,
I'm having a tough time wrapping my head around this problem. More than
anything, I'm looking for the "why" of why this isn't working.
THE SETUP:
I have a Cross-Tab query (we'll call qCrossTab) that details and sums
up the number of absences from class between a particular time frame
(for testing, I've hard-coded it into the SQL)
WHERE (((URKIDS_ATTENDANCE.CLASS_DATE) >= #8/1/1880# And
(URKIDS_ATTENDANCE.CLASS_DATE) <= #8/2/1880#))
Since the number of columns can vary (or will vary once I've added the
ability for the user to select date parameters), I've created a report
(rptAttendance) that does some formatting based on the number of
columns. The RECORDSOURCE of rptAttendance is set to qCrossTab
With this setup, everything works like a charm.
THE PROBLEM AND MYSTERY:
I am building a form that will eventually ask the user to select the
FROM and TO dates to embed into the WHERE statement above. For right
now, the form simply has one button (GENERATE REPORT) that attempts to
launch rptAttendance while passing it a STRING value (strSQL). NOTE:
strSQL below is an exact copy of the SQL from qAttendance.
Code for cbGenerateReport
=========================================================
Private Sub cbGenerateReport_Click()
Dim strSQL As String
' builds the SQL one phrase at a time
strSQL = "TRANSFORM Sum(URKIDS_ATTENDANCE.ABSENT) AS SumOfABSENT "
strSQL = strSQL & "SELECT URKIDS_ATTENDANCE.URKIDSID,
URKIDS_ATTENDANCE.GROUPID, Abs(Sum(URKIDS_ATTENDANCE.ABSENT)) AS [Total
of ABSENT] "
strSQL = strSQL & "FROM URKIDS_GROUP RIGHT JOIN URKIDS_ATTENDANCE ON
URKIDS_GROUP.ID = URKIDS_ATTENDANCE.GROUPID "
strSQL = strSQL & "WHERE (((URKIDS_ATTENDANCE.CLASS_DATE) >= #8/1/1880#
And (URKIDS_ATTENDANCE.CLASS_DATE) <= #8/2/1880#)) "
strSQL = strSQL & "GROUP BY URKIDS_ATTENDANCE.URKIDSID,
URKIDS_ATTENDANCE.GROUPID, URKIDS_GROUP.SEASON "
strSQL = strSQL & "ORDER BY URKIDS_GROUP.SEASON "
strSQL = strSQL & "PIVOT URKIDS_ATTENDANCE.CLASS_DATE"
DoCmd.OpenReport "rptURKids-Attendance-Crosstab", acViewPreview, , , ,
strSQL
End Sub
===========================================================
What's odd, is that when I run the report from this code (which,
technically, should have the exact same control source as just running
the report on it's own), I get the following error:
Run-time error '3637':
Cannot use the crosstab of a non-fixed column as a subquery
From my research, it sounds as though I need to use STORED PROCEDURES with bound columnheadings in order for a report to change it's control,
which doesn't make sense since I don't see the difference between the
two directions above...
OTHER POSSIBLE SOLUTIONS
Since I don't really get SP at the moment, I was trying to figure out a
way to get this to work.
I created a query that simply SELECT * from qCrossTab The problem
with this is that there's no way to specify the date parameters because
this is part of qCrossTab.
So, I thought, what if I could create an SQL statement and save it to
qCrossTab, overwriting what's already there. Then, I could run my
report with the proper date parameters. Question is, though, how can
I, in VB, save a query? I'm sure this isn't the best way to structure
a database, but it seems like it could be easier than fumbling around
with stored procedures.
Phew. That's all. Thanks for sticking this out. Thoughts? Suggestions?
Thanks,
Mike
|
| |  1 answer | Add comment |
|
| Help with simple querry please! Clownfish 01:27:03 |
| | OK, I'm having a brain freeze.
I have a table like this:
Office Name Phone
----------------------------------
SG Larry 555-1212
SG Moe 553-4444
SG Curly 666-8888
PO Ren 222-9999
PO Stimpy 555-8888
and I want to a querry that produces this:
Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 3
SG Moe 553-4444 3
SG Curly 666-8888 3
PO Ren 222-9999 2
PO Stimpy 555-8888 2
I try this:
SELECT Office, Name, Phone, Count(Office) AS OfficeCount
FROM Table
GROUP BY Office, Name, Phone
and I get
Office Name Phone OfficeCount
----------------------------------------------------------
SG Larry 555-1212 1
SG Moe 553-4444 1
SG Curly 666-8888 1
PO Ren 222-9999 1
PO Stimpy 555-8888 1
What am I missing or doing wrong?
Thanks!
Pete
|
| | 5 answers | Add comment |
Tuesday, 8 August 2006
|
| Access XP / MySQL / MyODBC: Access' caching system? Onnodb@Gmail.Com 23:49:56 |
| | Hi all,
While working on an Access UI to a MySQL database (which should be a
reasonable, low-cost, flexible interface to the DB, better than
web-based, much less costly than a full-fledged .NET app or so.... is
it?), I tried measuring the bandwith consumed by the
Access/MyODBC/MySQL link, which came out to be, er, quite high.
I fancied it would be interesting to look at the queries Access throws
at MySQL through the ODBC link, so I set up a query log for MySQL. It
now appears that Access tries to cache the records in the database, but
it does so in quite a weird way.
The db is accessed through a normal (one-record view) form, linked to a
query, linked to a "linked table". When I go to record #5 (of 5000) in
the form, a whole bunch of queries is executed (because of a subform,
but there also appears a bunch of lines in the log like "SET AUTOCOMMIT
= 0" every time I scroll... Pretty strange, this is a huge overhead).
What stroke me most is that Access seems to fetch records 41 to 50.
When I press PgDn again, scrolling to record #6, Access fetches 51 to
60, and it fetches 61 to 70 for record #7 on the screen, and so on.
Scrolling back to #5 makes Access fetch 41 to 50 again.
Is there some ridiculously failing caching system at work here? Is
there a way to disable it? I read some things about CacheSize; how
should I use it in a form with an updatable query?
Thanks for your time; this seems to be the final big problem until we
can start working seriously on this system, so I'd be really glad if I
could fix this 
Yours sincerely,
Onno
|
| | 6 answers | Add comment |
|
| Jet Roster doesn't seem to be working rdemyan via AccessMonster.com 23:46:25 |
| | I've implemented the Jet Roster code for determining how many users are
logged into the backend file.
There are many code examples and I've copied them. Also, I've checked at
least 20 times that the two computers are connected to the same backend file.
But when I run the Jet Roster code on each computer it only picks up the
computer on which it is running.
I've made sure that I have forms open on both computers that have actively
retrieved data.
I'm at a total loss. Any ideas, no matter how off the wall.
Thanks.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200608/1
|
| | 6 answers | Add comment |
|
| show last updated date on reports Guest 23:05:05 |
| | Does anyone know how to have Access put the "last updated" date on a
report? I have a table of our university's athletic events, grouped by
each sport per page. I'm trying to get the report to print the last
date updated, even if the secretary who does the input has to put it in
- hopefully i can set it to prompt her or do it automatically.
If anyone has an idea, I'd really appreciate it. I can give further
details of the set up, but the basics are:
Events table holds all data, Sports table list each sport and I've made
an "Updated" field in that table. The Events table uses the Sports
table sa a lookup for that field in Events.
Any help would be greatly appreciated!
Thanks,
Eddie
|
| | 1 answer | Add comment |
|
| Documentation of Tables Dragon 22:29:49 |
| | I have a database with 6 different tables in it. I have been told to
document the tables from the design mode into Excel. I don't want to
go into Excel and type each field and the format. Is there a way that I
can export this into Excel?
Thanks.
|
| | 1 answer | Add comment |
|
| dates and currency Elainie 20:15:44 |
| | I have a table and a form. I have a weekly cost field and a monthly
cost field and a
projected total field. I have got the monthly cost field to add up the
weekly cost field and now would like the projected total field to add
up the monthly cost field between 2 date fields, the effective start
date and the end date.
Don't know where to start
HELP!
Elaine
|
| | 2 answer | Add comment |
|
| Access Update Query Help Csgraham74 19:36:50 |
| | Hi Guys,
I was wondering if someone could help me with an access query.
I basicallly have two tables A & B
Im table A there are numerous records with Fields1 & fields 2
I want to use table B to update table A. Bascically i need to compare
table A & Table B - if records exist in Table B that are not in Table A
then i need to update table A with the appropriate records.
any help appreciated
Thanks
C
|
| | 1 answer | Add comment |
|
| Extracting email attachment in Outlook Dave G @ K2 18:50:42 |
| | I'm writing a function to look for particular emails in my Inbox and
when it finds one it copies the attachment to a folder and then deletes
the email. It spots the email by looking for certain text in the
subject line. Here's a code fragment:
Dim appOutlook As Outlook.Application
Dim mpiFolder As MAPIFolder
Dim msgMail As MailItem
Dim strAQSPath As String
Dim strSubject As String
Dim strZIP As String
Set appOutlook = CreateObject("Outlook.Application")
Set mpiFolder =
appOutlook.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
strAQSPath = "c:\test\"
For Each msgMail In mpiFolder.Items
If msgMail.Subject Like "Transfer from AQS" & "*" Then
strSubject = msgMail.Subject
strZIP = Right(strSubject, Len(strSubject) - 18) & ".zip"
msgMail.Attachments.Item(1).SaveAsFile Trim(strAQSPath &
strZIP)
msgMail.Delete
End If
Next
It works perfectly except for one thing - it doesn't find all the
emails. For example, if there are 6 in my Inbox and I run the code it
will find, say, 3. Then if I run it again it will find another 2, and
if I run it again it will find the last one.
Doesn't make any sense to me. Anyone have any clues please?
Thanks
Dave
|
| | 2 answer | Add comment |
|
| Changing Data Types from Code Nick 'The database Guy 16:16:17 |
| | Is it possible to change data types from code.
TIA
|
| | 2 answer | Add comment |
|
| Making sure MS Access scrolls properly in subforms/controls Paddy 15:50:05 |
| | Dear forum,
In my database, I have one instance where a specific collection of 7
forms are expected all at one time, so I tried to build an entry screen
where the entry form for each individual form is present as a subform
in a tabcontrol in one main form, to make it easier for my unit's Data
Entry Officers. Unfortunately, there seems to be a problem with
scrolling that I have been resolve, probably caused either by the
tabcontrol or the subforms, since it does not occur when the form which
was used as a basis for the subform is opened individually. This
problem is that where the individual subform is longer than the screen
allows, the page does not scroll down when fields below the screen are
selected (so that when the field at the bottom of the screen is exited
by pressing tab, or enter, or by an autotab or skip, the next field is
selected without the screen scrolling down). Does anyone know how to
fix this?
Best regards,
Patrick
|
| | 1 answer | Add comment |
|
| Row Number query - PK unknown BillCo 15:23:18 |
| | I've been fishing in the collective knowledge pool of cdma posts gone
by for a solution to this for a while - but no joy. Needless to say,
I've also done my own head in trying to figure this out.
Basically, I need to run a query agains a table that can identify what
row a value is on. This would be easy enough - except it's to be run
againt a number of indeterminate tables or indeterminate primary keys.
It's not that the tables arent indexed, most are - but i'm trying to
build a generic sql building function that can run against any table as
part of a verification process.
Greatly Simplified Example:
Table
Name Age
---- ---
Bob 6
Ann 10
Tim
Sam 5
Query Result:
Record No Field Problem
--------- ----- -------
3 "Age" Null Value
This is greatly simplified from what i'm doing and I have no problems
with the data validation stuff (driven from a seperate validity spec
table) - In reality it involves cross checking table vales etc - but
the problem remains the same:
When you dont know what the primary key is, how do you get a record
number? Given the width of some of these tables and the fact that for
some have no requirement for records to be distinct, grouping is out 
...probably not possible
|
| | 10 answers | Add comment |
|
| Skip a report record? Clownfish 14:42:13 |
| | I have a report that joins 12 external tables, and works great.
However, there is a time when I wish to skip printing a particular
record.
Background: The 12 tables all have the same fields, and are "unioned".
One field is [statusbox] and one is [Office]. The report GROUPS the
list by Office.
The logic I wanted to do in the report detail is:
If me.count>1 AND me.[statusbox] = "NO CHANGES" then SKIP THIS RECORD
(in the above code, me.count is counting the number of records in the
group [Office], and [statusbox] is one field within that group).
Now I know I could set up the querry to ignore records who's
[statusbox] field = "NO CHANGES", but I only want to do this if there
is more than one record in this group. If the sole record in the group
happens to be the one who's [statusbox] = "NO CHANGES" then that is
fine, and this record should be printed.
Example one:
Office statusbox
---------------------------
RSO bla bla bla
RSO bla bla bla bla
RSO NO CHANGES
MGT bla bla
MGT bla bla
POL NO CHANGES
in this example, the report will NOT show the 3rd record, because there
are 3 records in the RSO group, AND this 3rd record has the words "NO
CHANGES". However, the last record would print, becuase it only has 1
record.
Thanks!
Pete
|
| | 5 answers | Add comment |
|
| Help with DateDiff and Conditional Formatting OdAwG 14:15:44 |
| | Hello Again Access GURU's,
Need some help with Conditional Formatting and datediff. What I am trying
to do is the following:
I have three text boxes with dates in them and what I want to do is change
the background color if the date in the text box get within a certain date
range.
Date Range:
21 Days out Backcolor Green
14 Days out Backcolor Yellow
07 Days out Backcoor Red
Example:
textbox1 has date of 08/28/2006
textbox2 has date of 08/21/2006
textbox3 has a date of 08/14/2006
=DateDiff("d",[textbox1],Date()) <-- Gives me 21
=DateDiff("d",[textbox2],Date()) <-- Gives me 14
=DateDiff("d",[textbox3],Date()) <-- Gives me 07
How do i set the backcolor of the textbox to different colors listed above
given the date range
Thanks,
Argus
|
| | 2 answer | Add comment |
|
| Problem with Shell and space in file name Dave G @ K2 13:18:28 |
| | I know this is an old favourite but I can't find a simple answer to a
simple question.
I'm trying to run this:
shell("c:\program files\pkware\pkzipc\pkzipc.exe -ext " & myPath &
myZIPFile & " " & myPath)
I want to unzip a file who's path name is defined by the variable
myPath and who's file name is defined by the variable myZIPFile, and
place the unzipped file in the same folder.
It fails, I presume, because of the space in 'program files'. It also
fails because in some instances there may also be a space in 'myPath',
though I can probably work round this.
So any help please on how to solve this
Thanks
Dave
|
| | 8 answers | Add comment |
|
| Format Condition Confusion Randy Harris 11:07:36 |
| | I thought that Access 2000 supported more than one FormatCondition for a
control. I have two:
Me(CCtl).FormatConditions(0).BackColor = lngTravel
Me(CCtl).FormatConditions(1).ForeColor = RGB(0, 0, 255)
If either condition is true, the appropriate Format is applied to the
control. If both, however, are met, it only applies the first
condition. (Whichever one is first, the BackColor as shown).
Am I wrong thinking that it should support both? Any suggestion is
appreciated.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
|
| | 1 answer | Add comment |
|
| code for splitting a cell (space being the deliminator) Bill_nirl 08:50:23 |
| | Hi all, can anyone help. i have a spreasheet (set up by a looney)
they have in the surname field, the surname then forename. What sort
of code would i need to use to sort this out.
It looks like this:
|Smith Bill |14 Any Street|
|Doe John |19 High Stree|
and so on (there are 7891 of them). Would make life a lot easier to
run it in a module but cant for the life of me work out how to do it
(DUH!!!).
Thanks in advance.
Bill
bill underscore nirl at hotmail.com is my email
bill_nirl@hotmail.com or post it here
|
| | 4 answer | Add comment |
|
| Access to SQL server? Guest 08:10:48 |
| |
ManningFan wrote:> With that many users, switch to SQL Server. Access can handle at most> 20 connections before it gets flaky. It also doesn't handle tables> well once they get to a million records. SQL Server will be much> faster and more stable.>
If it uses Access' native security feature, it's about as secure as> your refrigerator. Send it to me, I'll crack your passwords in about> 20 minutes.
Thanks Manning for your speed reply. Since our database will only have
tens of thousands of records and only 5-10 concurrent users; from your
positng it looks as if it might work without horribly slowing down.
Also, I implemented the security feature so people don't accidently or
out of curiousity sign on as another user. the database is on the
internal network, not accessible to the public.
I'd appreciate a few words on how to simulate the groups and users
without using Access security.
|
| | 4 answer | Add comment |
|
| ACCESS STYLE CHANGES Liam M 07:42:30 |
| | Hey guys,
I was wonder if anyone could suggest any solution to this problem.....I
have now spent roughly about 2-3 days styling my database/ "dressing it
up" HOWEVER, the PC I have been using is NOT the PC that the database
is going to live on. When transfered to its new home...all of the hard
work I have done on the styling is still there BUT its margin's are
buggered...its not fitting the screen...and you are having to scroll to
the right!
Although the screen I designed the Database on is 17 or 18", and the
once I am talking about it 15'4, this should NOT make any difference,
however it is!?!? any suggestions other than resizing it on the smaller
screen? Is there a way to ensure it appears exactly the same from PC to
PC, like you can in web design?
Any suggestion would be much appreciated!
Regards,
Liam
|
| | 3 answer | Add comment |
|
| Compile menu item is grayed out after decompile command rdemyan via AccessMonster.com 07:15:35 |
| | I have a launcher program that creates the shortcut to open my application
using the Shell command. On the form I have a decompile checkbox that I can
conveniently use to decompile my program. I follow the protocol described
by Allen Browne.
Opening my program in decompile mode has always functioned flawlessly.
But I recently converted my program to A2002-2003 format from A2000. Now
when I try to decompile using the launching program code, the "Compile" menu
item is grayed out???!
Any ideas on why this is happening and how do I fix it.
TIA.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200608/1
|
| | 17 answers | Add comment |
|
| Find if a certain field exists in a certain table Dixie 05:27:00 |
| | I am looking for code that will check if a field (fieldname) exists in a
certain table (tablename), then to add it if it does not. Can anyone guide
me in the right direction. I think I can add the field, but I need to know
how to find if the field is already there.
TIA
dixie
|
| | 1 answer | Add comment |
|
| Group By-How do I trap the rolled up fields Guest 05:16:39 |
| | I have 4 fields in my table Field1, Field2, Field3 and Amount. A unique
record in this table is defined by the combination of Field1, Field2
and Field3. I have a query that rolls up by Field1. The other 2 fields
- Field2 and Field3 are not used in the aggregate query - hence are
rolled up. The Amount field is Summed up to display the total.
Question - I want to know what makes up the aggregate record. How do I
find out what records (Field2 and Field3) make up the aggregate record.
Any thoughts are appreciated.
|
| | 1 answer | Add comment |
|
| Print all columns of subform in datasheet view Ghat12@Gmail.Com 03:07:22 |
| | Hi,
I am trying to print a subform containing approx 50 columns in
datasheet view. The printout cannot fit more than 8-10 columns. Does
anyone know of any way to print all columns; i don't care if the
printout spans multiple pages.
FYI, the records displayed in the sub-form are dynamically obtained
based on some ad-hoc querying in the main form. Any report, etc.
generated will have to have the same set of records.
TIA,
Brian
|
| | 2 answer | Add comment |
|
| Newbie Trouble Darrin@Rtc.Coop 03:00:21 |
| | Hello,
I have been asked to keep track of our companies work orders. I have
run a query to show which ones have been completed and which ones are
not finished. What I would like to do is beable to run this query but
add a start and stop date. i.e. July 1, 2006 - July 31,2006. In my
date column in the query design view of access I have tried to use:
between [startdate] and [enddate] when I run the query it will ask me
to enter the dates but it will give me all of the dates, not the time
frame I am looking for.
What am I doing wrong?
Thank you in advance for any and all advice.
|
| | 2 answer | Add comment |
|
|
