Query displaying no results

[BBK]

I want to enter a location and get the total rent for that location.

My SQL is as follows:

SELECT tblProperty.Address, tblPayment.AmountDue
FROM tblProperty INNER JOIN tblPayment ON tblProperty.PropertyID = tblPayment.PropertyID
WHERE (((tblProperty.Address) Like "*" & [Enter Location:] & "*"));

My relationships is shown in the link below:

http://i33.tinypic.com/i6e5as.jpg

Problems:
When i run the query it shows blank results.
When i break it down - and just use property table it will bring up address for what i enter but as soon as i bring in my payment table my results are blank.

Any help would be greatly appreciated, thank you for reading.

 

[Brianwarnock]

That would suggest that there is no hit on the join, ie there is no propertyID for that location in tblpayment. remove the where and see if that location now appears.

Brian

 

[BBK]

I removed the "where" clause and it still gives me blank results. I have propertyId in the payment tables it is the fk. I even tried simplfying the code as below and it still giving a blank result


SELECT tblProperty.Address, tblPayment.AmountPaid
FROM tblProperty INNER JOIN tblPayment ON tblProperty.PropertyID = tblPayment.PropertyID;

 

[Brianwarnock]

You obviously you do not have a match on any propertID from one table to the other, what type of ID is it, is it a number and if so are the formats the same, tho' I'm grasping at straws here if an ID exists in both tables.

Brian

 

[BBK]

Thanks for your reply i have just double checked them again and they are

PropertyTable - PropertyID - Autonumber
PaymentTable - PropertyID - Number

One to Many relationship - From property to payment table

Any more light that you can shed is appreciated

 

[Brianwarnock]

And you are absolutely certain that you have atleast 1 propertyID that is the same in both tables, beats me, sorry. I don't have 2007 otherwise can you attach the DB?

Brian

 

[BBK]

Damn it, just after seeing the the payments table is missing all the property Id's


How the hell

 

[Brianwarnock]

I can see that you will be mad but I'm relieved as I couldn't think of anything.

Brian

 

[BBK]

Brian, i have tried converting the database to be compatible with your version.

I tried ammending the relationship between Property and Payment but no luck.

If you have some spare time could you take a look and lead me in the right direction before i have no hair left.

Thanks

Database has some fake info added for testing

 

[Brianwarnock]

As you said earlier tblPayment does not contain the propertyID, however you could use tblLease to link all the info together or you could update tblpayment.
Not sure how you intend to keep all of the data in synch, I don't have much time on my hands at the moment as wife in hospital.
To update tblPayments

UPDATE tblLease INNER JOIN tblPayment ON tblLease.TenantID = tblPayment.TenantID SET tblPayment.PropertyID = [tblLease].[propertyid]
WHERE (((tblPayment.PropertyID) Is Null));

Actually I don't think the where should be included.

Brian

 

[BBK]

Thanks for your reply Brian and best wishes with a speedy recovery to your wife, i hope it nothing to serious that has her in hospital.

Just to let you know,

UPDATE tblLease INNER JOIN tblPayment ON tblLease.TenantID = tblPayment.TenantID SET tblPayment.PropertyID = [tblLease].[propertyid]

did indeed work and updated the PropertyID in the payment table with the proper ID, but not very pratical like you say for keeping things in sync.

Looks like im %^*# after $%&*ing up with the relationships once again and have hit a stumbling block.

Thanks for your time, advice and reply once again Brian.

 

[Brianwarnock]

What about my suggestion concerning using tblLease as a link table, it has all the info, your query then becomes.

SELECT tblProperty.Address, tblPayment.AmountPaid
FROM (tblProperty INNER JOIN tblLease ON tblProperty.PropertyID = tblLease.PropertyID) INNER JOIN tblPayment ON tblLease.TenantID = tblPayment.TenantID
WHERE (((tblProperty.Address) Like "*" & [Enter Location:] & "*"));

Hopefully my wife will be out later today, depends on the results of Heart and Lung scans.

Brian

 

[BBK]

That works perfectly - thank you so much for your help, I am very grateful. I do hope your wife's tests come back with good results. Thanks again for your help especially with the worries you have at the moment.