Hello. Thanks for your attention.
My description is as follow:
I have many tables: Invoices, Invoices Details(where I stored the
details of the invoice like ProducId,Name, Price, Quantity,etc). Each
product belongs to one Category (or Group of Products) named in another
table. Every category has a sales plan for every month, stored in Plans
table.
My question is as follow:
I need to do a querie with the following information:
In the rows: Caterory of products
In the columns: Sales Plan, Sales
Additional information: Current Month (I know to do this)
My problem is I need to find a way to show the plan of every categories
with or without sales in the current month.
For example, I used a Selection Query with: Plans, Sales,etc, but it
only shows the plan of the categories sold. I need it shows all
categories
May I use another kind of querie?
thanks again
Andy Eduardo
Andy,
If I read your narrative correctly, your tables are:
Categories
CategoryID INTEGER PK
CategoryName TEXT(48)
Plans
SalesPlan INTEGER PK
PlanName TEXT(48)
Products
ProductID INTEGER PK
CategoryID INTEGER FK to Categories (CategoryID)
ProductPlans
ProductPlanID INTEGER PK
ProductID INTEGER FK to Products (ProductID)
SalesPlan INTEGER FK to Plans
StartDate DATETIME
EndDate DATETIME
Invoices
InvoiceID INTEGER PK
InvoicesDetails
InvoicesDetailID INTEGER PK
The above is my best guess. I wasn't able to deterine in what table the
column sales appeared, or what it's exact purpose was.
How close was I? (Once I know more about your table structures, etc.
(see below), I can try and work on an answer to your question.)
The following contains information on how you can improve your chances of
getting your question answered:
-------------------------------
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 a way you believe others will understand.
-------------------------------
Table Structures/Description:
Post a description of your table structures.
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 "lightning bolt and stacked lines" 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
ColThree TEXT(10)
MyTableTwo
MyTableTwoID AUTOINCREMENT PK\
MyTableOneID INTEGER PK/-- Composite Primary Key
FK -- MyTableOne MyTableOneID
ColThree INTEGER
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 NOT NULL
,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 (or completely
different names, for whatever reason) may save space and prevent line-
wraps, but it can be severely confusing. If the chart gets too "wide",
make two (or more) charts if you have to, as noted above in Table
Structures.
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 normal maximum.
(Post only what is necessary, and no more.)
Note: Please try and use real data when possible. However, 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 found in the Sample Data section.)
-------------------------------
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 found in the Sample Data section.)
-------------------------------
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.
-------------------------------
I hope that the above can be of assistance in helping you receive an
answer to your various MS Access questions.
Sincerely,
Chris O.
