Okay, for this example, I'm going to assume that all your data is in this form. I'm not worrying about how it got that way. 
Code:
tblProjection
ProjKey (primary Key)
CompanyKey (foreign key to tblCompany)
RouteKey (foreign key to tblRoute - POLPOD table)
ProjMadeDate
(various information about the projection and who made it)
tblProjByWeek
PBWKey (primary key)
ProjKey (foreign Key to projection)
PBWProjDate
PBWProjContainers
PBWprojProduct
tblLoadByWeek
LBWKey (primary key)
CompanyKey (foreign key to company table)
RouteKey (foreign key to POLPOD table)
LoadDate
LoadContainers
tblCompany
CompanyKey (primary key)
CompanyName
tblRoute (POLPOD table)
RouteID (primary key)
POLPort (foreign key to tblPort for POL)
PODPort (foreign key to tblPort for POD)
tblPort
PortKey (primary Key)
PortName
Now, I'm going to assume, just for simplicity, that your Loads and projections are all stored on a weekly basis, and LoadDate and PBWProjDate are always going to be the Monday of your accounting week. The code would be slightly different if the dates didn't automatically align.
First, here's the query (call it qryActLoad) to get the actual results for all companies for all weeks:
Code:
SELECT
LBW.LoadDate AS EventDate,
"A" AS EventType,
LBW.CompanyKey AS CompanyKey,
LBW.RouteKey AS RouteKey,
LBW.LoadDate AS LoadDate,
LBW.LoadContainers AS LoadContainers
FROM tblLoadByWeek AS LBW;
Second, here's the query (call it qryProjLoad) to get all projected results for all companies for all weeks:
Code:
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 tblProjection AS PROJ INNER JOIN tblProjByWeek AS PBW
ON PROJ.ProjKey = PBW.ProjKey
GROUP BY EventDate, CompanyKey, RouteKey, LoadDate
Now, you'll notice that the structure of both those tables is exactly the same, and that if you smashed the two of those queries together, you'd have the total loaded for each week in the "A" record, with the week's date as the EventDate, and the prior projections of the total loaded for each week would be in "P" records, with the date the projection was MADE in the event date.
So, now we can make a UNION query to get all the information for one Combination of company, Route, and range of LoadDates like this:
Code:
SELECT * FROM qryActLoad
WHERE CompanyKey = 27
AND RouteKey = 218
AND LoadDate BETWEEN #05/06/2013# AND #06/03/2013#
UNION
SELECT * FROM qryProjLoad
WHERE CompanyKey = 27
AND RouteKey = 218
AND LoadDate BETWEEN #05/06/2013# AND #06/03/2013#
Later, we'll have to bind those queries to the Company table to get the company name, and to the route and port tables to get the port names, but I wanted to make the example as easy as possible so you see what is happening.
The key to being able to use a database is to understand what entity each table represents, so that when you join them together, you are enforcing the true relationship between the entities, rather than doing things accidentally or by rote.
I'll pause now for questions, because I know you'll have them.
Windows 7 64bit 
Access 2010 64bit
...
Reply With Quote
trying to link the 2 tables
). 
>
