Insert Query/SQL Problem 1

[Cosmiccradle]

Hi:

How can I add more then one Library while keeping the rest of the information the same? In the example below I only get Betondorp as library, but I also need Geuzenveld, Duivendrecht, Pintohuis, Osdorp ect. while the rest of the information stays the same. UPDATE only changes exsisting fields, I need to add fields within the one table Smallbranchlibraries.

INSERT INTO Smallbranchlibraries
( Library, Code, Title, Bookecode, Price, Youth, Grownup, Comment, Baselistnr )

SELECT
'Betondorp' AS Library, 'w' AS Code, 'Try Again' AS Title, '422.22' AS Bookcode, ' 45,00' AS Price, 'P' AS Youth, ' ' AS Grownup, 'ipv Bobbo' AS Comment, '1 ' AS Baselistnr;

Thanks for any help Cosmiccradle

 

[benjamenus]

If I've understood your question, you would need a table (tblLibraryName) to store the different names (in a field Name).

e.g. tblLibraryName.Name would contain the values Betondorp, Geuzenveld, Duivendrecht, Pintohuis, Osdorp, etc.

Your insert statement would then look like:

INSERT INTO SmallBranchLibraries
(Library, Code, Title, Bookecode, Price, Youth, Grownup, Comment, Baselistnr)
SELECT
tblLibraryName.Name AS Library, 'w' AS Code, 'Try Again' AS Title, '422.22' AS Bookcode, ' 45,00' AS Price, 'P' AS Youth, ' ' AS Grownup, 'ipv Bobbo' AS Comment, '1 ' AS Baselistnr
FROM tblLibraryName;

The result would be:

Betondorp, w, Try Again, ...
Geuzenveld, w, Try Again, ...
Duivendrecht, w, Try Again, ...
Pintohuis, w, Try Again, ...
Osdorp, w, Try Again, ...
...

You could also include a WHERE clause if required.

 

[Cosmiccradle]

Hi Benjamenus:

I don't know how to thank you, with the WHERE function it works like a charm.

INSERT INTO SmallBranchlibraries ( Branch, BookCode, Title, Bookcode, Price, Youth, Adult, Comment, Basislistnr )

SELECT Basisnr.Branch AS Branch, 'w' AS Code, 'Try Again' AS Title, '422.22' AS Bookcode, ' 45,00' AS Price, 'P' AS Youth, ' ' AS Adult, 'ipv Bobbo' AS Comment, ' ' AS Basislistnr
FROM Basisnr
WHERE Basis = (1);

I first got all 29 libraries but with the WHERE function I can choose which branch libraries I need those falling under the numbers 1,2 or 3. Thanks for your time and help.

Cosmiccradle

 

[Cosmiccradle]

Hi Benjamenus:

I spoke too soon. All goes well within the query and in the table, however when I want to open my report I get the message "Branch can refer to more then one table in the component FROM in your SQL instruction." Any ideas, I do use this collum heading in four different tables, however each table is called something else.

Cosmiccradle

 

[benjamenus]

Is the report based on some SQL - if so, can you post it please?

 

[Cosmiccradle]

Hi Bejamenus:

No the report is not based on sql but the query itself. However it could be the computer. I removed the query and I still got the same error message when trying to open the report. Threw the whole database out and replaced it with the backup and tried again, with exactly the same wording and low and behold it worked. Keeping my fingers crossed and hope that it stays that way.

Thanks for your time.
Cosmiccradle

Ps However if it should happen again do you have any idea why?

 

[benjamenus]

No idea I'm afraid - I'd try

http://support.microsoft.com/

and search the knowledge base. Hopefully you wont have to!

P.S. The query is an SQL statement.

 

[Cosmiccradle]

Hi Benjamenus:

Thanks for your time and patience and help. All's well so far.

Cosmiccradle