Get Field ID not Field Name in Query

[andy_dyer]

That's what the previous two pages of posts have been trying to achieve...

My query is this:

SELECT [tblMental Health].Referrer AS Expr1, Count(tblInput.[Centre Code]) AS [Number of Referrals], tblInput.Referrer
FROM [tblMental Health] RIGHT JOIN tblInput ON [tblMental Health].[Mental Health] = tblInput.Referrer
WHERE (((tblInput.[Referral 1 Received Date]) Between [Forms]![frmReportDates]![txtStartDate] And [Forms]![frmReportDates]![txtEndDate]))
GROUP BY [tblMental Health].Referrer, tblInput.Referrer
HAVING (((tblInput.Referrer)=[Forms]![frmInput]!cboReferrer.Column(1)));

But it doesn't like the syntax where I am trying to access the correct column of the combo box...

 

[Rich]

What exactly is this query for ?

 

[andy_dyer]

Just a basic old query...

Trying to get the number of referrals from each location within a given date span.

The date span is picked up by two text boxes on the form.

The basic query is:

SELECT tblInput.Referrer, Count(tblInput.[Centre Code]) AS [Number of Referrals]
FROM [tblMental Health] RIGHT JOIN tblInput ON [tblMental Health].[Mental Health] = tblInput.Referrer
WHERE (((tblInput.[Referral 1 Received Date]) Between [Forms]![frmReportDates]![txtStartDate] And [Forms]![frmReportDates]![txtEndDate]))
GROUP BY tblInput.Referrer;


This gives me the ID of the referrers and the number of referrals, I want to be able to pull through the Name instead...

 

[Rich]

Then you have to add the LookUp table to the query window, make sure the joins are correct and add the text field to the grid, remove or unckeck the original ID field

 

[andy_dyer]

Ok, checking these off:

The lookup table tblMentalHealth is in the query

I think the joins are right (knowing only a little about relationships)

How do I add the combo box to the grid?

The original ID field, is this the field for tblMentalHealth's ID?

If so this isn't part of the query at present anyway...

 

[Rich]

There is no purpose for a combo box in a table or a query, just double click the field from the lookup table and it will be added to the grid

 

[andy_dyer]

Ok I've added that column to the query now I have three column's displayed on my query the one from tblMentalHealth is blank and I have the Column from the main table tblInput that has the ID in from the input, and then I have the number of referrals.

I am beginning to feel that I am missing something very simple...

 

[Rich]

Post a 97 ver here with some dummy data if need be

 

[andy_dyer]

How do I make an Access 2000 db into an Access 97 one?

 

[Mile-O]

Tools -> Convert to Access 97 Database

 

[andy_dyer]

In that case, here we go...

 

[namliam]

I downed the sample

1) dont use spaces in names EVER!
2) would be nice to have a form present...
3) dont use spaces in names EVER!
4) would be nice to have some test data present...
5) dont use spaces in names EVER!

Regards

 

[andy_dyer]

All my databases have spaces in them!!!

I had created them before knowing that i shouldn't, and it is far too much like hard work changing them in their 10 seperate locations!!

If I'd have left a form it was too big to post, so it had to go!

Will try to add a few bits of sample data... and re-post!!

 

[Rich]

You are try to store the text field from the lookup table instead of the ID. You have both PK fields named ID, I've altered the tables and joins slightly to correct them. Your query now displays the text value based on the ID. I don't have time to correct or check the rest of the structure though

 

[andy_dyer]

Even managed to leave a few forms this time!

Although as I've deleted most the movement between them may not work as planned!

Have put a couple of dummies in as well...

 

[namliam]

The problem is your table design!!!! (again!) *GRMBL*

In your tblInput the field Referrer is a foreign key to the table tblMental Health *SIGH* *S P A C E S*
In tblInput its a text field while in tblMental Health its an Autonumber (Number!)

2 posible solutions
1) change the field referrer in tblInput to number and link to the Autonumber in tblMental Health *SIGH* => Problem solved...
(Good practice also)

2) change the bound column on your form to 2 instead of 1. Now you are storing the full name from the tblMental Health, no more relations are needed => Problem solved (BAD practice, or you must have a good reason to store them in this input table as well...)

Regards

 

[andy_dyer]

Ok...

I consider myself to have had my wrists well and truely slapped...



It does seem so easy now and makes so much more sense!! I always wondered why when creating new tables they wanted to create an autonumber primary key, now I know that if I do I can link them using this!!

*Plonk* (That was the sound of a penny dropping...)

I have a few tables like this and I shall now go and amend them for the next release...

Thanks everyone for all their help and patience!!