Append Query Major Complication

[Tal]

So, I am creating a database for a charitable organization.
I have a donations table and then, for legal purposes a separate receipt
table.
I am using an append query to generate the records in the receipt table.
However, first I am searching for all open donations by each donor and
aggregating these into a single receipt.
However, the problem is re-connecting the receipt to the donation(s) after
the append. Is there any way that I can create a field in the append query
that includes a list of the primary keys of all relevant donations that I can
separate out later to create the "details" section of the receipt.
The problem that I am finding is that my query is pulling up old donations
that match the donor name, address and receipt to fields, which is what I
have linked between the tables. If I could link by donation that would be the
most secure method, but given the aggregation business, it is not so simple.

Any help is massively appreciated.
Cheers,
Talia

 

[Lynn Trapp]

So, I am creating a database for a charitable organization.
I have a donations table and then, for legal purposes a separate receipt
table.
I am using an append query to generate the records in the receipt table.
However, first I am searching for all open donations by each donor and
aggregating these into a single receipt.
However, the problem is re-connecting the receipt to the donation(s) after
the append. Is there any way that I can create a field in the append query
that includes a list of the primary keys of all relevant donations that I can
separate out later to create the "details" section of the receipt.
The problem that I am finding is that my query is pulling up old donations
that match the donor name, address and receipt to fields, which is what I
have linked between the tables. If I could link by donation that would be the
most secure method, but given the aggregation business, it is not so simple.

Any help is massively appreciated.
Cheers,
Talia

Talia,
It seems to me that the simplest way would be to add something like a
BatchID field to both the donations table and the receipts table. When
you"aggregate" the donations, you would need to update all records
with the new BatchID.

Lynn Trapp
www.ltcomputerdesigns.com

 

[Tal]

Hi Lynn,
I am wondering if you could be a bit more specific.
Can you append and update at the same time?
Cheers,
Talia

 

[Michel Walsh]

With Jet, you can append new record and update existing one with one query.
Assume you want to modified the Inventory with NewPrices:



UPDATE NewPrices LEFT JOIN Inventory
ON NewPrices.ItemID = Inventory.ItemID

SET Inventory.ItemID=NewPrices.ItemID,
Inventory.UnitPrice = NewPrices.UnitPrice


That won't work on MS SQL Server 2005 (or previous versions) though, just
with JET.


To see what is going on, imagine it is a SELECT statement, rather than an
UPDATE:


SELECT Inventory.ItemID, NewPrices.ItemID,
Inventory.UnitPrice, NewPrices.UnitPrice
FROM NewPrices LEFT JOIN Inventory
ON NewPrices.ItemID = Inventory.ItemID


Some values under fields of Inventory have nulls (those that are new items)
and some are not (those of existing items). After the UPDATE, see the
columns in the SELECT updated as

SET Inventory.ItemID = NewPrices.ItemID,
Inventory.UnitPrice = NewPrices.UnitPrice


that means the existing values will be updated, and the not existing
records, those with nulls, will now have value. So, what you want the UPDATE
to perform is well defined: update existing, append (to Inventory) new
records.



Vanderghast, Access MVP

 

[Tal]

Hi Michael,

That seems to be barking up the right tree in conjunction with Lynn's input
but let me see if I can spell it out and get your input.

It would be great if I could assign a number to the receipt and its
component donations at the time when I am appending the records from the
donations table to the receipt table.

For example:

Donation
1 Donor $75.00 Etc.
2. Same Donor $63.00 Etc.
3. Same Donor $15.00 Etc.
4. Some Other donor $60.00
5. Same Other donor $60.00

So I aggregate this to
Donor $153.00
Other Donor $120.00

And append it to the Receipt Table
Receipt 1 Donor $153.00
Receipt 2 Other Donor $120.00

It would be great if I could assign a number to each entry in the Donation
table at the same time, which could be coded to increment.
So Donation would be
1. Donor $75.00 Batch 1
2. Donor $63.00 Batch 1
3. Donor $15.00 Batch 1
4. Other Donor $60.00 Batch 2
5. Other Donor $60.00 Batch 2

It would be even better if the batch # and key of the Receipt Table could be
the same.

Thoughts? All help hugely appreciated.
Cheers,
Talia

 

[Michel Walsh]

It seems that the Batch number is similar to the Donor_ID (Donor 1 always
matched with Batch 1, Donor 2 always matched with Batch 2, ... ). Sounds
strange, but if it is so, why not using the Donor ID as batch number?



Vanderghast, Access MVP

 

[Tal]

Hi Michael,

The problem with that is our donors donate multiple times throughout the
year and receive multiple receipts, so that would only work the first time.
Also the receipt have to be sequential and our donors are not.

Thanks for the suggestion.
Cheers,
Talia

 

[Lord Kelvan]

what i think you are trying to achive is to summarise information in
the table dont do that use a query to do it

at the moment it seems you have the primary key of donation in the
recipt table what would be better to do is have no recipt table have a
donor talbe and a donation table in the donor one store the donor
details and in the donation table have the donation tables place a
forigen key from donor into donation to link all the donations to the
one donor and then if you want to summarice the values write a query
to do that for reporting

the query may be something like

select donorname, sum(donationammount)
from donor, donation
where donor.donorid = donation.donorid
group by donorname
order by donorname;

that query will summarise all the donation information for the donor
for the use of putting it all into one recipt

creating the table structure of {donation >|-----||- donor} will allow
you to have multiple donations per donor but as i said tables are not
designed to summarise data the are designed to store the raw values
for summarisation by a query

hope this helps

 

[Lord Kelvan]

i am getting mixed messages after a re read of the posts are you
trying to (1)have one recipt per donor or (2)multiple recipts per
donor or is it you are trying to (3) have multiple values one the one
recipt or is it (4) you send out multiple recipts but you just want
the values to get bigger and bigger and bigger as they donate more

if you want option 1 or 3 or 4 you can use the above method but for
option 3 you just remove the sum and group by form the query if you
want option 2 though you need to have three tables btu can you tell me
the legal reasond for havign a seperate recipt table because i see
that releationship between recipt and donation for option 2 as a 1 to
1 releationship meaning it can be just one table

regards
kelvan

 

[Tal]

Hello everyone,
Thank you so much for sticking with me through this.

Here is the situation.
I have donors who donate throughout the year. Some quite often.
I need to issue receipts every couple of weeks or so.
In that case, a donor can have multiple receipts, each comprising multiple
donations.
For legal reasons, I need to store the receipts in a separate table, for
reporting, security and sequential reasons.
However, when I use an append query to summarize and create receipts from
any open donations, meaning any donations that haven't been receipted yet, I
lose the relationship between the receipt and the specific donations. So, it
becomes a matter of marking the donations as receipted and hoping for the
best. Ideally I would like the primary key of the donations to travel with
the summarized data to the receipt table, so that I can re-link them after
the fact. However, since there can be a one-to-many relationship with the
donations and the receipts. So how would I put multiple primary keys into one
field. Meaning, I don't know how to tell the donation which receipt it is
part of and tell the receipts which donations make it up, because the sum
function requires that all data be grouped.
An idea would be to give the receipts and the donations a batch number at
the time when the data is being summarized and appended, but I don't know how
to do this.

Again, thank you so much everyone for your help.
Cheers,
Talia

 

[Tal]

In case anyone is reading this. I returned back to Lynn's initial suggestion
and created an update query that time stamps the donations and the receipts
at the moment they are generated, which provides a more unique and secure
mechanism to match them back up later on.
Thanks to everyone for your help. It was a combination of ideas that led me
to my solution.
Cheers,
Talia

 

[Lord Kelvan]

you shouldnt put summary data in a table that what queries are for in
your donation table you can put a couple of fields in such as recipt
complete and reciptid and in your recipt table you can put some basic
information in but dont put a summary value just use a query to get it
putting summary values means you are destroying the raw data can you
please tell me what each of your current tables are and their fields

Regards
kelvan