How to consolidate and return extensions

[gmaxey]

The answer to this question may not involve VBA but I'm hoping someone can steer me in the right direction. For simplicity in explaining the issue, I am going to use a simplified example.

I have a table of Projects "tblProjects". With "ProjectID" as the primary key and fields "Base_Due_Date" and "Total_Days_Extended" . During the course of the project execution, the Project Manager may request one or more (probably not more than three but just out of interest, lets say unlimited). When an extension is granted, the due date is revised (pushed forward by the approved number of days).

So in a query I can have Due_Date = DateAdd("d, "Base_Due_Date", "Total_Days_Extended")

My table might look like this

ProjID Base_Due_Date Total_Days_Extended
1 1/1/2024 ??
2 2/1/2024 ??
3 3/1/2024 ??

My problem (question) is how to go about resolving "Total_Days_Extended).

I am just one step above being a complete novice but I believe the approach would be to have another table of extensions tblExtensions that has a relationship with tblProjects. Each approved extension is entered into that table. So it might look like this:

ExtID, ProjectID NumDay
1 1 10
2 2 15
3 1 30

... and with the proper relationship have a result in my tblProjects like this:

ProjID Base_Due_Date Total_Days_Extended
1 1/1/2024 40
2 2/1/2024 15
3 3/1/2024 0

Then if an extension of ProjID is approved:
ExtID, ProjectID NumDay
1 1 10
2 2 15
3 1 30
4 3 20

I get:

ProjID Base_Due_Date Total_Days_Extended
1 1/1/2024 40
2 2/1/2024 15
3 3/1/2024 20

If this is even possible, how would one go about setting up the relationship and between the tables and how would one define the "Total_Days_Extended" field expression to sum or add up all the extensions related to that record.

Thank you!!

[xps350]

First of all: you should not STORE Total_Days_Extended in the project table. It is a derived value, that you should calculate and SHOW whenever needed. For that you use a query like:

SELECT tblProjects.ProjID, tblProjects.Base_Due_Date, Sum(Nz(tblExtensions.NumDay)) AS Total_Days_Extended
FROM tblProjects LEFT JOIN tblExtensions ON tblProjects.ProjID = tblExtensions.ProjID
GROUP BY tblProjects.ProjID;

[Aussiebear]

hmmm.... not bad advice at all Peter.

[gmaxey]

Peter,

Thank you very much. So I created a new query and pasted your SQL string into the SQL view. When I first ran it, I got an error
Your query does not include the specified expression "Base_Due_Date" as part of an aggregate function.

Having no idea what that meant or what exactly to do to fix it, I started fiddling around. First I removed "tblProjects.Base_Due_Date" from the first line. When I ran the query I got no error and the query returned the ProjID and the sum of the extensions!!! I then went to design view and added "Base_Due_Date" and this time when I ran the query again I got exactly what I was looking for:

ProjID Base_Due_Date Total_Days_Extended
1 1/1/2024 40
2 2/1/2024 15
3 3/1/2024 20

When I went back and looked at the resulting SQL I noticed tblProjects.Base_Due_Date was added to the GROUP BY line.

SELECT tblProjects.pkProjID, tblProjects.Base_Due_Date, Sum(Nz(tblExtensions.NumDays)) AS Total_Days_Extended
FROM tblProjects LEFT JOIN tblExtensions ON tblProjects.pkProjID = tblExtensions.fkProjID
GROUP BY tblProjects.pkProjID, tblProjects.Base_Due_Date;

Why is the GROUP BY line necessary? What does Nz mean and what does it do?

Again thank you. Hopefully I will be able to apply what you have shown me here to a practical project that is much more complicated.

[xps350]

Why is the GROUP BY line necessary? What does Nz mean and what does it do?


The GROUP BY is necessary because you want to add (group) the extensions of a project together. So group and add per ProjID.

For some projects there are no extensions. So NumDay and the sum of that will return blank values (Null). The Nz (NullToZero) function will convert the nulls to zeroes, so that you can also calculate with it.

[gmaxey]

I understand. Not fully, but well enough. Thank you so much. My next objective was to filter the value returned on a specific extension type. I knuckled that part out myself:


SELECT tblProjects.pkProjID, tblProjects.Base_Due_Date, Sum(Nz(tblExtensions.NumDays)) AS Total_Days_Extended
FROM tblProjects LEFT JOIN tblExtensions ON tblProjects.pkProjID = tblExtensions.fkProjID
WHERE (((tblExtensions.ExtType)="A"))
GROUP BY tblProjects.pkProjID, tblProjects.Base_Due_Date

[matthew219]

Setting up a structured approach with tblProjects and tblExtensions to manage project extensions effectively demonstrates a clear understanding of database management principles. Well done on tackling challenge with a systematic solution!

[bekean23]

Setting up a structured approach with tblProjects and tblExtensions to manage project extensions effectively demonstrates a clear understanding of database management principles. Well done on tackling challenge with a systematic solution!
Yes, it’s scalable, avoids redundancy, and keeps your data flexible for future changes (like more extensions or detailed extension reasons)