APPENDING RECORDS

[Rjconrep]

I have an Access table that has the following fields;
FacID Loc # Inn Brand TableTents
3864 1179 RWCEX.pdf HIEX 75
5749 7629 SFOPF.pdf HEXS 75
7258 4648 YRMAB.pdf HIEX 75
I need the records in the table to repeat based on the "TableTents" field.
This is what I have written so far. Not sure how to continue.
Sub ExitExample()
Dim dbsSampeFile As Database
Dim db As DAO.Database
Dim Tbls As DAO.TableDefs
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim nextTbl As Label
Set db = CurrentDb
Dim a As String
Counter = 0
myNum = Tabletents
'a = "SELECT * FROM [" & tname & "];"
' MsgBox a

Set rs1 = db.OpenRecordset(a)
Do While Not rs1.EOF

Counter = Counter + 1
If Counter <= Tabletents Then



MsgBox "The loop made " & Counter & " repetitions."
End Sub

 

[Remou]

You can do this quite easily with a table of numbers and a query. The table of numbers should contain 1 to the maximum value of tents. The query would be something like:

strSQL="INERT INTO t ( [Loc #],Inn,Brand,TableTents ) " _
& "SELECT t.[Loc #],t.Inn,t.Brand,t.TableTents " _
& "FROM t, tblNumbers " _
& "WHERE tblNumbers.Num<" & Me.txtTableTents 

CurrentDB.Execute strSQL

 

[Rjconrep]

So would i use an Update Query? I am not sure I understand....sorry.

 

[HarleyQuinn]

An UPDATE query would change data in your table, the APPEND query that Remou is demonstrating will add data into your table (as you require).

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Rjconrep]

Here is what I now have;

Insert INTO Final ( ID,FacID,[Loc #],[Inn Code],Brand,Rooms,TableTents)
SELECT Samples ( ID,FacID,[Loc #],[Inn Code],Brand,Rooms,TableTents)
FROM Samples, TableNumbers
WHERE Sum1.TableNumbers< Samples.TableTents;

This is giving me the following error;
"number of query values and destination fields are not the same"

 

[HarleyQuinn]

Should this

Sum1.TableNumbers

be

TableNumbers.Sum1

Also (and more importantly) your SELECT statement should be:

SELECT ID,FacID,[Loc #],[Inn Code],Brand,Rooms,TableTents
FROM Samples, TableNumbers
WHERE Sum1.TableNumbers< Samples.TableTents;

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Rjconrep]

Okay, I think I am getting closer.
This is the query that I ran;
Insert INTO Final ( ID,FacID,[Loc #],[Inn Code],Brand,Rooms,TableTents)
SELECT ID,FacID,[Loc #],[Inn Code],Brand,Rooms,TableTents
FROM Samples, Sum1
WHERE Sum1.TableNumbers= Samples.TableTents;
Problem is that it didn't repeat any records. If TableTents = "75" then I need that record repeated in the table 75 times.

 

[Remou]

I will just add that the above query will only take account of the first value of sample, as far as I know, which is why I suggested referencing a control on the form. The general idea is to run a cartesian query, so you should not need to sum anything.

 

[HarleyQuinn]

Yes, the query you've got at the moment is joining to the matching record in the number table, so you will only get the one record back.

If you use the syntax for the join Remou and I posted (albeit with a slight modification so you get the same number of records as TableTents shows) it will work.

This looks a bit more like it:

Insert INTO Final ( ID,FacID,[Loc #],[Inn Code],Brand,Rooms,TableTents) 
SELECT ID,FacID,[Loc #],[Inn Code],Brand,Rooms,TableTents
FROM Samples, Sum1
WHERE Sum1.TableNumbers[red]<=[/red]Samples.TableTents;

Does that do what you need?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

I think you are typing too quick

Would that be:

TableNumbers.NameOfField<=TableTents.Samples

 

[HarleyQuinn]

You too...

TableNumbers.NameOfField<=Samples.TableTents

To be honest they've changed in each query so I'm not sure which one of Sum1 and TableNumbers is the table and which the field anymore

But the code I posted was at least right with the assumjption of Sum1 being the table

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Rjconrep]

You were right Harley. I can see that my naming convention was not the best here.
When I run this query;
INSERT INTO Final ( ID, FacID, [Loc #], [Inn Code], Brand, Rooms, TableTents )
SELECT ID, FacID, [Loc #], [Inn Code], Brand, Rooms, TableTents
FROM Samples, Sum1
WHERE Sum1.TableNumbers<=Samples.TableTents;
It gives me a very random number of records. Instead of the first record in the table repeating 75 times it only repeats 2 times.

 

[HarleyQuinn]

Hmm, works fine for me, I copied the first two lines of data you posted and got 150 records in my new table when I ran it. No problems there.

Turn SetWarnings on and it will tell you how many records it's appending to the table (this should be the number you're expecting ), that's your first start.

Also, what does the data look like in Sum1? In my test I used 100 rows with values 1 - 100 and it works as expected.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Rjconrep]

You guys are awesome are the man!!!
Harley, I had my Sum1 Table set up incorrectly. I fixed it, ran it and it worked perfectly.
thanks a bunch!!!

 

[HarleyQuinn]

Glad to help

I've given Remou a star using the "Thank Remou for this valuable post!" link for his part in sorting this out for you.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

Rjconrep
I have given HarleyQuinn a star for stick-with-it-ness and better explanations. We are a happy little group.

 

[HarleyQuinn]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.