Matching zero values with data values for the same date

[Raya]

Hi There

In my database I have a list of agents.
Further to that, they do 2 actions of monatery value, whic we we use to measure them.

I get a list of their names and that value from 2 flat files which I import into my tables.
I have managed to query the data seperately.
However, I want to add both values onto one report.

I have managed to query this correctly, but only for the days where both tables have values.
The other way I have tried only ended up using the previous days value in the missing days.

So, I need to find out how to I query the tables so that it shows a zero when that value for an agent is not there.

Regards

[JoeM]

I think a lot of it is dependent on your table structures, the nature of the realtionships between the tables, and what you want your resulting data look like, but the NZ function may be what you are looking for. You use the NZ function to tell it what to return in the event of a null value (i.e. unmatched value).

See the following for help on the NZ function: http://www.techonthenet.com/access/f...dvanced/nz.php

[Raya]

Thanks for you quick reply and the link on how to use the NZ function.
However, I think I'm still lost.

I have added screen shots of what I have done so far.
I now have 3 queries:

Full Name, where I take the Name and Surname fields from my agent lists and make them one field.
PTP Balance, here I match the Full Name to the Monatery Value (PTP Balance) and the date.
Reschedule Balance, I do the same as the above, but I sum the balances of the same day and agent to give me one total for the agent for the day.

I then link the last 2 queries to the date table in my query.
This then only matches the same dates.
However, if there is PTP value for agent for that day, but not a Reschedule value it gets left out.


Does the NZ funtion allow use of more that one parameter?

[JoeM]

It sounds like you have a linking issue in your query. It sounds like you want to use Left Joins.
The default is Inner Joins, which only return records if there are matches.
Left Joins essentially say, return ALL the records from one table/query, and then all the records the match (and returns Nulls for the unmatched ones, this is where you make use of the NZ function). If you double-click on the join lines in your Query builder, it will show you the different join options.

See here for more details on joins: http://www.about-access-databases.co...tionships.html