Append Query or other solution

[ste4en]

I have some data as shown in the following example:

Fiat 4
Ford 2
Porche 1

I need to make:
Fiat 1
Fiat 1
Fiat 1
Fiat 1
Ford 1
Ford 1
Porche 1

How can I achieve this, I am importing data from a spreadsheet; appending it to a data table.
Thanks
Ste4en

 

[BuilderSpec]

When you import the data , do you end up with a table in the db that has

Field1 Field2
Fiat 4
Ford 2

etc ?


Hope this helps!

Regards

BuilderSpec

 

[ste4en]

Yes the import results in the data looking like this

Field1 Field2
Fiat 4
Ford 2

Field1 Field2
Fiat 1
Fiat 1
Fiat 1
Fiat 1
Ford 1
Ford 1

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BuilderSpec]

err. that doesn't make sense.. which is it after you have imported it ?

Hope this helps!

Regards

BuilderSpec

 

[ste4en]

What I have (after importing)

Field1 Field2
Fiat 4
Ford 2

What I need

Field1 Field2
Fiat 1
Fiat 1
Fiat 1
Fiat 1
Ford 1
Ford 1

I have not tried anything in Access, in Excel I have been adding rows and manually modifying the spreadsheet prior to import. I was hoping to automate but I dont know where top start or if it is possible.

Thanks

 

[Golom]

I think you will need to do this in code

Dim rs As DAO.Recordset
Dim n As Integer
Set rs = CurrentDb.Openrecordset("Select * From ImportedTable")

Do Until rs.EOF
   For n = 1 To rs![Field2]
      CurrentDb.Execute "INSERT INTO NewTable (Field1, Field2) " & _
                        "Values('" & rs![Field1] & "',1)"
   Next

 

[ste4en]

Thanks for that; I hate to ask but not quite sure where to put this - I added a module and copied your code and modified it to suit my table and saved it. I don't know how to run it though..Pklease tell me how.

Public Function breakout()


Dim rs As DAO.Recordset
Dim n As Integer
Set rs = CurrentDb.OpenRecordset("Select * From sheet1")

Do Until rs.EOF
   For n = 1 To rs![qty]
      CurrentDb.Execute "INSERT INTO NewTable (Sort, qty) " & _
     "Values('" & rs![Sort] & "',1)"
   Next


End Function

 

[BuilderSpec]

In the access visual basic , create a new module and call it anything, it doesn;t matter. ( Right click "Modules" and choose Insert then Module. )


Paste that code above in..

Then go to the "Immediate Window" .. if it isn't there then use menu "View" then "Immediate Window".

In this "Immediate Window" then type "breakout" then hit enter.. this wil run the code.

NB If you run it more than once then you will create duplicate entries.







Hope this helps!

Regards

BuilderSpec

 

[kjv1611]

ste4en,

Also, another way to run the code, if you prefer, is to change the "Public Function" to "Public Sub" or "Private Sub", and just hit the "play" button on the toolbar or hit the <F5> key to run the program.

If you are going to be calling this from other Form Modules, then you would want to leave it public. But if you are just going to run it one time, or possibly another time way down the road, then it really does not matter which way you do it.

Typically, if I am just going to run something one time, I'll put it as "Private Sub", run it, and be finished with it.

Private just means that it cannot be called outside that module, whereas Public means it can be called from anywhere in the database.

Also, Function is not necessary unless you need the procedure (function) to return a value; otherwise, Sub (for Sub Procedure) is the simplest format to work with.

Someone may can argue that the Function is best, but unless necessary (such as returning a value), then it's mroe a personal preference.