And I Cry

[Guest]

Dear All,

For this I have been searching........................

Table 1 - ORDERS

OrderId Customer OrderMonth OrderValue
Ord1 ARTC Jan-06 250,000

Table 2 - MILESTONES

MileStoneId OrderId MileStoneMonth MileStoneValue
Ms1 Ord1 Jan-06 10,000
Ms2 Ord1 Feb-06 15,000
Ms3 Ord1 Mar-06 25,000
Ms4 Ord1 Apr-06 30,000

Now I want a query that could list all the orders with their customer, the
ordermonth, the order value + milestonemonth & mile stone value. But when i
design a query it repeats the OrdId, Customer, OrdMonth & OrdValue 4 times,
each for the MileStones. Is it a cross product? Should I sue
Distinct/Distinctrow? Shoul I union the Tables. What is required is to give a
piviottable type report (maybe a croostab query) in which it can been seen
that whict customers have which order, its value & when it was received AND
ALSO the milestones for thoes order.

The Order Table is joined as one to many with the Milestone table.

Thnaks in advance.

Regards

 

[Chris2]

Dear All,

For this I have been searching........................

Table 1 - ORDERS

OrderId Customer OrderMonth OrderValue
Ord1 ARTC Jan-06 250,000

Table 2 - MILESTONES

MileStoneId OrderId MileStoneMonth MileStoneValue
Ms1 Ord1 Jan-06 10,000
Ms2 Ord1 Feb-06 15,000
Ms3 Ord1 Mar-06 25,000
Ms4 Ord1 Apr-06 30,000

Now I want a query that could list all the orders with their customer,
the ordermonth, the order value + milestonemonth & mile stone

value.

JAA149,

Ok, I understand that part directly above.

But when i design a query it repeats the OrdId, Customer, OrdMonth
& OrdValue 4 times, each for the MileStones.

It appears to me that this is what you were asking for in the
section above.

"list all the orders with their customer, the ordermonth, the order
value + milestonemonth & mile stone value."

Is it a cross product?

I don't think so (but without seeing your SQL, it's hard to know).

If you are matching ORDERS and MILESTONES on OrderID in an INNER
JOIN, then of course you get 4 rows for Ord1 values based on the
sample data above.

Should I sue
Distinct/Distinctrow?

That will not help. Various columns in MILESTONES are already
making each row unique.

Shoul I union the Tables.

Probably not.

What is required is to give a
piviottable type report (maybe a croostab query) in which it can been seen
that whict customers have which order, its value & when it was received AND
ALSO the milestones for thoes order.

A Crosstab? I guess you could build one, but it wouldn't fulfill
the requirements given above.

The Order Table is joined as one to many with the Milestone table.

Tables are only JOINed in queries, and no JOIN is "one to many".

MS Access "relationships" can be one to many, but those aren't query
JOINs.

Thnaks in advance.

Regards

Can you please provide a more detailed specification of what you
want?

Perhaps you could write out how you want your data to appear (based
on the sample data given above) after the query is done.


Sincerely,

Chris O.

 

[Guest]

Dear Chris2,

----------------------------------------------------------
Table 1 - ORDERS
----------------------------------------------------------
OrderId Customer OrderMonth OrderValue
Ord1 ARTC Jan-06 250,000
----------------------------------------------------------

OrderId is the Primary Key.

-----------------------------------------------------------------------
Table 2 - MILESTONES
-----------------------------------------------------------------------
MileStoneId OrderId MileStoneMonth MileStoneValue
Ms1 Ord1 Jan-06 10,000
Ms2 Ord1 Feb-06 15,000
Ms3 Ord1 Mar-06 25,000
Ms4 Ord1 Apr-06 30,000
-----------------------------------------------------------------------
MileStoneId is the Primary Key.

OrderId is the Foreign Key. The Field OrderId is a lookup column in the
Table 2 - MILESTONES from the Table 1 - ORDERS.

RowSource property of the Field OrderId in the Table 2 - ORDERS is
"SELECT ORDERS.OrderId FROM ORDERS ORDER BY [OrderId];"

In the relationship window
_____________ _________________
|ORDERS | |MILESTONES |
------------------
-----------------------
|OrderId |--------------\ |MileStoneId |
|Customer | \-------------|OrderId |
|OrderMonth | |MileStoneMonth |
|OrderValue | |MileStoneValue |
_____________ ________________

Clicking the join line Brings Edit Relationship Which Says
1 - "Relationship Type is : One-To-Many"
2 - Clicking Join Type Says "1: Only include records where the joind field
from both tables are equal"

So there is one to many relationship between the entity ORDERS & MILESTONES
as 1 Order can have many Milestones. So Far So Good. Now We Go To Query

REQUIREMENT:-

What is needed is a report which we can call Orders Revenue Forecasting
report which will be based on a query which we can call Orders Revenue
Forecasting query which shows the spread of the invoices to be raised over
the months and also details of the order such as the OrdeId, Customer,
OrderMonth & OrderValue.

Now the Query I made is

"SELECT ORDERS.OrderId, ORDERS.Customer, ORDERS.OrderMonth,
ORDERS.OrderValue, MILESTONES.MileStoneId, MILESTONES.MileStoneMonth,
MILESTONES.MileStoneValue
FROM ORDERS INNER JOIN MILESTONES ON ORDERS.OrderId = MILESTONES.OrderId;"

Which Shows (Excuse the filed names which I changed because there is no
space here)

__________________________________________________________
|OrdId|Cust |OrdMon|OrdVal |MileStId|MileStMon|MileStoneValue|
|Ord1 |ARTC |Jan-06 |250,000 |Ms1 |Jan-06 |10,000 |
|Ord1 |ARTC |Jan-06 |250,000 |Ms2 |Feb-06 |15,000 |
|Ord1 |ARTC |Jan-06 |250,000 |Ms3 |Mar-06 |25,000 |
|Ord1 |ARTC |Jan-06 |250,000 |Ms4 |Apr-06 |30,000 |
___________________________________________________________

In this query Ord1, ARTC, Jan-06 & 250,000 is repeated. The query tells us
the total orders are 1,000,000 (250,000 X 4) which is wrong. When this query
is taken for analysis in Excel and is used to datasource for pivottable, the
pivottable shows the same crosstabe type of report BUT wit the same mistake
above (inflated Order).

Do I have to make a full outer join? I know Access does not have the option
to do it but there is a workaround it.

I am realy confused about it and to the point of giving up. I mean I know
inner, outer, left outer, right outer, full outer & self joins. I understand
what they mean. I understand table/entity relationships. I under stand data
modelling (Conceptual ). I know ERD diagrams. Love RDBMS. Adore SQL. Familiar
with UML.

But when it comes down to simple thing like the above every thing goes
haywire. I think what has happened is that I have too much in my head. So
actualy have nothing.

Regards

 

[Chris2]

Dear Chris2,

<snip>

JAA149,

The stuff in this section was great.

REQUIREMENT:-

What is needed is a report which we can call Orders Revenue Forecasting
report which will be based on a query which we can call Orders Revenue
Forecasting query which shows the spread of the invoices to be raised over
the months and also details of the order such as the OrdeId, Customer,
OrderMonth & OrderValue.

Please understand that I don't know what your "Orders Revenue
Forecasting report" will look like. I also don't know what you mean
by "shows the spread of the invoices to be raised over the months
and also details of the order such as the OrdeId, Customer,
OrderMonth & OrderValue."

No amount of narrative description is likely to help make this clear
(unless you have a full Systems Analyst quality programming
specification available).

The best solution is to write out the column headers that you want,
and to write out the data that you want to appear under them.
Believe me, showing your desired results is the easiest thing for
someone else to work toward.

Now the Query I made is

"SELECT ORDERS.OrderId, ORDERS.Customer, ORDERS.OrderMonth,
ORDERS.OrderValue, MILESTONES.MileStoneId, MILESTONES.MileStoneMonth,
MILESTONES.MileStoneValue
FROM ORDERS INNER JOIN MILESTONES ON ORDERS.OrderId = MILESTONES.OrderId;"

Which Shows (Excuse the filed names which I changed because there is no
space here)

This SQL makes a great deal clear about what is currently going on.

__________________________________________________________
|OrdId|Cust |OrdMon|OrdVal |MileStId|MileStMon|MileStoneValue|
|Ord1 |ARTC |Jan-06 |250,000 |Ms1 |Jan-06 |10,000 |
|Ord1 |ARTC |Jan-06 |250,000 |Ms2 |Feb-06 |15,000 |
|Ord1 |ARTC |Jan-06 |250,000 |Ms3 |Mar-06 |25,000 |
|Ord1 |ARTC |Jan-06 |250,000 |Ms4 |Apr-06 |30,000 |
___________________________________________________________

Each row of ORDERS is being JOINED to each row of MILESTONES for
every row where OrderID match. Of course "250,000" will continue to
repeat once for the product of the number of times the "Ord1" value
is found in both tables (in the sample data, there is one "Ord1"
value in ORDERS, and four "Ord1 values in MILESTONES, so we get 4
rows; 1 x 4 = 4).

Basically, Customer and OrderValue are getting repeated because they
are being called on to repeat.

One of the reasons I have been confused is because you want all the
MileStoneValues. To get them, you need one row for each of them.
In a table you are INNER JOINing to another (where the relationship
is one to many), you are going to get repeating values from the
"one" table. Here is another place where the gap in my understading
is coming out. I don't know what your desired results look like.

In this query Ord1, ARTC, Jan-06 & 250,000 is repeated. The query tells us
the total orders are 1,000,000 (250,000 X 4) which is wrong. When this query
is taken for analysis in Excel and is used to datasource for pivottable, the
pivottable shows the same crosstabe type of report BUT wit the same mistake
above (inflated Order).

Based on the above narrative only, and without seeing all the MS
Access and MS Excel code involved, I can't be sure (and it would be
impractical to call all that information into this discussion), but
I believe this to be caused by running your crosstab on two
different sets of information. The Crosstab in MS Access is running
on the same source data as the query above, but the crosstab in MS
Excel is running on the data coming out of the query above (which is
different that the data in the source tables).

Do I have to make a full outer join? I know Access does not have the option
to do it but there is a workaround it.

A very clunky work-around, if I may say so. A FULL OUTER JOIN
probably isn't applicable here.


<snip>

Again, show me a "desired results" list, and I'll try for a
solution.


Sincerely,

Chris O.

 

[Guest]

Dear Chris2,

The desired result in this example where we only have one customer with one
order & four payments is simply this.
______________________________________________________
|OrdId|Cust |OrdMon|OrdVal |Jan-06|Feb-06|Mar-06|Apr-06|
|Ord1 |ARTC |Jan-06 |250,000 |10,000|15,000|25,000 |30,000|
______________________________________________________

This is how the "Orders Revenue Forecasting report" will look like. And this
is what I meant by "shows the spread of the invoices to be raised over the
months and also details of the order such as the OrdeId, Customer,OrderMonth
& OrderValue.". This report/query/table does exactly that. It shows that for
particular order, who is the customer and in which months how much invoices
we are going to raise.

The reason I asked about full outer join is that it will "combine related
but indentical data" (Am I right?) unlike the SQl specific query (UNION)
where the data types & table design needs to be the same. Surely the data
types & table design for the Orders table and Mile stone table can never be
the same as they are two different entities.(however related).

This is what I am trying to do for which I commited my self to the study of
databases, RDBMS, SQL, Set Theory, VBA in Excel & Access, Access, MS Query,
Advance Excel, PIVIOTTABLE, OLAP, Cubes, Data Modelling and whole lot of
other stuff. But i get stuck on this part where I make tables, define
relationships/joins & run queries. But the result is absurd.

Please see
"http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html"
where Excel is being used to develop RDBMS.

Regards

Jawad

 

[Chris2]

Dear Chris2,

The desired result in this example where we only have one customer with one
order & four payments is simply this.
______________________________________________________
|OrdId|Cust |OrdMon|OrdVal |Jan-06|Feb-06|Mar-06|Apr-06|
|Ord1 |ARTC |Jan-06 |250,000 |10,000|15,000|25,000 |30,000|
______________________________________________________

This is how the "Orders Revenue Forecasting report" will look like. And this
is what I meant by "shows the spread of the invoices to be raised over the
months and also details of the order such as the OrdeId, Customer,OrderMonth
& OrderValue.". This report/query/table does exactly that. It shows that for
particular order, who is the customer and in which months how much invoices
we are going to raise.

The reason I asked about full outer join is that it will "combine related
but indentical data" (Am I right?) unlike the SQl specific query (UNION)
where the data types & table design needs to be the same. Surely the data
types & table design for the Orders table and Mile stone table can never be
the same as they are two different entities.(however related).

This is what I am trying to do for which I commited my self to the study of
databases, RDBMS, SQL, Set Theory, VBA in Excel & Access, Access, MS Query,
Advance Excel, PIVIOTTABLE, OLAP, Cubes, Data Modelling and whole lot of
other stuff. But i get stuck on this part where I make tables, define
relationships/joins & run queries. But the result is absurd.

Please see
"http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.h
tml"
where Excel is being used to develop RDBMS.

Regards

Jawad

Jawad,

Tables:

Note: Please forgive the dates appended to the table names.

CREATE TABLE ORDERS_20051214_1
(OrderID CHAR(4)
,Customer CHAR(4)
,OrderMonth DATETIME
,OrderValue CURRENCY
,CONSTRAINT pk_ORDERS_20051214_1
PRIMARY KEY (OrderID)
)

CREATE TABLE MILESTONES_20051214_1
(MileStoneID CHAR(3)
,OrderID CHAR(4)
,MileStoneMonth DATETIME
,MileStoneValue CURRENCY
,CONSTRAINT pk_MILESTONES_20051214_1
PRIMARY KEY (MileStoneID)
,CONSTRAINT fk_MILESTONES_20051214_1_ORDERS_20051214_1_OrderID
FOREIGN KEY (OrderID)
REFERENCES ORDERS_20051214_1 (OrderID)
)


Sample Data:

ORDERS:
OrderId Customer OrderMonth OrderValue
Ord1, ARTC, 01/01/2006, 250000

MILESTONES:
MileStoneId OrderId MileStoneMonth MileStoneValue
Ms1, Ord1, Jan-06, 10000
Ms2, Ord1, Feb-06, 15000
Ms3, Ord1, Mar-06, 25000
Ms4, Ord1, Apr-06, 30000


Query:

TRANSFORM SUM(M1.MileStoneValue)
SELECT O1.OrderID
,O1.Customer
,O1.OrderMonth
,O1.OrderValue
FROM ORDERS_20051214_1 AS O1
INNER JOIN
MILESTONES_20051214_1 AS M1
ON O1.OrderID = M1.OrderID
GROUP BY O1.OrderID
,O1.Customer
,O1.OrderMonth
,O1.OrderValue
PIVOT FORMAT(M1.MileStoneMonth, "mmm-dd")

Currently, the month columns are out of order. You can call them in
any order you like in a report. (However, I am going to work on an
IN expression to fix that. I've got a big one written out right
now, but it isn't working on missing operator error and I can't spot
what I've left out. Also, I need to leave, so I'll get back to it
later.

Otherwise, the results of the above query are correct:

OrderID, Customer, OrderMonth, OrderValue, Apr-06, Feb-06, Jan-06,
Mar-06
Ord1, ARTC, Jan-06, 250,000, 30000, 15000, 10000,
25000


Sincerely,

Chris O.

 

[Chris2]

Query:

TRANSFORM SUM(M1.MileStoneValue)
SELECT O1.OrderID
,O1.Customer
,O1.OrderMonth
,O1.OrderValue
FROM ORDERS_20051214_1 AS O1
INNER JOIN
MILESTONES_20051214_1 AS M1
ON O1.OrderID = M1.OrderID
GROUP BY O1.OrderID
,O1.Customer
,O1.OrderMonth
,O1.OrderValue
PIVOT FORMAT(M1.MileStoneMonth, "mmm-dd")

That should be:
"mmm-yy"

Still working on the IN clause. Access is not cooperating at the
moment.


Sincerely,

Chris O.

 

[Chris2]

That should be:
"mmm-yy"

Still working on the IN clause. Access is not cooperating at the
moment.


Sincerely,

Chris O.

JAA149,

If you can accept having a "1/06" format instead of "Jan-06", then
we're good to go:

PIVOT DatePart("m",M1.MileStoneMonth) & "/" &
FORMAT(M1.MileStoneMonth, "yy")

That will order the months correctly.

However, including the year in the column headers will make the
column names change over time, ruining any report based on it,
eventually. Manual intervention at the development level will be a
constant maintenance need. This is probably unacceptable.


Putting the year into a separate column would be entirely superior,
and then numbering the months by DatePart() in the query will order
them correctly (although a report will still be able to call on
"Apr", "Feb", "Jan", "Mar" in the correct order with a little
tweaking; using numbered month output means that the report wizard
will generate the columns in the correct order to begin with).


Query:

TRANSFORM SUM(M1.MileStoneValue)
SELECT O1.OrderID
,O1.Customer
,O1.OrderMonth
,O1.OrderValue
,Year(M1.MileStoneMonth) As MileStoneYear
FROM ORDERS_20051214_1 AS O1
INNER JOIN
MILESTONES_20051214_1 AS M1
ON O1.OrderID = M1.OrderID
GROUP BY O1.OrderID
,O1.Customer
,O1.OrderMonth
,O1.OrderValue
,Year(M1.MileStoneMonth)
PIVOT DatePart("m", M1.MileStoneMonth)

Results (with short column names):

OrdID Cust OrdMonth OrdVal MiStYr 1 2 3 4
Ord1, ARTC, Jan-06, 250,000, 2006, 10000, 15000, 15000, 25000


My thanks to Duane Hookum and his post in another thread, that
essentially led me to this idea (after I slept on it). It makes a
lot of sense to keep the month column names the same (fixed month
numbers or names), and to include the year in a separate output
column, at least for the purposes of a report.


Sincerely,

Chris O.