Cant get Query to Run

[jersey]

I am getting really frustrated. These two queries that I have to run look very simple but I can't get them to run. Can someone please help me???

Senario #1 - I have a client table that lists FirstName, LastName, BillingAddress and phonenumber. I need to run a query to find out who is at a particular address. For example I have 20 different names and addresses. I know that there are about 5 people that have the same street name (just different street number). How do I run this query? I have tried typing in "Eureka" in the criteria line and nothing. Then I tried "Eureka Lane" and still nothing. I need a query that shows all addresses on Eureka Lane. Please help...

Senario #2 - I have a client, account and rep tables. In my rep table is the repnumber, replastname, repfirstname and commisionrate (in percentage). In my account table I have contractID, amountpaid, balancedue and clientID. I need to run a query to show what commision rate is due to each account rep based on amount paid. How do I do this?? I don't have a relationship between these two tables - should I? and if so, what is my relationship???

Thanks for any help

 

[IanH]

Dear Jersey,

Scenario 1:

SELECT firstname, lastname, billingaddress, phonenumber
FROM DataTable
WHERE billingaddress like "*eureka*"

Alternatively you could create a parameter query, so that when you run the query it asks you for an input, like this:

SELECT firstname, lastname, billingaddress, phonenumber
FROM DataTable
WHERE billingaddress like "*" & [StreetName] & "*"

When the input box comes up just type in Eureka (or Eur or even E) and it will find all billing addresses with the name or part name in it.

Scenario #2:
You will need to create a relationship between the client, accounts and reps. This could depend on the relationships between the tables.
I assume from your design that each rep has the same amount of commission for every accouunt / client (otherwise the commission rate would be attached to either the client or the account). and that each rep therefore only appears once in the rep table.
As to which table you put the RepID in, this may depend on whether the rep manages the client, or manages the account.

If each client has only one account, then arguably you could put the repID field in either the account table or the client table.

If there are more than one rep for each client (each manageing a different account), then you would need to attach the repID to the account.

If there the rep is seen to manage the client (and therefore multiple accounts for that client) then you could put the RepID in the client table.

If however there is a many to many relationship - with for example each client having multiple accounts, all managed by different reps - then you would probably need a separate table with repID, clientID and accountID fields to uniquely identify all of the elements.

Have a look at the sections in the Access books online (help file) on relationships and referential integrity

Hope that is clear

Regards

Ian

 

[jersey]

Ian,

Thank you so much. Senario #1 worked . The only thing I forgot to try was *Eureka*.

As for Senario #2. I am still confused. My rep table has a relationship to my clients table and my clients table has a relationship to my account table. There is no relationship from my rep table to my account table. Should there be?? I only have three reps listed in my rep table. Each rep has different clients. Once I establish a relationship how do I calculate the commission rate that is due based on the accounts that have been paid?

 

[jersey]

Ian,

I know I already posted stating that I was still confused. I kept re-reading your reply. I think I got it. Please tell me if this is right:

I created a new query by the design view. I added all three of my tables. I took the rep first/last name and commission from my rep table and the amount paid from my account table. I right clicked in the next empty row, and clciked build. I then created an expression builder that looks like this Sum([Reps]![CommisionRate]*[Accounts]![AmountPaid]). I then made sure that I was getting the sum - since the reps have more then 1 client (but they are all different clients). My query then came up with the First and Last name of my rep and an amount that looks like what commision is due to them.

Does this sound right????

 

[IanH]

Ian,

I know I already posted stating that I was still confused. I kept re-reading your reply. I think I got it. Please tell me if this is right:

I created a new query by the design view. I added all three of my tables. I took the rep first/last name and commission from my rep table and the amount paid from my account table. I right clicked in the next empty row, and clciked build. I then created an expression builder that looks like this Sum([Reps]![CommisionRate]*[Accounts]![AmountPaid]). I then made sure that I was getting the sum - since the reps have more then 1 client (but they are all different clients). My query then came up with the First and Last name of my rep and an amount that looks like what commision is due to them.

Does this sound right????

This does look OK - it gives you the standard commission for each rep and multiplies this by the amount paid for the accounts table. I have re-read what I wrote and am not surprised it still confused you - (I'm more shocked by my appalling typos,but can only say I was a little in a bit of a rush!)

My only concern was that you might have varying rates of commission for each rep - varying by either account or client - or multiple clients and accounts for each rep.

If as you are suggesting each rep has a fixed commission rate (regardless of client or account) then what you have got works fine.

In my reply I appear to have forgotten something I constantly impress on my team - KISS (keep it simple, stupid!).

If it works for you now, stick with it. If you need to vary the structure because of more complex relationships between reps / accounts / clients then we can always look at this again.

best regards

Ian