Select Distinct Problem

[johnhanis]

I'm using a Visual Basic front end with an SQL query to select some
data from a MS Access database. I have a table named Tithes with
Columns of

TitheDate
Tither No
Total
Tithes
Faith Promise
Building Fund

I have the following query that returns the sum of each column grouped
by year.

SELECT DISTINCTROW Format$([Tithes].[TitheDate],'yyyy') AS [Year],
Sum(Tithes.Tota l) AS Total, Sum(Tithes.Tith es) AS Tithes,
Sum(Tithes.[Faith Promise]) AS [Faith Promise], Sum(Tithes.[Building
Fund]) AS [Building Fund]
FROM Tithes
GROUP BY Format$([Tithes].[TitheDate],'yyyy'),
Year([Tithes].[TitheDate]);

There will be times when I have to add columns to the Tithes table. How
do I update the query to include the added columns?

[pietlinden]

jo*******@gmail .com wrote:

I'm using a Visual Basic front end with an SQL query to select some
data from a MS Access database. I have a table named Tithes with
Columns of

TitheDate
Tither No
Total
Tithes
Faith Promise
Building Fund

I have the following query that returns the sum of each column grouped
by year.

SELECT DISTINCTROW Format$([Tithes].[TitheDate],'yyyy') AS [Year],
Sum(Tithes.Tota l) AS Total, Sum(Tithes.Tith es) AS Tithes,
Sum(Tithes.[Faith Promise]) AS [Faith Promise], Sum(Tithes.[Building
Fund]) AS [Building Fund]
FROM Tithes
GROUP BY Format$([Tithes].[TitheDate],'yyyy'),
Year([Tithes].[TitheDate]);

There will be times when I have to add columns to the Tithes table. How
do I update the query to include the added columns?

Looks like your first problem is an incorrect table design. You should
not have to change the structure of your database over time unless you
start adding new kinds of information.

Could you not use a structure like:

tblTithe(
TitheID,
TitheDate,
Tither_No,
Amount,
Fund)

New Tithers would go in a table (FirstName, LastName...)
Getting how much each person tithed to each account - just use a
summary query.

Group by Tither_no, Fund, Year(TitheDate)

If you want the total amount tithed per year, you just do a totals
query on the Tithe table, sum(Amount). IF you want it by week,
calculate the weekNumber and group by that... This structure should
prove a lot more flexible over time. And easier to query... no PITA
unions and all that nonsense.

[Larry Daugherty]

If there are times when you need to add tables to your Tithes table
then you don't have your data designed correctly. It sounds like
"Excel Think" but doesn't look like it. What are the occasional
"added columns"? If you ever need to add columns then you always need
to leave them there.

In a relational database, entities in the application are entered into
tables. The tables are designed to conform to at least 3rd Normal
form. If that sounds like gobbledegook I understand. But, it's true.

In your Tithes table what do the fields below Tither No hold? If
total is the sum of the fields below it, get rid of it. One of the
relational rules is that nothing in any field in a record depends or
is derived from any other part of the record.

Just modify your query to include the other columns when needed,
otherwise leave them out.

Otherwise this sounds like a VB issue of where and how to store
queries, not Access. In Access you can embed your queries in the
Forms and Reports that use them or create named Queries and save them
as objects in the database. or write SQL fragments in code and
concatenate them into a string and execute that string as a SQL
statement.

HTH
--
-Larry-
--

<jo*******@gmai l.comwrote in message
news:11******** **************@ d34g2000cwd.goo glegroups.com.. .

I'm using a Visual Basic front end with an SQL query to select some
data from a MS Access database. I have a table named Tithes with
Columns of

TitheDate
Tither No
Total
Tithes
Faith Promise
Building Fund

I have the following query that returns the sum of each column

grouped

by year.

SELECT DISTINCTROW Format$([Tithes].[TitheDate],'yyyy') AS [Year],
Sum(Tithes.Tota l) AS Total, Sum(Tithes.Tith es) AS Tithes,
Sum(Tithes.[Faith Promise]) AS [Faith Promise], Sum(Tithes.[Building
Fund]) AS [Building Fund]
FROM Tithes
GROUP BY Format$([Tithes].[TitheDate],'yyyy'),
Year([Tithes].[TitheDate]);

There will be times when I have to add columns to the Tithes table.

How

do I update the query to include the added columns?