Full Version: Create Multiple Records In A Table Based On Info In Single Record
UtterAccess Forums > Microsoft® Access > Access Forms
Alx420
Hello,

For starters i am not a programmer. I know basics so not sure this is possible. I have information that gives me a start date an end date number of services and frequency of said service. e.g. Start Date 01/01/19 End date 06/25/19 Nbr services 26, Frequency Weekly. I would like to create a table that has a record for each of the service with the corresponding date. I am early in the process so none of my tables are set and i want to make sure i create a good base structure.

Ultimately the goal is to create individual invoices based on contract information.

Any help would be greatly appreciatted

thank you
Alex

Doug Steele
welcome2UA.gif

It's certainly possible, but why would you want multiple records for the same thing? I'd say it's seldom (if ever) a good idea.

As I said, it's possible, but you'll have to use code to do it: Access has nothing built in that supports this. Example 2 in my article Uses for Cartesian Products in MS Access Rather than the simple table containing digits from 0 to 9, you'd need a calendar table. You could limit which rows are returned from the calendar table, and join that (using a Cartesian Product) with the table containing the current service of interest.
Alx420

In regards to multiple records, i may have miss-spoke. i get contract info that tells me how many services, when they start, and the frequency (monthly, weekly, etc) i then need to create an invoice for each service with the date of the contracted service. So each record is unique.

When you said to have a Calendar table it sparked an idea. I basically created a table with the date and frequency, linked the frequency to the contract tbl and limited the results to between the start and end date. And to make sure i don't get unexpected duplicate info, summing my Invoice table should always equal my contract table.

Thanks for your help

Alex


TheMathGuy
I have a similar issue. I am trying to keep attendance records for students. I would like to be able to create an attendance record for each student registered in a class for each session of that class. My idea was to have a form that looks something like this:

ClassID: ___ Date: ___
Comments: _____________________

<Students Subform>
Name: ________ Attended: (y/n) Comments: ______________
Name: ________ Attended: (y/n) Comments: ______________
.
.
.

Where the subform would automatically generate records in an attendance table and fill in names based on the students in the class. The instructor could then record whether the student attended or not and any comments about the particular student.
I've been trying to find something viable, but I am a relative beginner as well. Any help would be much appreciated.

Thank you.
Doug Steele
TheMathGuy: I'd recommend posting this as a new question, rather than trying to add onto someone else's question.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.