Summing Totals in a query

[Rudi]

Name Projects1 Hours1 Projects2 Hours2
Projects3 Hours3

User1 ProjectA 3 ProjectB 5
ProjectA 1
User1 ProjectB 2 ProjectB 3
ProjectA 2
User1 ProjectA 1 ProjectB 7
ProjectB 3

Now, I want the totals (preferably in a report)

Totals for User1 for Period A (the user is the CurrentUser() and the period
is a chosen date from a form. The user on the form chooses the year and month
and all the entries in that month is displayed in the query result
--------------------------------------------------------------------------------
Projects TotalHours (This is basically what I want in the end)

ProjectA 7
ProjectB 2
--------------------------------------------------------------------------------
Below is my query is SQL-view.

SELECT Timesheet.Date, Timesheet.Project1, Timesheet.Project2,
Timesheet.Project3, Timesheet.ProjectHours1, Timesheet.ProjectHours2,
Timesheet.ProjectHours3, Timesheet.Names
FROM Timesheet
WHERE (((Timesheet.Date) Between DateSerial(Forms![Timesheet Report
Selection]!Years,Forms![Timesheet Report Selection]!Months,20) And
DateSerial(Forms![Timesheet Report Selection]!Years,Forms![Timesheet Report
Selection]!Months+1,19)) And ((Timesheet.Names)=Forms![Timesheet Report
Selection]!Names))
ORDER BY Timesheet.Date;

As you will see, there is 3 project slots a choice of several projects
(ProjectA, projectB, etc). For each project there is spesific hours assigned.
The query works fine for generating a complete report, but now I want to sum
the totals of hours worked on each project for the duration spesified from
the form and generate a report from this (like I showed above).

How can I do this?

Thanks!

 

[Douglas J Steele]

The major reason why what you're trying to do is difficult is because your
tables aren't normalized. Having fields like Project1, Project2 & Project3
and Hours1, Hours2 & Hours3 in a single row is known as a repeating group,
and is a violation of database normalization principles.

Instead of having each project as a field, you should have a second table,
joined to the first, with each project as a row in that second table. Then
your queries become very simple. (It also handles the situation where
suddenly you need a fourth project)

The maxim that gets quoted here a lot is "Rows are cheap, Fields are
expensive".

 

[Rudi]

Okay, I will then try and change this into what you propose.

Here is what I have at the moment:

I have a form with 8 slots for the project name and 8 slots for the Hours
corresponding to each project slot. From these project slots, the user can
select any of the projects (from a drop-down list which uses another table
listing all of the possible projects). My form writes into the final table
for which I have at the moment, a field for every project slot and a field
for all the corresponding project hours.

What do have to need to change? Should I only have one project field and one
hours field named "Projects" and "Hours" in my final table respectively and
somehow connect it to another table? And how would I get the hours to
correspond with a certain project chosen?

Thanks!

I have

The major reason why what you're trying to do is difficult is because your
tables aren't normalized. Having fields like Project1, Project2 & Project3
and Hours1, Hours2 & Hours3 in a single row is known as a repeating group,
and is a violation of database normalization principles.

Instead of having each project as a field, you should have a second table,
joined to the first, with each project as a row in that second table. Then
your queries become very simple. (It also handles the situation where
suddenly you need a fourth project)

The maxim that gets quoted here a lot is "Rows are cheap, Fields are
expensive".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Name Projects1 Hours1 Projects2 Hours2
Projects3 Hours3

User1 ProjectA 3 ProjectB 5
ProjectA 1
User1 ProjectB 2 ProjectB 3
ProjectA 2
User1 ProjectA 1 ProjectB 7
ProjectB 3

Now, I want the totals (preferably in a report)

Totals for User1 for Period A (the user is the CurrentUser() and the period
is a chosen date from a form. The user on the form chooses the year and month
and all the entries in that month is displayed in the query result)
-------------------------------------------------------------------------- ------
Projects TotalHours (This is basically what I want in the end)

ProjectA 7
ProjectB 20
-------------------------------------------------------------------------- ------
Below is my query is SQL-view.

SELECT Timesheet.Date, Timesheet.Project1, Timesheet.Project2,
Timesheet.Project3, Timesheet.ProjectHours1, Timesheet.ProjectHours2,
Timesheet.ProjectHours3, Timesheet.Names
FROM Timesheet
WHERE (((Timesheet.Date) Between DateSerial(Forms![Timesheet Report
Selection]!Years,Forms![Timesheet Report Selection]!Months,20) And
DateSerial(Forms![Timesheet Report Selection]!Years,Forms![Timesheet Report
Selection]!Months+1,19)) And ((Timesheet.Names)=Forms![Timesheet Report
Selection]!Names))
ORDER BY Timesheet.Date;

As you will see, there is 3 project slots a choice of several projects
(ProjectA, projectB, etc). For each project there is spesific hours assigned.
The query works fine for generating a complete report, but now I want to sum
the totals of hours worked on each project for the duration spesified from
the form and generate a report from this (like I showed above).

How can I do this?

Thanks!

 

[Douglas J Steele]

Looking more closely at your existing data, unless there are additional
fields that you didn't show, your table would only include User, Project and
Hours:

User Project Hours
User1 ProjectA 3
User1 ProjectB 2
User1 ProjectA 1
User1 ProjectB 5
User1 ProjectB 3
User1 ProjectB 7
User1 ProjectA 1
User1 ProjectA 2
User1 ProjectB 3

(You shouldn't use Name as a field: it's a reserved word. For that matter,
User might be, so you might want to use UserName)

To convert your existing data to that, you can use a UNION query:

SELECT [Name] AS User, Projects1 AS Project, Hours1 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects2 AS Project, Hours2 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects3 AS Project, Hours3 AS Hours
FROM MyTable

To summarize, you'd have a TOTALS query:

SELECT User, Project, Sum(Hours) AS TotalHours
FROM MyNormalizedTable
GROUP BY User, Project



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay, I will then try and change this into what you propose.

Here is what I have at the moment:

I have a form with 8 slots for the project name and 8 slots for the Hours
corresponding to each project slot. From these project slots, the user can
select any of the projects (from a drop-down list which uses another table
listing all of the possible projects). My form writes into the final table
for which I have at the moment, a field for every project slot and a field
for all the corresponding project hours.

What do have to need to change? Should I only have one project field and one
hours field named "Projects" and "Hours" in my final table respectively and
somehow connect it to another table? And how would I get the hours to
correspond with a certain project chosen?

Thanks!

I have

The major reason why what you're trying to do is difficult is because your
tables aren't normalized. Having fields like Project1, Project2 & Project3
and Hours1, Hours2 & Hours3 in a single row is known as a repeating group,
and is a violation of database normalization principles.

Instead of having each project as a field, you should have a second table,
joined to the first, with each project as a row in that second table. Then
your queries become very simple. (It also handles the situation where
suddenly you need a fourth project)

The maxim that gets quoted here a lot is "Rows are cheap, Fields are
expensive".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Name Projects1 Hours1 Projects2 Hours2
Projects3 Hours3

User1 ProjectA 3 ProjectB 5
ProjectA 1
User1 ProjectB 2 ProjectB 3
ProjectA 2
User1 ProjectA 1 ProjectB 7
ProjectB 3

Now, I want the totals (preferably in a report)

Totals for User1 for Period A (the user is the CurrentUser() and the period
is a chosen date from a form. The user on the form chooses the year

and
month

and all the entries in that month is displayed in the query result)

--------------------------------------------------------------------------

------
Projects TotalHours (This is basically what I want in the end)

ProjectA 7
ProjectB 20

--------------------------------------------------------------------------

------
Below is my query is SQL-view.

SELECT Timesheet.Date, Timesheet.Project1, Timesheet.Project2,
Timesheet.Project3, Timesheet.ProjectHours1, Timesheet.ProjectHours2,
Timesheet.ProjectHours3, Timesheet.Names
FROM Timesheet
WHERE (((Timesheet.Date) Between DateSerial(Forms![Timesheet Report
Selection]!Years,Forms![Timesheet Report Selection]!Months,20) And
DateSerial(Forms![Timesheet Report Selection]!Years,Forms![Timesheet Report
Selection]!Months+1,19)) And ((Timesheet.Names)=Forms![Timesheet Report
Selection]!Names))
ORDER BY Timesheet.Date;

As you will see, there is 3 project slots a choice of several projects
(ProjectA, projectB, etc). For each project there is spesific hours assigned.
The query works fine for generating a complete report, but now I want

to
sum

the totals of hours worked on each project for the duration spesified from
the form and generate a report from this (like I showed above).

How can I do this?

Thanks!

 

[Rudi]

My main table has the following fields:

ID, Names, Dates, Project1, Project2, ..., Project6, ProjectHours1,
ProjectHours2, ...,ProjectHours6.

I dont want to put every project in a different row (as you have shown
below) unless it is in a secondary table which is linked to this table. The
secondary table should look something like you have shown below, but should
be (if possible) connected with only one row in the primary table. Like an
array:

ID Names Dates Projects Hours
__________________________________________
23 John 22/05/1982 ProjectA 5
ProjectB 6
ProjectA 2
__________________________________________
24 Mary 26/8/2003 ProjectC 1
ProjectB 2
__________________________________________

The seconday table should look something like this (I suppose):

ID Projects Hours
______________________
23 ProjectA 5
ProjectB 6
ProjectA 2
______________________
24 ProjectC 1
ProjectB 2


Something like this? I suppose this is where relationships come into play?

Looking more closely at your existing data, unless there are additional
fields that you didn't show, your table would only include User, Project and
Hours:

User Project Hours
User1 ProjectA 3
User1 ProjectB 2
User1 ProjectA 1
User1 ProjectB 5
User1 ProjectB 3
User1 ProjectB 7
User1 ProjectA 1
User1 ProjectA 2
User1 ProjectB 3

(You shouldn't use Name as a field: it's a reserved word. For that matter,
User might be, so you might want to use UserName)

To convert your existing data to that, you can use a UNION query:

SELECT [Name] AS User, Projects1 AS Project, Hours1 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects2 AS Project, Hours2 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects3 AS Project, Hours3 AS Hours
FROM MyTable

To summarize, you'd have a TOTALS query:

SELECT User, Project, Sum(Hours) AS TotalHours
FROM MyNormalizedTable
GROUP BY User, Project



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay, I will then try and change this into what you propose.

Here is what I have at the moment:

I have a form with 8 slots for the project name and 8 slots for the Hours
corresponding to each project slot. From these project slots, the user can
select any of the projects (from a drop-down list which uses another table
listing all of the possible projects). My form writes into the final table
for which I have at the moment, a field for every project slot and a field
for all the corresponding project hours.

What do have to need to change? Should I only have one project field and one
hours field named "Projects" and "Hours" in my final table respectively and
somehow connect it to another table? And how would I get the hours to
correspond with a certain project chosen?

Thanks!

I have

The major reason why what you're trying to do is difficult is because your
tables aren't normalized. Having fields like Project1, Project2 & Project3
and Hours1, Hours2 & Hours3 in a single row is known as a repeating group,
and is a violation of database normalization principles.

Instead of having each project as a field, you should have a second table,
joined to the first, with each project as a row in that second table. Then
your queries become very simple. (It also handles the situation where
suddenly you need a fourth project)

The maxim that gets quoted here a lot is "Rows are cheap, Fields are
expensive".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Name Projects1 Hours1 Projects2 Hours2
Projects3 Hours3

User1 ProjectA 3 ProjectB 5
ProjectA 1
User1 ProjectB 2 ProjectB 3
ProjectA 2
User1 ProjectA 1 ProjectB 7
ProjectB 3

Now, I want the totals (preferably in a report)

Totals for User1 for Period A (the user is the CurrentUser() and the
period
is a chosen date from a form. The user on the form chooses the year and
month
and all the entries in that month is displayed in the query result)
--------------------------------------------------------------------------
------
Projects TotalHours (This is basically what I want in the end)

ProjectA 7
ProjectB 20
--------------------------------------------------------------------------
------
Below is my query is SQL-view.

SELECT Timesheet.Date, Timesheet.Project1, Timesheet.Project2,
Timesheet.Project3, Timesheet.ProjectHours1, Timesheet.ProjectHours2,
Timesheet.ProjectHours3, Timesheet.Names
FROM Timesheet
WHERE (((Timesheet.Date) Between DateSerial(Forms![Timesheet Report
Selection]!Years,Forms![Timesheet Report Selection]!Months,20) And
DateSerial(Forms![Timesheet Report Selection]!Years,Forms![Timesheet
Report
Selection]!Months+1,19)) And ((Timesheet.Names)=Forms![Timesheet Report
Selection]!Names))
ORDER BY Timesheet.Date;

As you will see, there is 3 project slots a choice of several projects
(ProjectA, projectB, etc). For each project there is spesific hours
assigned.
The query works fine for generating a complete report, but now I want to
sum
the totals of hours worked on each project for the duration spesified from
the form and generate a report from this (like I showed above).

How can I do this?

Thanks!

 

[Douglas J Steele]

I'm assuming that your existing table (which I'll call tblCurrent) has ID as
its primary key.

Based on what you've shown below, it should be changed to have only three
fields: ID, Names and Dates (still with ID as its primary key)

ID Names Dates
23 John 22/05/1982
24 Mary 26/08/2003

The new table (tblNew) should also have three fields: ID, Projects and
ProjectHours. The primary key for this new table will be a compound key
consisting of both ID and Projects.

ID Projects Hours
23 ProjectA 5
23 ProjectB 6
23 ProjectA 2
24 ProjectC 1
24 ProjectB 2

Yes, there should be a relationship between tblCurrent and tblNew. Hopefully
you realize, though, that adding a relationship doesn't help populate the
fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My main table has the following fields:

ID, Names, Dates, Project1, Project2, ..., Project6, ProjectHours1,
ProjectHours2, ...,ProjectHours6.

I dont want to put every project in a different row (as you have shown
below) unless it is in a secondary table which is linked to this table. The
secondary table should look something like you have shown below, but should
be (if possible) connected with only one row in the primary table. Like an
array:

ID Names Dates Projects Hours
__________________________________________
23 John 22/05/1982 ProjectA 5
ProjectB 6
ProjectA 2
__________________________________________
24 Mary 26/8/2003 ProjectC 1
ProjectB 2
__________________________________________

The seconday table should look something like this (I suppose):

ID Projects Hours
______________________
23 ProjectA 5
ProjectB 6
ProjectA 2
______________________
24 ProjectC 1
ProjectB 2


Something like this? I suppose this is where relationships come into play?

Looking more closely at your existing data, unless there are additional
fields that you didn't show, your table would only include User, Project and
Hours:

User Project Hours
User1 ProjectA 3
User1 ProjectB 2
User1 ProjectA 1
User1 ProjectB 5
User1 ProjectB 3
User1 ProjectB 7
User1 ProjectA 1
User1 ProjectA 2
User1 ProjectB 3

(You shouldn't use Name as a field: it's a reserved word. For that matter,
User might be, so you might want to use UserName)

To convert your existing data to that, you can use a UNION query:

SELECT [Name] AS User, Projects1 AS Project, Hours1 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects2 AS Project, Hours2 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects3 AS Project, Hours3 AS Hours
FROM MyTable

To summarize, you'd have a TOTALS query:

SELECT User, Project, Sum(Hours) AS TotalHours
FROM MyNormalizedTable
GROUP BY User, Project



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay, I will then try and change this into what you propose.

Here is what I have at the moment:

I have a form with 8 slots for the project name and 8 slots for the Hours
corresponding to each project slot. From these project slots, the user can
select any of the projects (from a drop-down list which uses another table
listing all of the possible projects). My form writes into the final table
for which I have at the moment, a field for every project slot and a field
for all the corresponding project hours.

What do have to need to change? Should I only have one project field

and
one

hours field named "Projects" and "Hours" in my final table

respectively
and

somehow connect it to another table? And how would I get the hours to
correspond with a certain project chosen?

Thanks!

I have

:

The major reason why what you're trying to do is difficult is

because
your

tables aren't normalized. Having fields like Project1, Project2 & Project3
and Hours1, Hours2 & Hours3 in a single row is known as a repeating group,
and is a violation of database normalization principles.

Instead of having each project as a field, you should have a second table,
joined to the first, with each project as a row in that second

table.
Then

your queries become very simple. (It also handles the situation where
suddenly you need a fourth project)

The maxim that gets quoted here a lot is "Rows are cheap, Fields are
expensive".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Name Projects1 Hours1 Projects2 Hours2
Projects3 Hours3

User1 ProjectA 3 ProjectB 5
ProjectA 1
User1 ProjectB 2 ProjectB 3
ProjectA 2
User1 ProjectA 1 ProjectB 7
ProjectB 3

Now, I want the totals (preferably in a report)

Totals for User1 for Period A (the user is the CurrentUser() and the
period
is a chosen date from a form. The user on the form chooses the

year
and

month
and all the entries in that month is displayed in the query result)

-------------------------------------------------------------------------- the
end)

ProjectA 7
ProjectB 20

--------------------------------------------------------------------------

------
Below is my query is SQL-view.

SELECT Timesheet.Date, Timesheet.Project1, Timesheet.Project2,
Timesheet.Project3, Timesheet.ProjectHours1, Timesheet.ProjectHours2,
Timesheet.ProjectHours3, Timesheet.Names
FROM Timesheet
WHERE (((Timesheet.Date) Between DateSerial(Forms![Timesheet Report
Selection]!Years,Forms![Timesheet Report Selection]!Months,20) And
DateSerial(Forms![Timesheet Report Selection]!Years,Forms![Timesheet
Report
Selection]!Months+1,19)) And ((Timesheet.Names)=Forms![Timesheet Report
Selection]!Names))
ORDER BY Timesheet.Date;

As you will see, there is 3 project slots a choice of several projects
(ProjectA, projectB, etc). For each project there is spesific hours
assigned.
The query works fine for generating a complete report, but now I

want
to

sum
the totals of hours worked on each project for the duration

spesified
from

the form and generate a report from this (like I showed above).

How can I do this?

Thanks!

 

[Rudi]

In creating a relationship between these tables, which fields should I
connect with which? And what exactly do you mean with "...doesn't help
populate the fields"? I haven't used relationships and is therefore kind of
new with the concept.

Thanks!

I'm assuming that your existing table (which I'll call tblCurrent) has ID as
its primary key.

Based on what you've shown below, it should be changed to have only three
fields: ID, Names and Dates (still with ID as its primary key)

ID Names Dates
23 John 22/05/1982
24 Mary 26/08/2003

The new table (tblNew) should also have three fields: ID, Projects and
ProjectHours. The primary key for this new table will be a compound key
consisting of both ID and Projects.

ID Projects Hours
23 ProjectA 5
23 ProjectB 6
23 ProjectA 2
24 ProjectC 1
24 ProjectB 2

Yes, there should be a relationship between tblCurrent and tblNew. Hopefully
you realize, though, that adding a relationship doesn't help populate the
fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My main table has the following fields:

ID, Names, Dates, Project1, Project2, ..., Project6, ProjectHours1,
ProjectHours2, ...,ProjectHours6.

I dont want to put every project in a different row (as you have shown
below) unless it is in a secondary table which is linked to this table. The
secondary table should look something like you have shown below, but should
be (if possible) connected with only one row in the primary table. Like an
array:

ID Names Dates Projects Hours
__________________________________________
23 John 22/05/1982 ProjectA 5
ProjectB 6
ProjectA 2
__________________________________________
24 Mary 26/8/2003 ProjectC 1
ProjectB 2
__________________________________________

The seconday table should look something like this (I suppose):

ID Projects Hours
______________________
23 ProjectA 5
ProjectB 6
ProjectA 2
______________________
24 ProjectC 1
ProjectB 2


Something like this? I suppose this is where relationships come into play?

Looking more closely at your existing data, unless there are additional
fields that you didn't show, your table would only include User, Project and
Hours:

User Project Hours
User1 ProjectA 3
User1 ProjectB 2
User1 ProjectA 1
User1 ProjectB 5
User1 ProjectB 3
User1 ProjectB 7
User1 ProjectA 1
User1 ProjectA 2
User1 ProjectB 3

(You shouldn't use Name as a field: it's a reserved word. For that matter,
User might be, so you might want to use UserName)

To convert your existing data to that, you can use a UNION query:

SELECT [Name] AS User, Projects1 AS Project, Hours1 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects2 AS Project, Hours2 AS Hours
FROM MyTable
UNION
SELECT [Name] AS User, Projects3 AS Project, Hours3 AS Hours
FROM MyTable

To summarize, you'd have a TOTALS query:

SELECT User, Project, Sum(Hours) AS TotalHours
FROM MyNormalizedTable
GROUP BY User, Project



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Okay, I will then try and change this into what you propose.

Here is what I have at the moment:

I have a form with 8 slots for the project name and 8 slots for the Hours
corresponding to each project slot. From these project slots, the user can
select any of the projects (from a drop-down list which uses another table
listing all of the possible projects). My form writes into the final table
for which I have at the moment, a field for every project slot and a field
for all the corresponding project hours.

What do have to need to change? Should I only have one project field and
one
hours field named "Projects" and "Hours" in my final table respectively
and
somehow connect it to another table? And how would I get the hours to
correspond with a certain project chosen?

Thanks!

I have

:

The major reason why what you're trying to do is difficult is because
your
tables aren't normalized. Having fields like Project1, Project2 &
Project3
and Hours1, Hours2 & Hours3 in a single row is known as a repeating
group,
and is a violation of database normalization principles.

Instead of having each project as a field, you should have a second
table,
joined to the first, with each project as a row in that second table.
Then
your queries become very simple. (It also handles the situation where
suddenly you need a fourth project)

The maxim that gets quoted here a lot is "Rows are cheap, Fields are
expensive".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Name Projects1 Hours1 Projects2 Hours2
Projects3 Hours3

User1 ProjectA 3 ProjectB 5
ProjectA 1
User1 ProjectB 2 ProjectB 3
ProjectA 2
User1 ProjectA 1 ProjectB 7
ProjectB 3

Now, I want the totals (preferably in a report)

Totals for User1 for Period A (the user is the CurrentUser() and the
period
is a chosen date from a form. The user on the form chooses the year
and
month
and all the entries in that month is displayed in the query result)

--------------------------------------------------------------------------
------
Projects TotalHours (This is basically what I want in the
end)

ProjectA 7
ProjectB 20

--------------------------------------------------------------------------
------
Below is my query is SQL-view.

SELECT Timesheet.Date, Timesheet.Project1, Timesheet.Project2,
Timesheet.Project3, Timesheet.ProjectHours1, Timesheet.ProjectHours2,
Timesheet.ProjectHours3, Timesheet.Names
FROM Timesheet
WHERE (((Timesheet.Date) Between DateSerial(Forms![Timesheet Report
Selection]!Years,Forms![Timesheet Report Selection]!Months,20) And
DateSerial(Forms![Timesheet Report Selection]!Years,Forms![Timesheet
Report
Selection]!Months+1,19)) And ((Timesheet.Names)=Forms![Timesheet
Report
Selection]!Names))
ORDER BY Timesheet.Date;

As you will see, there is 3 project slots a choice of several projects
(ProjectA, projectB, etc). For each project there is spesific hours
assigned.
The query works fine for generating a complete report, but now I want
to
sum
the totals of hours worked on each project for the duration spesified
from
the form and generate a report from this (like I showed above).

How can I do this?

Thanks!

 

[Douglas J Steele]

The line in the relationship window should join the ID field between the two
tables.

All a relationship does is indicate that the fields are related. This can be
a help when you're creating a query joining the two tables: Access will
already know how to join them (although you can, of course, override what
Access assumes).

To me, the major point of relationiships is to allow you to set up
referential integrity, which will prevent you from entering an ID in tblNew
if that ID doesn't exist in tblCurrent, or can be used to delete all rows
with a specific ID in tblNew if you delete the row with that ID in
tblCurrent (or can be used to update the ID field in tblNew if you change
the value of the ID field in a row in tblCurrent, although I've always
questioned this, as I don't believe the primary key value should ever be
changed!)

However, adding a row with a particular ID in tblCurrent will not cause rows
to be inserted in tblNew (a common misconception), nor (as I implied above)
can you insert a row with a particular ID into tblNew and expect a
corresponding row to be created in tblCurrent.