How do I loop a query?

[Baldeagle]

I have a Macro that runs a number of queries in sequence which work on data from a monthly .csv report. This report lists donors who have used our website to make a donation during the last month. One query identifies those donors who already are on our database and links them to their existing Donor Number. But the one that I am having trouble with is the next one which identifies new donors. The query is built on 2 queries. The one identifies the first unused Donor Number in our Donors Table and the other selects those records from the .csv report which don't have an existing record in our Donors Table. The query appends the new donors' details (including the next Donor Number) to the Donors Table. My problem is that the query tries to give the same number to all of the new Donors listed in the monthly .csv report and because the Donor Number field is a Primary Key this is not permitted.

How can I get this query to go looking for the next unused Donor Number for each new donor? Is there any way that this can be done in my Macro? Or do I need to use Visual Basic - if so I will need help with the wording as I am not proficient in this area (I can cut & paste!!).

I use the design mode to create my queries but the SQL version of it is as follows -



INSERT INTO Donors ( [Donor Number], Title, [Christian Names], Surname, [Address 1], [Address 2], [Town/City], Postcode, [EMail Address], [Use Email?], [Gift Aid Declaration?], Code )
SELECT [First Unused Donor Number].MinOfNumber, [Select New Donors from Website Monthly Reports].Title, [Select New Donors from Website Monthly Reports].[Christian Names], [Select New Donors from Website Monthly Reports].Surname, [Select New Donors from Website Monthly Reports].[Address 1], [Select New Donors from Website Monthly Reports].[Address 2], [Select New Donors from Website Monthly Reports].[Town/City], [Select New Donors from Website Monthly Reports].Postcode, [Select New Donors from Website Monthly Reports].Email, [Select New Donors from Website Monthly Reports].[Use Email?], [Select New Donors from Website Monthly Reports].[Gift Aid Declaration?], IIf([Gift Aid Declaration?]="Yes","GJ","DJ") AS Exp1
FROM [First Unused Donor Number], [Select New Donors from Website Monthly Reports];

Any guidance would be much appreciated.

[June7]

Where are these unused donor numbers coming from?

[Baldeagle]

The first of the 2 queries (First unused donor numbers) looks at the Donors table and looks for the lowest unused number - after 5 years of a donor not making a donation their details are deleted and so random numbers become available for reallocation. The first unused number at present in the Donors table is 185 and the second is 217..... Trust this helps you understand my problem.

I'm off to bed now but I look forward to hearing from you again in the morning!!

[June7]

Is that an autonumber field? How do you know the number is available? You delete data and leave blank record in table? Then what you really have to do is update an existing record, not insert a new one. Won't be able to do this on a mass import of data in a single query. You would be better off just letting those old numbers go away or don't even bother deleting donors. If you don't want those inactive donor records to show in forms or reports, have a field to tag them as inactive and filter them out.

If you delete donor data, can't do accurate historical analysis.

[Baldeagle]

Thanks for your reply. The following are the answers to your questions -
1. The Donor Number field is not Auto number, just number.
2. My first query compares a list of all consecutive numbers from 1 with the numbers used in the Donors table and selects the minimum.
3. When a record in the Donors table is deleted everything is gone - there is nothing left. So there are no blank records.
4. So I do want to add a new record and not update an existing one.
5. We are content to forget about donors who haven't donated for 5 years as far as our live database is concerned. We do have historical databases created at the end of each year which allows us to explore donations from deleted donors.

My logic suggests that if I could find a way to repeat the query for the number of times that there are new donors each month then each time the query was run it would find the first unused donor number and would assign that number to each new donor in turn and would enter the details into the Donors table. Can you help me to do that?

I look forward to hearing from you.

[June7]

Have to loop through the CSV data and write individual record to table with the INSERT action.

Options to read the CSV file:

1. link or import CSV, open recordset in VBA, loop through the recordset

2. open the CSV as text object in VBA, read one line at a time and parse the string

Grab the next available number with code - DLookup or open a recordset that returns a single record with the available number and read the value from the recordset field.

[Baldeagle]

Thanks June for your response. But the output of the append query that I quoted in my original post (which is called 'Add New Donors' details to Donors Table') has all the information (drawn from the .csv and other tables in the database) that is required to append to the Donors table. I just need to find a way to repeat this query (either from within my Macro or by using Visual Basic - for which I would require something that I can cut and paste) for as many times as there are records in the output of the query. The first run of the query puts the details for only the first new donor into the Donors table (because the Donors table won't permit a second entry with the same donor number). So by repeating the query it will add the details for all the new donors because it will pick up a new unused number each time and will have noted that the first new donors details have already been entered so it will go to the second new entry and so on...

Can you guide me on how I can repeat the query the required number of times from within my Marco? Or if Visual Basic is required can you give me the appropriate wording to use as I am not able to use VB myself?

[June7]

My point is the append query you now have won't work to add all the CSV data in one shot. Can't loop within the query. A single record must be saved with available number and by saving that record the next available number determined by [First Unused Donor Number] will be different. Pseudocode logic:

1. open recordset of linked/imported CSV or open text object in VBA

2. grab the next available number by DLookup on [First Unused Donor Number] or open [First Unused Donor Number] as a recordset in VBA

3. save data from CSV record along with the available number into new record

4. move to next record of CSV

5. repeat from 2

There are code examples all over the web and in threads throughout this forum for each of those steps. Something like:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM linkedCSV")
While Not rs.EOF
CurrentDb.Execute "INSERT INTO Donors ([Donor Number], Title, [Christian Names], Surname, [Address 1], [Address 2], [Town/City], Postcode, [EMail Address], [Use Email?], [Gift Aid Declaration?], Code) VALUES(" & DLookup("[MinOfNumber", "[First Unused Donor Number]") & ", '" & rs!Title & "', '" & rs![Christian Names] & "', '" & rs!Surname & "', '" & rs![Address 1] & "', '" & rs![Address 2] & "', '" & rs![Town/City] & "', '" & rs!Postcode & "', '" & rs![EMail Address] & "', " & rs![Use Email?] & ", " & rs![Gift Aid Declaration?] & ", '" & rs!Code & "'")
rs.MoveNext
Wend

That assumes [Use Email?] and [Gift Aid Declaration?] are Yes/No fields and data in the CSV is either 0 or -1.

BTW, should not use spaces or special characters/punctuation (underscore is exception) in names.

[Baldeagle]

Thanks for your latest guidance. I will get into this in the morning and see if I can work my way through your suggestions. I have to confess that Visual Basic is 'double Dutch' to me but I will use your code and fit it into my database and see how I get on. I will let you know the result!!

[Baldeagle]

Morning June. Thanks again for your patience with me!

I have got it working by picking up your coding about 'While Not' etc and have incorporated my various queries into Visual Basic as follows -

Private Sub Command60_Click()
Dim rs As DAO.Recordset
DoCmd.SetWarnings False
DoCmd.OpenQuery "New Bank Details from Website Monthly Reports", acViewNormal, acReadOnly
DoCmd.OpenQuery "Add All New References from Website Monthly Reports", acViewNormal, acReadOnly
DoCmd.OpenQuery "Make Table - New Donors", acViewNormal, acReadOnly
Set rs = CurrentDb.OpenRecordset("New Donors")
While Not rs.EOF
DoCmd.OpenQuery "Add New Donors' details to Donors Table", acViewNormal, acReadOnly
DoCmd.OpenQuery "Update Table Donor Details for Credits with Donor Number", acViewNormal, acReadOnly
rs.MoveNext
Wend
DoCmd.OpenQuery "Email Details from Website Monthly Reports", acViewNormal, acReadOnly
DoCmd.OpenQuery "Update Donors Table with Email Addresses from Web Reports", acViewNormal, acReadOnly
DoCmd.DeleteObject acTable, "Email Details from Web Reports"
DoCmd.Close acTable, "New Donors", acSaveYes
DoCmd.DeleteObject acTable, "New Donors"
DoCmd.SetWarnings True
End Sub

Everything works the way I want it to with just one very minor hitch at the end. The penultimate line throws up an error - it can't delete the table 'New Donors' because it is being used by some other process. I don't know what is using it!! I thought I could overcome the problem by closing it before deleting it. But that hasn't solved the problem. Can you help me on this final point?

[June7]

Might have to close the recordset first.

rs.Close

[Baldeagle]

Morning June - Brilliant as ever!! Thanks again for all your guidance.