Re: And I Cry

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"JAA149" <JAA149@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EB21D667-9822-4243-879E-74BACDEE4E4C@xxxxxxxxxxxxxxxx
> 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.


.

Relevant Pages

  • Re: dynamic crosstab A2002 - Almost there
    ... I would set up the crosstab as ... Query 1: QryMonthlyIncidentsBase_Crosstab ... Base your report on this query. ... >>> Dim ReportLabelAs String ...
    (microsoft.public.access.queries)
  • Re: SELECT Statement in a Report
    ... If you are still looking for a method to display the results of a crosstab ... probably be handled in the reports source query, ... calculated controls on the report. ... What do I use for the select statement in the Control Source ...
    (microsoft.public.access.reports)
  • Re: Convert week # into date of the first day of week in SQL
    ... I understand setting paramets as you have outlined. ... > query is run. ... I then created a crosstab query to show in the Row ... the problem is when I create a report. ...
    (microsoft.public.access.reports)
  • Re: Variable # of columns being output by a Crosstab query
    ... this query doesn't take parameters, it pulls its data from another ... going to want in the crosstab. ... means that the report's query and the report itself need to be capable ... Then loop through the recordset and change the Caption of the Labels ...
    (comp.databases.ms-access)
  • Re: Populating a field
    ... If you already have the data in 1 location, you never need to repeat it. ... A query joining the 2 ... I shuld have mentioned the report. ... Sub cboWhatever_AfterUpdate ...
    (microsoft.public.access.forms)