Finding values in one table when I know the field names from anoth

[NevilleT]

I have two tables.

The first is tblPeriod which lists months including start and end dates. It
also has an AllocatedNo field which gives every month a number such as P1,
P2, P3 etc.

The second is tblResourceXMonth which lists resources assigned to each
month. It has (amongst other fields) 60 fields called P1 to P60

I want to run a report to look at all assignments between 2 dates. I can
select the date range from the tblPeriod and through a query, find I need to
look at P1 to P6 for example.

Question is how do I use the fact I know which P fields from the second
query I want to look at, to get the values of those fields? I can probably
use VBA to loop through the records but wondered if I can do it through SQL.

 

[Allen Browne]

Neville, whenevery you have repeating fields (such as P1, P2, ...) it always
means that you have built a spreadsheet and not a database.

In a relational database, these many fields should be many *records* in a
related table. This table will have fields like this:
ResourceID indicates which resource this is for.
PeriodID contains the value "P1", or "P2", etc.
Amount the amount for this resource in this period.
So, there will be 60 records for resource 5 if it has data for 60 periods.

With that structure, you can easily query as you requested. Since all the
amounts are in one column, the question of how to know what field to look in
just disappeared.

Form more information, read about "normalization." Here's a starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[NevilleT]

Hi Allen
I started off down that track. I have a record that records monthly totals
for 5 years - hence the 60 fields, and daily records that record up to 42
daily records. When I started I built it as a single record for each
day/person/project combination. The monthly screen had 42 subforms to cater
for the days (6 weeks so that I could present it as a calendar and start on
the appropriate day on the top row, then end on the fourth or fifth row).
Problem was it was taking about 30 seconds to load all the queries. My
solution was to go back to a single record for each person/project
combination for the month with 42 allocation fields, and a single record for
each person/project for 5 years with 60 fields. Breaks all normalisation
rules but even after fiddling with indexes I could not get a form with 42
subforms to load successfully.

Since I posted, I have been working on running the first SQL using VB and
taking the field names to populate the controlsource of the report. It is
partially working and I think I can get it to run eventually but still
curious as to whether there is an SQL solution.
This is the code at present

Private Sub Report_Open(Cancel As Integer)

subInitialize

strSQL = "SELECT tblPeriod.StartDate, tblPeriod.EndDate, tblPeriod.Name,
tblPeriod.AllocatedNo" & _
" FROM tblPeriod" & _
" WHERE (((tblPeriod.StartDate)>= #" &
[Forms]![frmResourceReportSelector]![cmbFrom] & "#) AND" & _
" ((tblPeriod.EndDate) <= #" &
[Forms]![frmResourceReportSelector]![cmbTo] & "#));"
RunSelectQuery (strSQL)

rst.MoveLast
intRecs = rst.RecordCount

rst.MoveFirst
For intCounter = 1 To intRecs
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = rst![AllocatedNo]
Me.Controls(strTextBox).BorderStyle = 1
Me.Controls(strLabel).Caption = rst![Name]
rst.MoveNext
Next

End Sub

Sub subInitialize()
For intCounter = 1 To 12
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = ""
Me.Controls(strTextBox).BorderStyle = 0
Me.Controls(strLabel).Caption = ""
Next
End Sub

Still to be refined but it does work.

 

[Allen Browne]

Okay, good: you at know what normalization is about.

I don't get the need for 42 subforms though. Seems to me that you want the
lot (in one subform), or you want to query a particular one (so you filter
the subform, or reassign its RecordSource with a WHERE clause.)

42 records per day * 365 days per year * 5 years is < 80k records.
Times 60 would still be < 5 million records, and obviously there won't be
one for each combination, so we are talking possibly a few million records.
You will need astute indexing and thought out SQL clauses, but it should
work fine.

Sometimes the best solution is to save a report unbound, and assign the
Control Source of the text boxes in Report_Open. You may be able to alias
the fields in the query to avoid that. If you do dynamically assign the
ControlSources, be careful if this app gets ported to Access 2007: it's
likely to crash Access.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen
I started off down that track. I have a record that records monthly
totals
for 5 years - hence the 60 fields, and daily records that record up to 42
daily records. When I started I built it as a single record for each
day/person/project combination. The monthly screen had 42 subforms to
cater
for the days (6 weeks so that I could present it as a calendar and start
on
the appropriate day on the top row, then end on the fourth or fifth row).
Problem was it was taking about 30 seconds to load all the queries. My
solution was to go back to a single record for each person/project
combination for the month with 42 allocation fields, and a single record
for
each person/project for 5 years with 60 fields. Breaks all normalisation
rules but even after fiddling with indexes I could not get a form with 42
subforms to load successfully.

Since I posted, I have been working on running the first SQL using VB and
taking the field names to populate the controlsource of the report. It is
partially working and I think I can get it to run eventually but still
curious as to whether there is an SQL solution.
This is the code at present

Private Sub Report_Open(Cancel As Integer)

subInitialize

strSQL = "SELECT tblPeriod.StartDate, tblPeriod.EndDate,
tblPeriod.Name,
tblPeriod.AllocatedNo" & _
" FROM tblPeriod" & _
" WHERE (((tblPeriod.StartDate)>= #" &
[Forms]![frmResourceReportSelector]![cmbFrom] & "#) AND" & _
" ((tblPeriod.EndDate) <= #" &
[Forms]![frmResourceReportSelector]![cmbTo] & "#));"
RunSelectQuery (strSQL)

rst.MoveLast
intRecs = rst.RecordCount

rst.MoveFirst
For intCounter = 1 To intRecs
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = rst![AllocatedNo]
Me.Controls(strTextBox).BorderStyle = 1
Me.Controls(strLabel).Caption = rst![Name]
rst.MoveNext
Next

End Sub

Sub subInitialize()
For intCounter = 1 To 12
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = ""
Me.Controls(strTextBox).BorderStyle = 0
Me.Controls(strLabel).Caption = ""
Next
End Sub

Still to be refined but it does work.

Neville, whenevery you have repeating fields (such as P1, P2, ...) it
always
means that you have built a spreadsheet and not a database.

In a relational database, these many fields should be many *records* in a
related table. This table will have fields like this:
ResourceID indicates which resource this is for.
PeriodID contains the value "P1", or "P2", etc.
Amount the amount for this resource in this period.
So, there will be 60 records for resource 5 if it has data for 60
periods.

With that structure, you can easily query as you requested. Since all the
amounts are in one column, the question of how to know what field to look
in
just disappeared.

Form more information, read about "normalization." Here's a starting
point:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[NevilleT]

Thanks Allen. The reason for 42 subforms is that I have day names (Sun, Mon
etc) at the top, and 5 rows of 7 subforms below. If the days in the heading
start with Sunday, and Saturday is the 1st of the month, the first 6 subforms
are hidden and the 7th visible as 1st. That means row 2, 3, 4 and 5 give me
another 28 days which, if it is a 31 day month, leaves 2 days on row 6 with
the remaining 5 subforms hidden. In effect, they will never be used.

In the example above, the first 6 subforms wer not assigned a controlsource
during the on open event and the 7th had a controlsource using a hidden text
field containing the date for the first of the month. The query used a
criteria of the 6th hidden date field. All in all it just took too long to
process. In the current form, it loads in less than 5 seconds.

The approach I am using seems to be working and whilst it is certainly not
the purist solution, it will work in this situation. Thanks again for your
help - as you have helped me in the past.

Okay, good: you at know what normalization is about.

I don't get the need for 42 subforms though. Seems to me that you want the
lot (in one subform), or you want to query a particular one (so you filter
the subform, or reassign its RecordSource with a WHERE clause.)

42 records per day * 365 days per year * 5 years is < 80k records.
Times 60 would still be < 5 million records, and obviously there won't be
one for each combination, so we are talking possibly a few million records.
You will need astute indexing and thought out SQL clauses, but it should
work fine.

Sometimes the best solution is to save a report unbound, and assign the
Control Source of the text boxes in Report_Open. You may be able to alias
the fields in the query to avoid that. If you do dynamically assign the
ControlSources, be careful if this app gets ported to Access 2007: it's
likely to crash Access.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen
I started off down that track. I have a record that records monthly
totals
for 5 years - hence the 60 fields, and daily records that record up to 42
daily records. When I started I built it as a single record for each
day/person/project combination. The monthly screen had 42 subforms to
cater
for the days (6 weeks so that I could present it as a calendar and start
on
the appropriate day on the top row, then end on the fourth or fifth row).
Problem was it was taking about 30 seconds to load all the queries. My
solution was to go back to a single record for each person/project
combination for the month with 42 allocation fields, and a single record
for
each person/project for 5 years with 60 fields. Breaks all normalisation
rules but even after fiddling with indexes I could not get a form with 42
subforms to load successfully.

Since I posted, I have been working on running the first SQL using VB and
taking the field names to populate the controlsource of the report. It is
partially working and I think I can get it to run eventually but still
curious as to whether there is an SQL solution.
This is the code at present

Private Sub Report_Open(Cancel As Integer)

subInitialize

strSQL = "SELECT tblPeriod.StartDate, tblPeriod.EndDate,
tblPeriod.Name,
tblPeriod.AllocatedNo" & _
" FROM tblPeriod" & _
" WHERE (((tblPeriod.StartDate)>= #" &
[Forms]![frmResourceReportSelector]![cmbFrom] & "#) AND" & _
" ((tblPeriod.EndDate) <= #" &
[Forms]![frmResourceReportSelector]![cmbTo] & "#));"
RunSelectQuery (strSQL)

rst.MoveLast
intRecs = rst.RecordCount

rst.MoveFirst
For intCounter = 1 To intRecs
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = rst![AllocatedNo]
Me.Controls(strTextBox).BorderStyle = 1
Me.Controls(strLabel).Caption = rst![Name]
rst.MoveNext
Next

End Sub

Sub subInitialize()
For intCounter = 1 To 12
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = ""
Me.Controls(strTextBox).BorderStyle = 0
Me.Controls(strLabel).Caption = ""
Next
End Sub

Still to be refined but it does work.

Neville, whenevery you have repeating fields (such as P1, P2, ...) it
always
means that you have built a spreadsheet and not a database.

In a relational database, these many fields should be many *records* in a
related table. This table will have fields like this:
ResourceID indicates which resource this is for.
PeriodID contains the value "P1", or "P2", etc.
Amount the amount for this resource in this period.
So, there will be 60 records for resource 5 if it has data for 60
periods.

With that structure, you can easily query as you requested. Since all the
amounts are in one column, the question of how to know what field to look
in
just disappeared.

Form more information, read about "normalization." Here's a starting
point:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

I have two tables.

The first is tblPeriod which lists months including start and end
dates.
It
also has an AllocatedNo field which gives every month a number such as
P1,
P2, P3 etc.

The second is tblResourceXMonth which lists resources assigned to each
month. It has (amongst other fields) 60 fields called P1 to P60

I want to run a report to look at all assignments between 2 dates. I
can
select the date range from the tblPeriod and through a query, find I
need
to
look at P1 to P6 for example.

Question is how do I use the fact I know which P fields from the second
query I want to look at, to get the values of those fields? I can
probably
use VBA to loop through the records but wondered if I can do it through
SQL.

 

[Allen Browne]

Ah: I probably should have guessed that: 6 rows of 7 - a month-view calendar
layout. Fair enough.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. The reason for 42 subforms is that I have day names (Sun,
Mon
etc) at the top, and 5 rows of 7 subforms below. If the days in the
heading
start with Sunday, and Saturday is the 1st of the month, the first 6
subforms
are hidden and the 7th visible as 1st. That means row 2, 3, 4 and 5 give
me
another 28 days which, if it is a 31 day month, leaves 2 days on row 6
with
the remaining 5 subforms hidden. In effect, they will never be used.

In the example above, the first 6 subforms wer not assigned a
controlsource
during the on open event and the 7th had a controlsource using a hidden
text
field containing the date for the first of the month. The query used a
criteria of the 6th hidden date field. All in all it just took too long
to
process. In the current form, it loads in less than 5 seconds.

The approach I am using seems to be working and whilst it is certainly not
the purist solution, it will work in this situation. Thanks again for
your
help - as you have helped me in the past.

Okay, good: you at know what normalization is about.

I don't get the need for 42 subforms though. Seems to me that you want
the
lot (in one subform), or you want to query a particular one (so you
filter
the subform, or reassign its RecordSource with a WHERE clause.)

42 records per day * 365 days per year * 5 years is < 80k records.
Times 60 would still be < 5 million records, and obviously there won't be
one for each combination, so we are talking possibly a few million
records.
You will need astute indexing and thought out SQL clauses, but it should
work fine.

Sometimes the best solution is to save a report unbound, and assign the
Control Source of the text boxes in Report_Open. You may be able to alias
the fields in the query to avoid that. If you do dynamically assign the
ControlSources, be careful if this app gets ported to Access 2007: it's
likely to crash Access.

Hi Allen
I started off down that track. I have a record that records monthly
totals
for 5 years - hence the 60 fields, and daily records that record up to
42
daily records. When I started I built it as a single record for each
day/person/project combination. The monthly screen had 42 subforms to
cater
for the days (6 weeks so that I could present it as a calendar and
start
on
the appropriate day on the top row, then end on the fourth or fifth
row).
Problem was it was taking about 30 seconds to load all the queries. My
solution was to go back to a single record for each person/project
combination for the month with 42 allocation fields, and a single
record
for
each person/project for 5 years with 60 fields. Breaks all
normalisation
rules but even after fiddling with indexes I could not get a form with
42
subforms to load successfully.

Since I posted, I have been working on running the first SQL using VB
and
taking the field names to populate the controlsource of the report. It
is
partially working and I think I can get it to run eventually but still
curious as to whether there is an SQL solution.
This is the code at present

Private Sub Report_Open(Cancel As Integer)

subInitialize

strSQL = "SELECT tblPeriod.StartDate, tblPeriod.EndDate,
tblPeriod.Name,
tblPeriod.AllocatedNo" & _
" FROM tblPeriod" & _
" WHERE (((tblPeriod.StartDate)>= #" &
[Forms]![frmResourceReportSelector]![cmbFrom] & "#) AND" & _
" ((tblPeriod.EndDate) <= #" &
[Forms]![frmResourceReportSelector]![cmbTo] & "#));"
RunSelectQuery (strSQL)

rst.MoveLast
intRecs = rst.RecordCount

rst.MoveFirst
For intCounter = 1 To intRecs
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = rst![AllocatedNo]
Me.Controls(strTextBox).BorderStyle = 1
Me.Controls(strLabel).Caption = rst![Name]
rst.MoveNext
Next

End Sub

Sub subInitialize()
For intCounter = 1 To 12
strTextBox = "txt" & intCounter
strLabel = "lbl" & intCounter
Me.Controls(strTextBox).ControlSource = ""
Me.Controls(strTextBox).BorderStyle = 0
Me.Controls(strLabel).Caption = ""
Next
End Sub

Still to be refined but it does work.
:

Neville, whenevery you have repeating fields (such as P1, P2, ...) it
always
means that you have built a spreadsheet and not a database.

In a relational database, these many fields should be many *records*
in a
related table. This table will have fields like this:
ResourceID indicates which resource this is for.
PeriodID contains the value "P1", or "P2", etc.
Amount the amount for this resource in this period.
So, there will be 60 records for resource 5 if it has data for 60
periods.

With that structure, you can easily query as you requested. Since all
the
amounts are in one column, the question of how to know what field to
look
in
just disappeared.

Form more information, read about "normalization." Here's a starting
point:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

I have two tables.

The first is tblPeriod which lists months including start and end
dates.
It
also has an AllocatedNo field which gives every month a number such
as
P1,
P2, P3 etc.

The second is tblResourceXMonth which lists resources assigned to
each
month. It has (amongst other fields) 60 fields called P1 to P60

I want to run a report to look at all assignments between 2 dates.
I
can
select the date range from the tblPeriod and through a query, find I
need
to
look at P1 to P6 for example.

Question is how do I use the fact I know which P fields from the
second
query I want to look at, to get the values of those fields? I can
probably
use VBA to loop through the records but wondered if I can do it
through
SQL.