473,320 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Select Distinct Problem

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.Total) AS Total, Sum(Tithes.Tithes) 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?

Sep 20 '06 #1
2 2084
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.Total) AS Total, Sum(Tithes.Tithes) 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.

Sep 20 '06 #2
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*******@gmail.comwrote in message
news:11**********************@d34g2000cwd.googlegr oups.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.Total) AS Total, Sum(Tithes.Tithes) 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?

Sep 20 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Colman | last post by:
Howdy all! I guess I'm a newbie, because I am stumped (or maybe just too durned tired). Here's what I got... CREATE TABLE `nodecat_map` ( `nodecat_id` mediumint(8) unsigned NOT NULL...
5
by: Martin Feuersteiner | last post by:
Dear Group I'm having trouble with the clause below. I would like to select only records with a distinct TransactionDate but somehow it still lists duplicates. I need to select the...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
9
by: Kelvin | last post by:
Okay so this is baking my noodle. I want to select all the attritbutes/fields from a table but then to excluded any row in which a single attributes data has been duplicated. I.E. Here's my...
6
by: John M | last post by:
Hi, The line below is used to feed a combobox. (It is from a database which is used to log pupil behaviour!) The 'incidents' table contains a list of students who have been involved in incidents....
18
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are...
2
by: ray well | last post by:
i have to extract info from a legacy access database, which i can't alter, or run APPEND or UPDATE quries against. i can only use SELECT statments to extract what i need. the database has...
6
by: Bob Stearns | last post by:
I am getting duplicate rows back from a select distinct statement of the form: SELECT DISTINCT 'jhough', '000111', t0.bhid FROM (SELECT lots of good stuff) t0 LEFT OUTER JOIN another_table ...
6
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214,...
5
by: Daniel Wetzler | last post by:
Dear MSSQL experts, I use MSSQL 2000 and encountered a strange problem wqhile I tried to use a select into statement . If I perform the command command below I get only one dataset which has...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.