Query error when data changes in crosstab source query.

[Allan]

I have a database which among other things records how jobs are received – i.e.: Telephone, Email, Mail, Facsimile or Web.

For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.

I have created a make table query for all jobs received between variable dates for a client – entry of the name of the client and the start and finish dates are required to run the query.

I have a crosstab query set up to count each method of receipt and a final query to work out the percentages – using the total from the crosstab query fields divided by the total of all methods.

I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different ‘methods of receipt’ may vary for the less active clients i.e.: they may only have telephone and email .

My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.

My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.

I may be doing this the long way but would appreciate any help please.

 

[Allan]

Thank you for your response but I am not too sure if I explained the problem correctly or how looking at the table fields will help.

The attached are screen snips of the database and the sequence I am following.

The tblAnchor is the main table from which I am extracting the information by running a query. That query creates a separate table with the required information about a particular client.

I then run a crosstab query to find the number of contacts and by what method for that client.

The final query calculates the percentages for each category. When the client does not have a record from say a WEB contact as per the example, the final query will not work.

What I was trying to identify was if it is possible to program the query to ignore a non existent field. It works fine if each category is present but some clients will only have telephone or email contact with us.

I recognise I may be taking the long road but I am willing to try any suggestions.

 

[vbaInet]

I can't understand your reluctance in answering the question, unless you have already asked the question elsewhere, and didn't like the answer.

The screenshot "Main database tblAnchor" has that information.

 

[Allan]

Hi,
If the question is what the table looks like, I have provided a screen grab of the table itself. I found it easier than the steps you suggested and provided the same information. Is there other data using your methods you need to see? Otherwise I am not too sure what you are looking for.

 

[Allan]

This is the only forum I have posted on and being a novice I don't know enough to "not like an answer". I just wanted to know if what I am trying to do is possible. I know why I am getting the error and am looking for advice on whether there is another way.

 

[vbaInet]

The very last field in the screenshot Uncle Gizmo. Look at the description of that field.