Hi Ray
In addition, I suggest you write the ID (primary key) of the related Editions table record into each Bookings record that you create. That way, if the source record changes then you can find the related Bookings records to modify or delete them.
Warmly,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, 5 May 2018 05:14
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Creating the right number of records - loop logic
Maybe this will get you started.
Here is a simple sub routine that will insert records into a table named tblMagEditions.
Public Sub FillEditions(intEditions As Integer, intStart As Integer, lngCampaign As Long)
Dim db As DAO.Database
Dim strSQL As String
Dim strMonth As String
Dim x As Integer
Set db = CurrentDb
For x = intStart To intStart + intEditions - 1
strMonth = Format(DateSerial(Year(Date), x, 1), "mmm")
strSQL = "INSERT INTO tblMagEditions(CampID, MagEdtn) " _
& "VALUES(" & lngCampaign & ", '" & strMonth & "')"
db.Execute strSQL, dbSeeChanges
Next
Set db = Nothing
End Sub
If you can upload a small sample database to our Files>_Assistance Needed folder we can give yu more direct help.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
My nothing-to-do-with-Access blog
---In MS_Access_Professionals@yahoogroups.com, <rayfrew@gmail.com> wrote :
Dear Professionals
If you could give me a nudge in the right direction, it would be most appreciated.
I'm trying to create a database that reserves space in editions of magazines.
For example:
Aspen db Ltd wants a Summer campaign of adverts in 3 magazines to run for 4 editions starting with the June issue.
My database structure is:
Customer –< many Campaigns –< many Campaign / Magazine Editions
From the other end Magazine --< Magazine Editions --< Campaign/Magazine Editions
A veritable many to many structure.
What I want to do is walk through the Campaign / Magazine Editions table and create the right number of Bookings. If the data in Campaign / Magazine Editions was:
Rec1 Aspen db Ltd Full Page ad in Astley Mag for 4 editions starting in June
Rec2 Aspen db Ltd Full Page ad in Beaumont Mag for 4 editions starting in June
Rec3 Aspen db Ltd Full Page ad in Sharples Mag for 2 editions starting in September
I want to hold Rec1 and create 4 bookings starting in June (June, July, Aug, Sept). Then move to Rec2 and create 4 bookings starting in June (June, July, Aug, Sept). Finally move to Rec3 and create 2 bookings starting in September (Sept, Oct). I expect to create recordsets to walk through the data and have loops within loops to create the right number of bookings but I'm a bit flummoxed about the logic. Any thoughts welcomed.
Many thanks
Ray
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar