SQL and Access

Matt_Hirst · 13 Jan 2011 at 15:01

I have a table that I need to create a set number of records in depending on a user selection, i.e. user selects 4 so 4 records are created now I have tried to do this using SQL and here in lies the problem.

I am wanting the record to use the default values for the fields apart from one, this field will have the same numeric value across the set of records.

Is htere a way to do this or do I have to supoply the default value for each field in the record being created?

e.g. User selects 2

so run the following statement twice.
Insert Into table1 (field1, field 2, ..., field 23) Values ('1','2','m'...)

regards,

Matt

Stelly · 13 Jan 2011 at 15:45

Hey Matt,

Would help with more information, are you using access or web programming, more information the better, but it is certainly doable...

Stelly

SimonCHere · 13 Jan 2011 at 16:42

Here is a bit of a scruffy way of doing it. You need a table with a number of rows in it greater than the number of rows you want to insert with 1 column, a number!

Code:

[table]tblCounter
[fields]number int
number
-------
1
2
3
4
5
6

then just replace the number in the select below with the number of rows you want to insert.

Code:

Insert Into table1 (field1, field 2, ..., field 23)
select 1,'test','rubbish,'data1','data2'
from (select * from tblCounter where number <= 2) a

Matt_Hirst · 13 Jan 2011 at 19:40

Thanks for this, was bugging the hell out of me so I posted this message then got it sorted abot an hour later !

regards,

Matt

Matt_Hirst · 14 Jan 2011 at 16:19

One further question say I had a table, table1, the records are all index stamped relating to when they were input, if three of these records were related how would I write an sql statement to remove the last record enterred?

regards,

Matt

Ciphon · 14 Jan 2011 at 17:16

Matt_Hirst said:
One further question say I had a table, table1, the records are all index stamped relating to when they were input, if three of these records were related how would I write an sql statement to remove the last record enterred?

Code:

DELETE TOP 1
FROM <TABLE>
WHERE <Whatever you want to search on>
ORDER BY TimeStamp DESC

Something like the above would work for a single occurrence of what you just described, but not if you had mutliple sets of data that you wanted to delete the last record for.

You would obviously need to complete the WHERE clause...

The above is a little long winded to just delete one record though - may as well use your eyes to check which was the last entered and delete via a primary key value.

Unless you do want something that will remove for all occurrences - Don't have the time at the moment to write something that would do this.