Query results lopsided!

[Guest]

Good evening everyone,

I have two kinds of donations, cash and in-kind. Not wanting to have two
lists of contributors, as some contributors make both kinds, I created one
table_contributors. However, when I tried to create a query with the date,
cash, in-kind, and contributors as the field names, I returned the correct
information on the cash side (we had more cash contributions than in-kind)
but on the side of the in-kind, I had the same contributor’s name listed
over, and over, again.

If I create two separate contributor tables, the query will work okay, but
it makes me have to manually enter the information. On one of my forms, I
have a subform_contributors, where I can up-date the list. Is there a way
that information can be stored in both a tbl_cashcontributors and
tbl_in-kindcontributors?

And, can someone tell me why, I can put my main table in a query and its
reference tables, pulling down the needed fieldnames from the main table and
the fieldnames I need from the reference tables (I did this so I would be
able to have text boxes in my report rather than combo boxes). Everything
works great. The correct information is in my report. However, when I tried
the same technique for the next report, all but one of the fieldnames from my
reference tables allow my query to return the correct information. But, when
I add the fieldname from my tbl_contributors, none of my sales income shows.
Any ideas?

Hope someone can help me out of this jam, too. And, most importantly, God
Bless all of you who take the time to help us out; thank you, k

 

[tina]

do i understand correctly that you have a field for cash, and another field
for in-kind, in your contributions table? if so, you're storing data in
fieldnames ("cash" and "in-kind"), which violates normalization principles
and, not incidentally, causes or at least contributes to your problem.

please post your tables/ relationships structure in an easy-to-read format,
such as

TableName
FieldName (primary key)
FieldName
FieldName

TableName2
FieldName2 (primary key)
FieldName2
FieldName2 (foreign key from <other tablename>)

TableName.FieldName 1:n TableName2.FieldName2
(1:n means one-to-many relationship)

hth

 

[Guest]

You are absolutely right to have only one Contributors table. Contributors,
whether cash or in-kind (or both) are a single entity type with the same
attributes, name, address etc. Each column of the Contributors table models
an attribute of that entity type which is specific to it and it alone; in the
jargon of the relational model its said to be functionally dependent solely
on the whole of the key of the table. This means that for the value of the
key there can be only one value of each non-key attribute, e.g. for
contributor 42 the first name will always be John and the last name Doe, so
inconsistencies are eliminated. As names can be duplicated use a unique
numeric key, e.g. a ContributorID autonumber column.

Contributions are another entity type, so these are represented by another
table which will have attributes, and therefore columns in the Contributions
table, such as ContributionDate etc. It will also have a ContributorID column
which, as a foreign key, references the primary key of Contributors, thus
identifying the contributor for each Contributions record. The key of this
table can conveniently be an autonumber ContributionID.

So far everything is cut and dried, but how you develop the model from here
allows for alternative approaches. Which way you go depends on the other
attributes of the Contributions entity type. The alternatives are:


1. To regard ContributionType as an attribute of Contributions with
possible values 'Cash' and 'In-kind' as Tina suggested, and therefore a
column in the Contributions. This, however does depend on the cash and
in-kind contributions having the same attribute types. This is fine for
ContibutionDate for instance, but cash contributions will presumably have an
attribute type Amount of currency data type, but this does not seem an
appropriate attribute type of in-kind contributions which will presumably be
quantified in some other way (you haven't actually said what forms the
in-kind contributions might take so its difficult for me to say more on this
point). What many people would do here is have separate columns in the
Contributions table for the currency amount of a cash contribution and
however the in-kind contributions are quantified. That's not a good design,
however, as it means that for each contribution one of these columns will
have no value, i.e. be Null. Nulls are problematical because they are
semantically ambiguous, not being values. A currency column should never be
Null, but have a default value of zero and a Required property of True,
otherwise calculations will be affected as any arithmetical expression
involving a Null results in a Null regardless of the other values. Despite
the deficiencies of this model it would work and a query as the basis for a
report is very simple, e.g.

SELECT Contributions.ContributorID,
FirstName, LastName, ContributionDate,
ContributionType, Amount, InKindContribution
FROM Contributors INNER JOIN Contributions
ON Contributions.ContributorID – Contributions.ContributorID;

A report could be grouped first on ContributorID, then on ContributionType
and sorted by ContributionDate for instance, with the Contributor details in
the first group header, the ContributionType in the second and the dates,
amounts and in kind contributions in the detail (one of the latter would
always be zero or Null per detail row of course). The data could be
aggregated e.g. counted or summed in either or both group footers and/or in
the report footer.

2. The alternative model, and one which I think is more correct in terms of
the relational model is to see CashContributions and CashContributions as
sub-types of the type Contributions. Sub-types are characterized by sharing
the attributes of their (super) type e.g. ContibutionDate, ContributorID, but
not those of other sub-types, So CashContributions has an Amount attribute,
but InKindContributions has an InKindContribution attribute of a different
data type, e.g. a text description. With this model the CashContributions
and CashContributions tables would have a ContributionID primary key, but
this would also be a foreign key referencing the primary key of
Contributions, i.e. the relationship is one-to-one. For these tables
modelling the sub-types the primary key cannot be an autonumber of course,
but would be a straightforward long integer number data type.

With this second model a report would not be based on a single query but
would be made up of a main parent report based on Contributors, with two
subreports based on queries, one of which would join Contributions and
CashContributions, and the other joining Contributions and
InKindContributions. The subreports would be linked to the parent report on
ContributorID.

Which of these alternative models you choose depends on how far the
attributes of the cash contributions and in-kind contributions entity type
differ. The first model is deficient, but if the attribute types don't
differ radically might not be unacceptably so, and is easier to implement. I
hope I've outlined the underlying principles sufficiently for you to decide
on which is appropriate in your case.

Ken Sheridan
Stafford, England

 

[Jamie Collins]

A currency column should never be
Null, but have a default value of zero and a Required property of True

I too try to avoid nullable values (my only nullable ones are
subatomic DATETIME columns e.g. start- and end date pair with end_date
IS NULL as the 'magic date' to signify the current period).

But what if the OP wanted to record that a contribution is known to
have been received from a known source on a known date but that the
amount is currently unknown or is currently undisclosed or is
confidential or similar?

Personally, I'd use distinct tables to model such contributions if
required and such tables would (obviously) contain no column for
amount. So while I agree with your "A currency column should never be
Null[able]" it would be wrong to equate a 0.0000 CURRENCY value with
NULL (not that you necessarily are, I recognise).

If one advocates widespread use of nullable columns (and many in these
groups do -- I understand it fits well with the 'always connected,
always writing' bound Forms approach) then I see no reason why
CURRENCY would be an exception.

Jamie.

--

 

[Guest]

Tina, Ken, and Jamie,

Good evening, I was sure I'd already given you a list of my tables, etc. I
wonder where I put them????????? And after reading Ken and Jamie's posts, I
am not at all sure I've done anything correctly.

I have two main tables: expenses, and income
In my Tbl_Income, I have
ID-AutoNumber-PK
Bank - Number (linked with Bank Table via query as are each of the other
Number fields)
RefDate - Number
IncDate- Date/Time
IncType - Number
SS - Currency (which is set to standard with 2 decimal places)
More - C
Less - C
Contributor - Number
ACD - C
I-KContributor - Number
Contribution - Text
I-KV - C
OS - C
OI - C
BldFnd - C

Tbl_Expenses
CLID-AutoNumber -PK
RefDate - Number
Bank - Number
ACkNo - Text
ACkDate - Date/Time
Bsn - Numer
Who/WhatFor - Text
AClCkAmt - Currency
ANon-ClCkAmt - Currency
Whoes - Number
Description - Number

For each of the fields above that are marked "Number" I have a table by that
name. The only thing I have in each of those tables are the PK which is an
AutoNumber and a list of, for example, contributors. I do not have a
reference date or any other information in those tables.

I have created a query to join each of the number fields to the PK field in
the individual tables which has created a many to one relationship. For the
reports In-Kind Donations and Cash donations, I create a query in which I
pull the reference date, the contributorID, what was contributed $60.00 from
Tbl_Income. I then pull "Contributors" from the Contributors table. I found
that I can make a Sub_Report for the In-Kind Contributions (cloths, shelves,
etc) in the same manner. I do not understand why it keeps asking me for the
opening paramenter over and over again. (Any ideas?)


I enter, and edit information via an income form and an expense form. (I
still have a problem with the subforms ie, contributor, updating immediately
so that I have that option for entry on the income/expense form)

For my Rpt_FinancialStatement, I have Qry_FinancialStatement as the Parent
Report, which contains the Reference Month, Year, and the beginning Balance.
Also, in the Report Footer I want to put the Ending Balance. I have
SumQry_Income, which shows monthly totals of each of the income fields, as
the query for my first sub-report. I have Sum!Qry_Expense, which shows
monthly totals of each of the expenses. To obtain monthly totals for each
catagory, ie "Client Utilities", I had to use the RefDate rather than
the[Between [Enter Start Date] and [Enter End Date] (Sorry, forgot I'd posted
this as a problem.)

I hope you all are checking back and tell me if I need to completely start
over, I really hope not. I've been almost three months trying to remember,
with help what I've completed. Thanks for your help, and I'm waiting to hear
from you.

Christ's blessings,

k