To search record

**NeoPa** · Aug 25 '08, 08:02 PM

Originally posted by puT3

Can I ask u why some of the SQL has the field in bracket and some are not.

You like to keep me busy I see. But that's not a problem, this is an interesting question that others may also need to understand.

In general, using [] to surround object names in SQL is not necessary. However there are situations where, without the [] specifically indicating that the contents refer to an object (Field; Table; Control from a form; Query; Database even sometimes) interpreting the SQL could be ambiguous (Remember we discussed clarity and ambiguity in communication earlier, well it applies even more specifically to computers). Well, if the SQL engine, or interpreter, is unable to understand what is meant, then it will not execute the SQL (at least not correctly).

Examples of possible ambiguity are reserved words (Date; For; SELECT; etc), as well as object names with embedded spaces (SQL uses any white space as a separator). If any of these are used as object names, then the SQL engine would not know whether the word referred to your object or the inbuilt item. Surrounding them in [] makes it clear exactly what is intended.

As far as posting goes, and READABILITY of the SQL, I often use them where they are not strictly required by the SQL engine, to make the context clearer and easier to read. Whenever you see something in the [] you KNOW it's an object. I don't use them so much within my own SQL, but for clarity on the forums I do.

PS. @Joe P.
Sorry, didn't catch your post until after I'd already posted this one. Thanks for jumping in anyway. All contributions appreciated :)

**NeoPa** · Aug 25 '08, 09:13 PM

Originally posted by puT3

I already run the SQL as u posted...When I click run,a message box appear to enter parameter value and i click ok,a datasheet view appear with no data in it...the SQL:

Code:

...
Some SQL you've knocked up
...

so what happen next?

If you refer back to post #54 (which you've quoted) you will find that the instructions are crystal clear. There is no ambiguity.

Telling me that a message box appeared prompting for a parameter value (without including even the name showing in the box) is NOT following the instruction.

It's not hard to understand why your response leaves me with nothing to work on. I couldn't help further if I wanted to from this.

I am not going to reverse engineer every piece of SQL you post simply because you cannot seem to pass the information across I need. That takes a lot of time and effort, and I have already done that a couple of times. I don't intend to do that every time you fail to post the information correctly, as clearly, from past experience, I would be spending most of my time doing just that.

**puT3** · Aug 26 '08, 01:21 AM

Originally posted by NeoPa

If you refer back to post #54 (which you've quoted) you will find that the instructions are crystal clear. There is no ambiguity.

Telling me that a message box appeared prompting for a parameter value (without including even the name showing in the box) is NOT following the instruction.

It's not hard to understand why your response leaves me with nothing to work on. I couldn't help further if I wanted to from this.

I am not going to reverse engineer every piece of SQL you post simply because you cannot seem to pass the information across I need. That takes a lot of time and effort, and I have already done that a couple of times. I don't intend to do that every time you fail to post the information correctly, as clearly, from past experience, I would be spending most of my time doing just that.

Im really sorry again...my earlier response wasnt enough for u to work on,really2 sorry,i dont read the instructions well enough,i really sorry,...

the message box that appear request to enter a parameter value for each of the field,it ask for

Code:

Printing Media Library.CallNumber,
Printing Media Library.Accession Number,
Printing Media Library.ISBN/ISSN,
Author.Author Name,
Printing Media Library.Title,
Printing Media Library.Physical Description Area,
Subject.Topical Headings,
Publication.Publication Name,
Printing Media Library.Bibliography Notes,
Printing Media Library.CopyrightYear,
Provider.ProviderID,
Printing Media Library.CategoryID,
Printing Media Library.Description....

I click ok everytime this message box appear n finally an empty datasheet appear....

i understand if u dont want to continue this but i need help,i will give u every information that u need after this...

**NeoPa** · Aug 26 '08, 11:30 AM

There seems to be a fundamental problem here, in that the references in your list don't appear in the SQL I provided in post #44.

SQL will only ask for the value of a reference when it finds it in the SQL and doesn't know how to resolve it.

The SQL I provided makes perfect sense in the context of the information I had from you at that stage, so if there are any failures of reference, they should be few and easily resolvable. In the process we make some headway, at least in the information communicated.

As these reference controls on your form in an incorrect manner, I can only assume that you have tried out some of your own attempted SQL instead of what I provided (in post #44). Looking at this will get us nowhere so let's not even waste our time looking at the various ways this is wrong.

###RESET###
Just go back to post #54 and try again from there.

**puT3** · Aug 27 '08, 03:19 AM

Originally posted by NeoPa

There seems to be a fundamental problem here, in that the references in your list don't appear in the SQL I provided in post #44.

SQL will only ask for the value of a reference when it finds it in the SQL and doesn't know how to resolve it.

The SQL I provided makes perfect sense in the context of the information I had from you at that stage, so if there are any failures of reference, they should be few and easily resolvable. In the process we make some headway, at least in the information communicated.

As these reference controls on your form in an incorrect manner, I can only assume that you have tried out some of your own attempted SQL instead of what I provided (in post #44). Looking at this will get us nowhere so let's not even waste our time looking at the various ways this is wrong.

###RESET###
Just go back to post #54 and try again from there.

I tried again the SQL u post in post #44 but i do not include the [Provider Name] field from [Provider] table and i change a little bit to match the name of the field i changed which i already given u in earlier post (#52)...

Code:

SELECT tP.CallNumber, tP.AccessionNumber, tP.[ISBN/ISSN], tA.[Author Name], tP.Title, tP.[Issue/Edition], tS.[Topical Headings], tPu.[Publication Name], tP.[Physical Description Area], tP.[Bibliography Notes], tP.CopyrightYear, tP.ProviderID, tP.CategoryID
FROM (([Printing Media Library] AS tP INNER JOIN Publication AS tPu ON tP.PublisherID=tPu.[Publication ID]) INNER JOIN Author AS tA ON tP.AuthorID=tA.AuthorID) INNER JOIN Subject AS tS ON tP.SubjectID=tS.[Subject ID];

This time there is no more message box appear,its went straight to a datasheet with all the fields but there is no data in the datasheet,shoul d this happen? Or should it have any data in it?I forgot to ask u what should I expect if I run this SQL code....

**puT3** · Aug 27 '08, 09:19 AM

Another thing i to ask u,what do u mean by this "you may also want to change some of the control names on [PRINTING MEDIA LIBRARY] to reflect they are no longer referring to the IDs" you have post in post#44....

For the SQL,I should see a list of data right?it still has problem right?

in [Printing Media Library], i make AuthorID,Publis herID,ProviderI D and SubjectID as a look up to [Author],[Publication],[Subject],[Provider] table, i shouldnt have any problems right?

**NeoPa** · Aug 28 '08, 10:40 AM

Originally posted by puT3

I tried again the SQL u post in post #44 but i do not include the [Provider Name] field from [Provider] table and i change a little bit to match the name of the field i changed which i already given u in earlier post (#52)...

Code:

Your SQL

This time there is no more message box appear,its went straight to a datasheet with all the fields but there is no data in the datasheet,shoul d this happen? Or should it have any data in it?I forgot to ask u what should I expect if I run this SQL code....

I am interested in seeing the results of the SQL I posted. I am not interested in seeing the results of any other SQL at this time.

I don't know how much more clear I can make this statement.

**NeoPa** · Aug 28 '08, 12:03 PM

Originally posted by puT3

Another thing i to ask u,what do u mean by this "you may also want to change some of the control names on [PRINTING MEDIA LIBRARY] to reflect they are no longer referring to the IDs" you have post in post#44....
...

From post #26, the latest published version of this form (updated and displayed below), it has places for the IDs for various elements, but what we really want to show on this form is the descriptions for those elements. This has been handled in the SQL (it returns the descriptions rather than the IDs now), but the names of the controls on your form have not (as far as I'm aware - & I should be) been changed to reflect these changes.
Form Name=[PRINTING MEDIA LIBRARY]

Code:

[I]Control; Type[/I]
Call Number; TextBox
Accession Number; TextBox
ISBN; TextBox
AuthorID; TextBox
Title Statement; TextBox
Issue; TextBox
SubjectID; TextBox
PublisherID; TextBox
Physical Description Area; TextBox
Bibliography Notes; TextBox
Description; TextBox
Copyright Year; TextBox
ProviderID; TextBox
CategoryID; TextBox

Originally posted by puT3

...
For the SQL,I should see a list of data right?it still has problem right?
...

I have no way of knowing yet, as you haven't given me any info regarding what happens when you run THE EXACT SQL I posted.

Originally posted by puT3

...
in [Printing Media Library], i make AuthorID,Publis herID,ProviderI D and SubjectID as a look up to [Author],[Publication],[Subject],[Provider] table, i shouldnt have any problems right?

If you follow the instructions correctly there should be no need for doing any extra lookups. You would certainly NOT want to design any into your form.

**puT3** · Aug 29 '08, 01:16 AM

Originally posted by NeoPa

From post #26, the latest published version of this form (updated and displayed below), it has places for the IDs for various elements, but what we really want to show on this form is the descriptions for those elements. This has been handled in the SQL (it returns the descriptions rather than the IDs now), but the names of the controls on your form have not (as far as I'm aware - & I should be) been changed to reflect these changes.
Form Name=[PRINTING MEDIA LIBRARY]

Code:

[I]Control; Type[/I]
Call Number; TextBox
Accession Number; TextBox
ISBN; TextBox
AuthorID; TextBox
Title Statement; TextBox
Issue; TextBox
SubjectID; TextBox
PublisherID; TextBox
Physical Description Area; TextBox
Bibliography Notes; TextBox
Description; TextBox
Copyright Year; TextBox
ProviderID; TextBox
CategoryID; TextBox

I have no way of knowing yet, as you haven't given me any info regarding what happens when you run THE EXACT SQL I posted.

If you follow the instructions correctly there should be no need for doing any extra lookups. You would certainly NOT want to design any into your form.

Sorry I was away yesterday that i couldn't reply back to you...

If I exactly run your posted SQL,a message box will appear and requesting for parameter for each of the field and click ok for each box appear without entering anything it in and lastly it will show a empty datasheet....because i think it does not match the field name in the table...is it true? thats why i changed a little of the name to match the name of the field in the table....

this is your code that i run....i copy it exactly like it in the query

Code:

SELECT tP.[Call Number],
       tP.[Accession Number],
       tP.ISBN,
       tA.[Author Name],
       tP.[Title Statement],
       tP.Issue,
       tS.SubjectTitle,
       tPu.[Publication Name],
       tP.[Physical Description Area],
       tP.[Bibliography Notes],
       tP.[Copyright Year],
       tP.[Provider ID],
       tP.CategoryID
 
FROM (([Printing Media Library] AS tP INNER JOIN
       [Publication] AS tPu
  ON   tP.PublisherID=tPu.[Publication ID]) INNER JOIN
       [Author] AS tA
  ON   tP.AuthorID=tA.AuthorID) INNER JOIN
       [Subject] AS tS
  ON   tP.SubjectID=tS.SubjectID

**puT3** · Aug 29 '08, 03:16 AM

Im sorry I just reread all your post n I missed to change the recordsource of the form to the query.However,t he problems occur when i run it as I previously posted..there are no data in it...

**puT3** · Aug 29 '08, 04:47 AM

sorry the same problem occur

**NeoPa** · Aug 29 '08, 11:43 AM

This is more like what I need.

Creating the QueryDef from it is only important later. It should just make life easier for you when we get it right and want to move on from here.

There is still a bit more information I need from you though. I have designed the SQL based on the information you've provided for me. This means that, if it's not working perfectly there are two possible reasons :

I have made an error in creating the SQL (possible, we can check this).
The information you have passed is incorrect.

It seems that the SQL is not recognising the names of the objects listed. These have come directly from what you've told me and I have asked you to check these on a number of occasions.

What I need from you now is a list of the messages for all the prompts you get when you try to run this. Doing them one-by-one is possible, but will take much longer. From what you say it SEEMS that the errors are fundamentally simple ones of getting the names of the fields wrong. I see nothing so far that indicates the linking of the tables together might be a problem.

**puT3** · Aug 30 '08, 02:27 PM

Ok,i'll do it...but i get it to you later because im at my hometown because of public holiday(indepen dence day)....i'll give to you as soon as possible...

**NeoPa** · Aug 30 '08, 10:35 PM

No worries.

I have plenty to keep me busy ;)

**puT3** · Sep 2 '08, 01:04 AM

Sorry about the long break...the message box appear like this....
Enter Parameter Value....tP.Cal l Number
Enter Parameter Value....tP.Acc ession Number
Enter Parameter Value....tP.ISB N
Enter Parameter Value....tP.Tit le Statement
Enter Parameter Value....tP.Iss ue
Enter Parameter Value....tS.Sub ject Title
Enter Parameter Value....tP.Cop yright Year
Enter Parameter Value....tP.Pro vider ID
Enter Parameter Value....tS.Sub ject ID

I think I want to give you the table of [Printing Media Library] again,see if i missed anything since i changed the name a little while the form i make directly from the change table...

Table Name=[Printing Media Library]

Code:

[I]Field                      Type   IndexInfo[/I]
CallNumber                 String     PK
AccessionNumber            String
ISBN/ISSN                  String
Title                      String
SubjectID                  Number     FK
PublisherID                Number     FK
Physical Description Area  String
Bibliography Notes         String
Description                String
CopyrightYear              String
ProviderID                 Number     FK
CategoryID                 Number     FK
Issue/Edition              String
AuthorID                   Number     FK

Table Name=[Author]

Code:

[I]Field             Type   IndexInfo[/I]
AuthorID          Number     PK
Author Name       String
Background        String

Table Name=[Subject]

Code:

[I]Field             Type   IndexInfo[/I]
Subject ID
Topical Headings  String

Table Name=[Provider]

Code:

[I]Field         Type  IndexInfo[/I]
ProviderID    Number    PK
ProviderName  String
Quantity      String

Table Name=[Publication]

Code:

[I]Field             Type   IndexInfo[/I]
Publication ID    Number     PK
Publication Name  String
Foundation        String

Table Name=[Category]

Code:

[I]Field          Type   IndexInfo[/I]
CategoryID     Number     PK
Category Name  String

To search record

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment