Help with database structure

[Tony Williams]

We currently have a database that holds statistics from external
organisations. Once a quarter they provide figures on a year to date basis
from which we produce quarterly reports on activity. The database has 3
tables for the organisation name, the quarter and a linked table that holds
all the statistics. Currently we use Excell to deduct the last quarters
figures from the year to date figures to give us the current quarters
figures and then key them into the database to produce the reports.
Unfortunately we can't get the organisations to submit quarterly figures
(the obvious answer!) and would like to structure the database so that we
can input the year to date figures and let Access create the quarterly
figures rather than have this two stage approach using Excell. Could someone
point us in the direction of how we start to do this?
TIA
Tony Williams

 

[Guest]

Hi, Tony.

I recommend a "divide and conquer" strategy. After importing your new data
into a dummy table of the same structure as your Data table (inserting the
current quarter into the Quarter field), you can run an append query to add
the new quarterly data, by subtracting the current YTD from it:

1. Create a Totals query, Grouping By the Organization PK, and Summing
Amount.

2. Create a Select query that chooses the Organization PK the Quarter, and
the Amount from your dummy table.

3. Create an Append query that inserts the OrganizationID, the Quarter, and
the NewAmount - YTD Amount into your Data table.

INSERT INTO [Quarterly Figures] ( CustomerID, Quarter, Amount )
SELECT [Quarterly Figures 1].CustomerID, [Quarterly Figures 2].Quarter,
[Amount]-[Quarterly Figures 1].YTD AS Expr1
FROM [Quarterly Figures 1] INNER JOIN [Quarterly Figures 2] ON [Quarterly
Figures 1].CustomerID = [Quarterly Figures 2].CustomerID;

HTH
Kevin Sprinkel

 

[Guest]

Thanks Sprinks I'll start to look at that. Bit of a novice so it may take a
while!!!
Thanks again
Tony

Hi, Tony.

I recommend a "divide and conquer" strategy. After importing your new data
into a dummy table of the same structure as your Data table (inserting the
current quarter into the Quarter field), you can run an append query to add
the new quarterly data, by subtracting the current YTD from it:

1. Create a Totals query, Grouping By the Organization PK, and Summing
Amount.

2. Create a Select query that chooses the Organization PK the Quarter, and
the Amount from your dummy table.

3. Create an Append query that inserts the OrganizationID, the Quarter, and
the NewAmount - YTD Amount into your Data table.

INSERT INTO [Quarterly Figures] ( CustomerID, Quarter, Amount )
SELECT [Quarterly Figures 1].CustomerID, [Quarterly Figures 2].Quarter,
[Amount]-[Quarterly Figures 1].YTD AS Expr1
FROM [Quarterly Figures 1] INNER JOIN [Quarterly Figures 2] ON [Quarterly
Figures 1].CustomerID = [Quarterly Figures 2].CustomerID;

HTH
Kevin Sprinkel

We currently have a database that holds statistics from external
organisations. Once a quarter they provide figures on a year to date basis
from which we produce quarterly reports on activity. The database has 3
tables for the organisation name, the quarter and a linked table that holds
all the statistics. Currently we use Excell to deduct the last quarters
figures from the year to date figures to give us the current quarters
figures and then key them into the database to produce the reports.
Unfortunately we can't get the organisations to submit quarterly figures
(the obvious answer!) and would like to structure the database so that we
can input the year to date figures and let Access create the quarterly
figures rather than have this two stage approach using Excell. Could someone
point us in the direction of how we start to do this?
TIA
Tony Williams

 

[Guest]

I should mention that you needn't learn any SQL code, you can easily (and in
fact, I did) create the queries one at a time using the Query By Design grid
(the default Query Design View).

Create your dummy table by highlighting your data table, cutting (Ctrl-C)
and pasting (Ctrl-V) to the Table window. Select Structure Only from the
dialog box and give it a unique name.

If you've never designed a query, Select Queries/New. You'll be shown a
list of tables and queries. In the first query, you'll select your data
table. Access will put its field list in the top half of the view. Drag the
fields you need from the Field List to the query grid below--Organization and
Amount.

Since you need to do a total to calculate the YTD value for each
organization, switch to Totals view by selecting View, Totals. A Totals row
is added to the lower half of the grid, and "Group By" will be assigned as
the default value for each field. Totals creates a total for each unique
combination of the fields designated as "Group By". Change this value to
"Sum" in your Amount field.

Press the exclamation point icon to see the result of selecting these
fields--a recordset is created showing the total Amount for each
organization. If you wish, give the column an identifying label by changing
Amount in the field row to YTD:[Amount]. The query will still sum the Amount
field for each unique customer, but will display the column with the heading
"YTD". You're done; save the query definition.

For the second query, choose your dummy table on entrance to design view,
and drag and drop the OrganizationID, the Quarter, and the Amount fields to
the grid. Save the query.

Now create the Append query that inserts the new records in your table. On
entering Design view, click on the Queries tab. Double-click on each of your
2 previously saved queries to load them into the window. Be sure a
relationship is shown by a line connecting the OrganizationID field in each
query. If not present, make the connection by dragging one of them to the
other. Drag and drop the OrganizationID and Quarter fields to the grid.

To create the calculation, you'll simply type in the formula. Since there
are two recordsets in this query, you'll need to specify the query and the
field, separated by a dot. For example, if your two queries are called QF1
and QF2, and you gave the total the YTD label, enter the following in the
Field: row of a new column:

=[QF2].[Amount] - [QF1].[YTD]

At this point, you still have a Select query. Press the exclamation point
to see the new quarterly numbers. To add these records to your data table,
select Query, Append. Select your data table from the dropdown list and
press OK. Access will make a "guess" as to what fields you'd like to write
this data to in the Append To: row. Check it over and make any corrections
you need. Save the query.

Notes:

Each month, after verifying the dummy table is empty, load your new YTD data
into it. If your organizations don't supply the data with the quarter, you
can easily add it to each record with a Modify query. Let me know if you
need any assistance with this. Then simply run the Append query to add the
new records.

Be aware that if you happen to run the Append query a second time, Access
will attempt to paste another set of records to your table. If the dummy
table is empty, it can not add any records. If it's still full with the
latest quarterly numbers (which equals the YTD), it would add a set of
records with the amount equal to zero. This wouldn't affect your totals, but
I think it would be prudent to make some provisions to limit who can run this
query.

HTH
Kevin Sprinkel

 

[Guest]

Thanks a lot Kevin for such a detailed explanation! I will let you know when
we've got it to work on our database.
Thanks again, extremely grateful
Tony

I should mention that you needn't learn any SQL code, you can easily (and in
fact, I did) create the queries one at a time using the Query By Design grid
(the default Query Design View).

Create your dummy table by highlighting your data table, cutting (Ctrl-C)
and pasting (Ctrl-V) to the Table window. Select Structure Only from the
dialog box and give it a unique name.

If you've never designed a query, Select Queries/New. You'll be shown a
list of tables and queries. In the first query, you'll select your data
table. Access will put its field list in the top half of the view. Drag the
fields you need from the Field List to the query grid below--Organization and
Amount.

Since you need to do a total to calculate the YTD value for each
organization, switch to Totals view by selecting View, Totals. A Totals row
is added to the lower half of the grid, and "Group By" will be assigned as
the default value for each field. Totals creates a total for each unique
combination of the fields designated as "Group By". Change this value to
"Sum" in your Amount field.

Press the exclamation point icon to see the result of selecting these
fields--a recordset is created showing the total Amount for each
organization. If you wish, give the column an identifying label by changing
Amount in the field row to YTD:[Amount]. The query will still sum the Amount
field for each unique customer, but will display the column with the heading
"YTD". You're done; save the query definition.

For the second query, choose your dummy table on entrance to design view,
and drag and drop the OrganizationID, the Quarter, and the Amount fields to
the grid. Save the query.

Now create the Append query that inserts the new records in your table. On
entering Design view, click on the Queries tab. Double-click on each of your
2 previously saved queries to load them into the window. Be sure a
relationship is shown by a line connecting the OrganizationID field in each
query. If not present, make the connection by dragging one of them to the
other. Drag and drop the OrganizationID and Quarter fields to the grid.

To create the calculation, you'll simply type in the formula. Since there
are two recordsets in this query, you'll need to specify the query and the
field, separated by a dot. For example, if your two queries are called QF1
and QF2, and you gave the total the YTD label, enter the following in the
Field: row of a new column:

=[QF2].[Amount] - [QF1].[YTD]

At this point, you still have a Select query. Press the exclamation point
to see the new quarterly numbers. To add these records to your data table,
select Query, Append. Select your data table from the dropdown list and
press OK. Access will make a "guess" as to what fields you'd like to write
this data to in the Append To: row. Check it over and make any corrections
you need. Save the query.

Notes:

Each month, after verifying the dummy table is empty, load your new YTD data
into it. If your organizations don't supply the data with the quarter, you
can easily add it to each record with a Modify query. Let me know if you
need any assistance with this. Then simply run the Append query to add the
new records.

Be aware that if you happen to run the Append query a second time, Access
will attempt to paste another set of records to your table. If the dummy
table is empty, it can not add any records. If it's still full with the
latest quarterly numbers (which equals the YTD), it would add a set of
records with the amount equal to zero. This wouldn't affect your totals, but
I think it would be prudent to make some provisions to limit who can run this
query.

HTH
Kevin Sprinkel

Thanks Sprinks I'll start to look at that. Bit of a novice so it may take a
while!!!
Thanks again
Tony

"Sprinks" wrote: