query date question

[smiley0q0]

I have a query that has [date] and [yamount]
and another query that has [date] and [xamount]

the dates are not related at all, the first is a day in the moddle of the month, the other is the last day of the month of the previous month of the first date. i need to...
[yamount]/[xamount] but i can't figure it out because the dates don't work together. i need to make a graph out of this info. any suggestions?

 

[ghubbell]

Hi Smiley!
You'll have to find some relationship from one query to another in order to do your math, and that's a given. If you look at the records in both queries now how would you know which to divide by which other? One you can establish this link its simply a matter of adding another field that looks like:

SmileysResultIs: [yamount]/[xamount]

And this column will be your answer!

Gord
ghubbell@total.net

 

[smiley0q0]

hi gord, nie to hear from you again... do you ever do anything else, but answer these silly questions?

ok i will try to be a bit more specific this time...
qry1=[date], [org], [yamount]
qry2=[date], [org], [xamount]

date in qry1 can be any date in all of the past 12 months.
date in qry2 is the last day of the previous month for the date in qry1. there are 3 orgs, the is the relationship between the 2 queries. whenver i do a result:[yamount]/[amount] i end up with over 500 rows of results, there should only be 39, 3 for each date for 13 months. (3*13=39) In each of my queries, there are only 39 rows. i thought since my 2 queries both have 39 rows, and they are in the correct order to divide, i thought i could just display them and divide them but it doesn't seem to work that way. am i going about this the hard way or is today not my day?

 

[ghubbell]

Smiley! There's no silly questions here! Answers? Lots! That's my reason to hang out..

Unless you have a join line between the queries, you'll get something I believe is called a Cartesian result: a*b.
Each record tries to link to each other in the other table.

This is perfectly legal but you had better have only 3 records in one query and thirteen in the other. If you cannot establish a link you'll have to do some summing based on the 3 org's and another summing based on the months. Then put those two together to get your 39. Let me know if this is do-able or show me your queries and I can try to figure it out if you get stuck. I'm not the best with the date stuff and I've been watching from the wings while you've been fighting it the past little while. I think if you've gotten this far you're doing great! Gord
ghubbell@total.net

 

[smiley0q0]

i have a join between the 2 orgs. there needs to be 3 amounts for each day, (one for each org), there are 13 days that should be listed. even with the join it still gives me either 507 or more results. i got this to work on my report, by making 1 qry for each date and then putting all those queries into 1, but i also need this in a graph by date and in my query there are 13 columns. my graph doesn't like that, so i am trying to get it all into 1 column... and can't seem to figure it out %-(

 

[ghubbell]

No angry faces Smiley. It's just a computer! Run the first query on its own. How many records does it give? Same same for the second on its own: how many? I think you might end up trying to either steal the Record Source from your graph and work from it adding your math field or, start playing with the unique records/values options in each query. The last is very dependant on what is in the query grid so you should start with the absolute minimum of fields, and each time you add one, check your results. Not much help today but always trying...keep me posted, and keep that smiley face! Gord
ghubbell@total.net

 

[smiley0q0]

ok, each query run by itself returns 39 records (each).
How do i steal the Record Source from my graph? i'll try playing with the unique records thingy and let you know what i come up with, thanks for the help. s-)

 

[ghubbell]

Now that's a cool smiley face. (I 've gotta learn how you do that...). If you go to your report and check what it's Record Source is (Design view, report properties, Data tab), might be some awful string or might be as simple as an existing query name) open it with "..." button and hit Save. Name it something and try running it on it's own. Now you can use it elsewhere or make a copy of it to play with.
Sometime when you have a chance Smiley, zip your Db over to me cause I'm fascinated with all your date issues and even going back to your question to start afresh or not. Alway's a pleasure to try to help. Gord
ghubbell@total.net

 

[smiley0q0]

i'd be happy to mail it over to you!!! just give me a minute... :-Q