Parameter Query

[aussie]

I am trying to select parameters for my query to prompt.
Under Criteria I typed : PatientLastName
It said that there were two tables from the parameter could be called, so I added:
Patient History Table.[PatientLastName]
Result is that it either prompts me for the last name or says there is a syntac error, If the query does open with a prompt it shows all last names in the underlying tables.
Can anyone Help

 

[Galaxiom]

All names that include spaces need to be enclosed in the square brackets but it is better not to have spaces in names.

If you have PatientLastName in more than one table then you have a normalization error in your data structure.

 

[RainLover]

I am trying to select parameters for my query to prompt.
Under Criteria I typed : PatientLastName

If this is what you did then replace it with

[Patient Last Name] Use Spaces, it looks better here.

NB This is in the Criteria. No where else.

Patient Last Name will show up in a message asking you to enter the Patient's Name.

 

[aussie]

Ok, so I set the criteria to :
[Patient Details Table].[PatientLastName]
It didn't work, I was not prompted for the PatientLastName

I tried SELECT [Patient Details Table].PatientLastName and it didn't work.

Do you know what I have done wrong?

I know that it is better to not have spaces in table names etc. but by the time I realised it Iwas too far into the DB to change it.
Thanks

 

[RainLover]

Do you know what I have done wrong?

Yes I do.

You did not follow my instructions.

I said put this.

[Patient Details Table]

Nothing else. Try a copy paste.

 

[RainLover]

If you have PatientLastName in more than one table then you have a normalization error in your data structure.

Galaxiom,

As you are keen on using Natural Keys it would not be unNormalised to have PatientLastName in various Tables.

 

[aussie]

Sorted! Thanks very much

 

[aussie]

Sorted! Thanks

 

[RainLover]

No problem.

As you can see, close enough is not good enough. You have do to everything exactly right. That's why we all make mistakes.

 

[Galaxiom]

Galaxiom,

As you are keen on using Natural Keys it would not be unNormalised to have PatientLastName in various Tables.

No. Names are not unique and hence are are not suitable candidates for a key.

Clearly you don't understand how to select a suitable candidate for a natural key so it is best you permanently stick with synthetic keys for everything.

 

[Galaxiom]

[Patient Last Name] Use Spaces, it looks better here.

Moreover the parameter prompt cannot have the same as a field in the table. Query criteria can also be fieldnames so it needs to be different of Access will want to use the field.

The message about more than one table when using PatientLastName would be because there was a field by that name and that field is in more than one source table.

This is why I commented about the normalisation error. Ignore Rain's comment.

 

[RainLover]

Moreover the parameter prompt cannot have the same as a field in the table.

I knew that but had long forgotten as I don't use that type of thing any more.

Luckily I suggested the spaces.

Thanks for the reminder.

EDIT

Now I think about it more, that was completely wrong of me. I do know better than that.