Duplicate records in table x times (1 Viewer)

Big Pat

Registered User.
Local time
Today, 12:09
Joined
Sep 29, 2004
Messages
555
Hi,

I've been given an Excel list with almost 700 rows and two columns.
A: name of school
B: number of pupils who met a certain criteria, could be anywhere from 1 to 100.

I have imported that list to Access and I will need to add more information for individual pupils.

So as a first step I would like to create a table that lists School A 30 times, school B 50 times, etc. until there's around 12,000 rows in the table.

Is this possible? Now that I have the data in Access, my field names are
Schoolname - text
Entries - Number

I don't know whether to tackle this with an append query, or with VBA. Either way, I'm well out of my depth!
 

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
Adding partial records for school only is not how you enter data in Access NOR should records be duplicated.
Additional records for each school would be added as required using a form or an append query (or SQL statement)
OR ...if you have 12000 complete records in e.g. Excel you can append these at one go
 

Big Pat

Registered User.
Local time
Today, 12:09
Joined
Sep 29, 2004
Messages
555
Adding partial records for school only is not how you enter data in Access NOR should records be duplicated.
Additional records for each school would be added as required using a form or an append query (or SQL statement)
OR ...if you have 12000 complete records in e.g. Excel you can append these at one go

Yeah, it's not normally how I'd go about things, but needs must. I'll do it manually if I have to.

I don't have 12,000 I have around 700, I need to create the 12000 as a first step and then I'll need to create several other fields for additional data coming from other sources. So they'll only temporarily be duplicated.

Are you able to help with an append query or VBA to accomplish this?
 

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
You need to add a primary key field so each record is unique. Suggest use auto number datatype.

Anyway, you can add multiple records by looping through using something like this.
Place in a standard module
Alter the loop number, table name and value to insert as appropriate

Code:
Sub PopulateTable()

Dim I As Integer

'this will insert 1000 records. Adjust as appropriate
For I = 1 to 1000
  CurrentDB.Execute "INSERT INTO YourTableName (SchoolName) VALUES (‘Required School Name‘);"
Next

End Sub

If you want to do this for several schools at once add a similar loop for each school in the same procedure

Hope that helps
 

Big Pat

Registered User.
Local time
Today, 12:09
Joined
Sep 29, 2004
Messages
555
Thank you, that's kind of starting to get there but it doesn't cut down the task by much. Maybe I didn't explain it properly.

...
Alter the loop number...

Rather than I = 1000, how do I make I refer to the "Entries" field? In some passes I'll need only a handful of records, whereas in others I'll need 50-100.

Code:
Sub PopulateTable()

Dim I As Integer

'this will insert 1000 records. Adjust as appropriate
For I = 1 to 1000
  CurrentDB.Execute "INSERT INTO YourTableName (SchoolName) VALUES (‘Required School Name‘);"
Next

End Sub


... add a similar loop for each school in the same procedure

But that would be approx 700 loops. I was really hoping for something nested like the following but I don't know the syntax

For School = 1 to TotalNumberOfSchools
..For Entry = 1 to TotalNumberOfEntries
....INSERT INTO MyTableName (NameOfTheSchool)
..Next Entry
Next School

Is that possible?
 

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
So you have 700 schools?
Yes its possible using nested loops but where is the name of each school and the total number of entries for each school stored?
 

Big Pat

Registered User.
Local time
Today, 12:09
Joined
Sep 29, 2004
Messages
555
Well, nearly 700, yes. I have a table that I imported from Access called ListOfSchools, with just two columns e.g.

Schoolname.....Entries
Glendale .........15
St John's ........50
Greenway........85

I'n not looking for a normalised database, rather trying to use Access to create a long list in Excel which will list Glendale 15 times, St. John's 50 times etc.

I've been given very poorly arranged/formatted data and I'm trying to get it into shape to be imported into a completely different system a few stages further down the line.

It may be that Access isn't the right tool for this but I'm not proficient enough in Excel to do it and I thought Access could do the trick.
 

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
Ah I see. That wasn't obvious to me before.
If you can upload a cut down version of your database with the ListOfSchools table together with the Access table you want populated, I'm happy to create a procedure to do this for you. You can then export the populated table to Excel if you wish
 

Big Pat

Registered User.
Local time
Today, 12:09
Joined
Sep 29, 2004
Messages
555
OK, try this. There's just ListOfSchools in this. You can just make another table called ExpandedList or something like that?

It's actually 643 records and I have just spotted a few blanks and zeroes, so feel free to ignore those. And the appaling punctuation!

Really appreciate this!!
 

Attachments

  • New Microsoft Access Database.accdb
    624 KB · Views: 83

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
Two issues
1. You have 48 duplicate records in your table but with different numbers of entries. See dupes query in the attached.
These dupes need to be fixed before the procedure is created. Most are paired results but at least one (St Patricks) has 3 records and another (Christchurch) has 5.
Should be entries be added to give one record with the total entries...or what?

Please provide a modified version with no dupes then make SchoolName the primary key field.

2. What fields do you want in the second table apart from school name?
It would make more sense for you to create that table.

The code can't just ignore the zeroes and null values. I'll either have to exclude them or delete them from the table if necessary

Its late here in the UK but I'll try and do this sometime tomorrow if you can do the above
 

Attachments

  • SchoolEntries.zip
    28.8 KB · Views: 73

Big Pat

Registered User.
Local time
Today, 12:09
Joined
Sep 29, 2004
Messages
555
It is late here in the UK. I'm in Warwickshire ;-)

I have no expectation of you doing this tonight, I'm about to crash anyway.

I queried the duplicates and apparently they're not REALLY. These are schools from all over the West Midlands region and there are some with the same name in different towns and cities. I have a separate list of codes provided by local authorities and I'll be doing lookups to match them and wil deal with the ambiguities then.

For now, can you ignore all that? Can the output table have a batch of Christchurch that's 46 records long, then somewhere further down the table, another batch of Christchurch with 24 records, etc?

Or will this whole thing not work without primary keys?
 

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
Better to avoid the issue at the start.

Recommend you add the codes provided by the LA as an extra field or fields. Then use an auto number PK field or if the LA codes are a unique code for each school E.G. 802 (for the LA) and 4140 (for the school), use that as the PK
 
Last edited:

MrHans

Registered User
Local time
Today, 13:09
Joined
Jul 27, 2015
Messages
147
Why not just make a recordset, get school name and number of pupils.
Add a loop that executes an INSERT statement for 1 to NrOfPupils times and go to the next record/school?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:09
Joined
Feb 19, 2002
Messages
42,970
I'm still completely at a loss as to the reason for creating x "empty" rows for a school. How EXACTLY is this going to save you ANY time? In order for the rows to be meaningful, you will need to enter something unique in each one. Where is that data coming from? That is the recordset that should be used to create "filled" rows.
 

Users who are viewing this thread

Top Bottom