Susan L said:
Chris: I hope you did not have to spend hours on this. But indeed, it does
work beautifully, after a couple of glitches adding my other fields. It's
just wonderful. Monday am I am going to try writing the SQL to pull Deletes
from one of my other tables. If I'm not presuming too much (and I feel as if
I already have...), could I post to this thread to ask you to review the code
if I can't get it to work?
If not, I'll understand. In the meantime, what you've done to assist me has
really been invaluable. I know I couldn't have learned how to structure this
SQL in the timeframe I have. Thanks for lending your expertise.
Susan L.,
I will be watching.
Time spent?
From the second set of information, let's see.
45 minutes, 10 minutes, 20 minutes.
When I wrote the short note requesting the sample data for the
second round, I hadn't yet realized that first chart presented in
the second round was part sample data, and part desired results, and
that the AudTypes had changed. Basically, I was running the
debugged query, and I wasn't getting the same rows back as I had
originally in my first answer. That threw me for a loop, and I
could not determine what was going on until I did a line by line
check of the new sample data and found that the AudTypes had
changed.
The following web page can be a great help, if you read and
understand it thoroughly.
Although meant for an SQL Server newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.
Yet More Information:
-------------------------------
Formatting:
Please use a monospace font (Courier New, etc.) when writing out
your examples (all descriptions, charts, SQL, etc.).
-------------------------------
Process Description:
Please only include the shortest possible narrative of what is going
on with the query. (Include all that is necessary, and nothing
more.)
When parts of your query make calculations, show the exact code or
nearest readable plain-text math formula you can create.
When you are done with this section, re-read it several times before
posting to assure yourself that you are accurately describing the
situation in way you believe others will understand.
-------------------------------
Table Structures/Description:
Post a description of your table structures . . . (The below are
only descriptions, and are not exact copies of DDL syntax!)
Although it can be a source of information, please do not copy and
paste information directly from MS Access' Documenter. It is
virtually unreadable. Please distill down and legibly format only
the relevant table information.
If reading the information in MS Access' Documenter is too
intimidating (I know what its output says, myself, and I still
dislike going over its output listings), open your table in Design
View, view the column names and data types in it, and then type out
the column names and data types *that are necessary* (do not include
columns that are not absolutely necessary for the query). Use the
Index dialog box (you can get at it by clicking on the "key" icon on
the toolbar) to locate information on primary and foreign keys and
other indexes and type out that information, as well.
Note: For table descriptions (or DDL) lining up the column names,
data type names, and key/index information in neat columns is quite
helpful.
Note: If you know how to write DDL SQL (CREATE TABLE), please post
that (including constraints) instead of text descriptions. (Please
post only the portion of the DDL that is relevant.)
Example (text description):
MyTableOne
MyTableOneID AUTOINCREMENT PK
ColTwo INTEGER NOT NULL DEFAULT 0
ColThree TEXT(10)
MyTableTwo
MyTableTwoID AUTOINCREMENT PK\
MyTableOneID INTEGER PK/-- Composite Primary Key
ColThree INTEGER FK -- MyTableOne ColOne
ColFour DATETIME
ColFive CURRENCY
ColSix BIT
ColSeven TEXT(1)
ColEight TEXT(1)
etc., etc., etc.
Example (DDL SQL/CREATE TABLE):
CREATE TABLE MyTableOne
(MyTableOneID AUTOINCREMENT
,ColTwo INTEGER
,ColThree TEXT(10)
,CONSTRAINT pk_MyTableOne
PRIMARY KEY (MyTableOneID)
)
CREATE TABLE MyTableTwo
(MyTableTwoID AUTOINCREMENT
,MyTableOneID INTEGER
,ColThree INTEGER
,ColFour DATETIME
,ColFive CURRENCY
,ColSix BIT
,ColSeven TEXT(1)
,ColEight TEXT(1)
,CONSTRAINT pk_MyTableTwo
PRIMARY KEY (MyTableTwoID)
,CONSTRAINT fk_MyTableTwo_MyTableOne_MyTableOneID
FOREIGN KEY (MyTableOneID)
REFERENCES MyTableOne (MyTableOneID)
)
The Primary Key and Foreign Key notes (or constraints in the DDL
SQL) are *critical*.
-------------------------------
Sample Data (using comma delimited lists):
Note: If your sample data is "wide" across the screen, and you can't
trim out any columns because they are needed, make *two* (or more)
charts, and then clearly note that the second chart is the
continuation of the first chart for the same table. It is far
easier to convert a comma delimited chart into a table in MS Word or
import it directly into MS Excel (where the data can be copied and
pasted into a new table in MS Access) or even MS Access than it is
to manually undo the line-break on *every* row of a line-wrapped
chart (in fact, manually undoing the line-breaks caused by newsgroup
posting is a huge pain in the neck).
Note: In a comma delimited list, it is not absolutely necessary
(although it is nice) to have the data in the columns lined straight
up and down, like I have in my examples below. When the data is
finally imported into MS Access, a quick glance at the table in
datasheet view will show things lined up straight. It is not
necessary to expend extra effort on your chart here. (The right
data does have to be in the right position of each row of the chart,
of course.)
Note: Use the real table and column names whenever possible. Use
invented table names and column names (like I use below in my
example) only when you absolutely have to.
Note: When naming the columns on this chart, use the same column
names as is the table structures above. Using shortened names may
save space and prevent line-wraps, but it can be severely confusing.
Make two (or more) charts if you have to, as noted above.
Note: Please include just enough rows of sample data so that
sufficient tests of the various possibilities ("test cases") can be
made.
Note: Please do not attempt to post endless rows of data. 3-5 rows
are probably the minimum, and 10-20 row are probably the maximum.
(Post only what is necessary, and no more.)
Note: Please try and use real data when possible. Real people's
personal information, or private information (banking, proprietary,
etc.), should never be posted. When you have information that
cannot be posted, you will have to invent test data that can produce
results similar to what the real data would produce.
MyTableOne
MyTableOneID, ColTwo, ColThree
1, 2, a
3, 4, b
5, 6, c
MyTableTwo (Part One)
MyTableTwoID, MyTableOneID, ColThree, ColFour, ColFive
1, 5, 1, 01/01/06, 1.01
MyTableTwo (Part Two)
ColSix, ColSeven, ColEight
-1 g, h
-------------------------------
Desired Results
.. . . <whatever it is you want your query to produce; "the right
stuff", if you will forgive the pun>
(Same chart style as for sample data.)
-------------------------------
Query:
Your SQL query code attempts to date. (If "SQL code" throws you for
a loop, open your Query in Design View, and then use the menus, View
SQL View, to switch to a window that will show the SQL code. Copy
and paste that into your new post to the newsgroup.)
Note: There is a huge temptation to merely copy and past the SQL
code. Usually, this is completely unreadable, and whoever reads it
must re-align the code in order to make heads or tails of it (yes,
there are a few out there who can read endless unbroken streams of
code packed together, but I am not one of them). If you know how,
spend some time straightening out and aligning the SQL before
posting it.
Note: In some situations, of course, you will have no query or SQL
code at all.
-------------------------------
Current Results:
.. . . <the incorrect results the current query(s) is producing>
(Same chart style as for sample data.)
-------------------------------
Lots Of Work:
Does all this sound like a lot of work?
Remember, whatever work you haven't done will have to be done by
whoever tries to answer your question.
Any information that is not included may have to be asked for,
necessitating additional posts (sometimes many) before someone can
begin answering your question.
Time spent doing these things is time spent not answering your
question.
Sincerely,
Chris O.
