Relate two calculated fields

[fedebrin]

ok, I am getting an error on the query qryProjLoad... I used the tables and field names exactly as you advised



I am getting this error: "You tried to execute a query that does not include the specified expression 'EventDate' as part of an aggregate function"

Additionally, if we have this format, it would be better if the sales can enter the projection as a week number rather than a date. The vessels normally call once a week therefore when a projection is made, they can enter week 1, 2, 3, 4 etc

[Dal Jeanis]

Please post the SQL. Usually, that error means that you have a query with a GROUP BY clause, and you're selecting a detail item.

What the Sales group enters, and what you store on the database, are two separate things. Probably, you will provide them a form that has both the week numbers and the dates, but you will store on the database by date, because anything else makes it overcomplex to analyze your data.

Repeat after me. I will make it easy to analyze my data. I will make it easy to analyze my data. I will make it easy to analyze my data.

[fedebrin]

ok, noted, this is the SQL code I pasted on the Query, the same one you provided:

SELECT PROJ.ProjMadeDate AS EventDate, "P" AS EventType, PROJ.CompanyKey AS CompanyKey, PROJ.RouteKey AS RouteKey, PBW.PBWProjDate AS LoadDate, SUM(PBWProjContainers) AS LoadContainers
FROM Projection AS PROJ INNER JOIN ProjByWeek AS PBW ON PROJ.ProjKey = PBW.ProjKey
GROUP BY EventDate, CompanyKey, RouteKey, LoadDate;

[Dal Jeanis]

Probably, in a summary query, the aliases to the group fields in the SELECT statement aren't available at the time the GROUP BY is calculated. It will probably kill that error if you change the last line to

GROUP BY PROJ.ProjMadeDate, PROJ.CompanyKey, PROJ.RouteKey, PBW.PBWProjDate;

[fedebrin]

I get this error

[Dal Jeanis]

Blurg?

1) check that PBWProjContainers is numeric field.
2) check that PROJ.ProjKey and PBW.ProjKey are defined as the same type.
3) check that you have spaces in all required places (no two words are run together)

That's the only two items in that query that could have type mismatches.

[ssanfu]

@Dal,

RE: My post #13 / Your post #14.

You are right. Not very clear. I discovered what went wrong: PBKAC!!!

Yes, I did mean "...because it[repeating fields in a record] breaks normalization rules."


I will strive to be less obtuse in upcoming chronological time periods .......

[Dal Jeanis]

The word "it" in English is a prime candidate for "unspecified referential index" issues. Most of the time, I kill "it" when rewriting for clarity. Sometimes, like the last sentence, I just put quotes around it.

Hmmm. When we say in English "It is raining" or "It is hot" or "it is late", what, precisely, is raining or hot or late?