[coachdan32]

I have a very weird problem. I have a report that is malfunctioning. A SQL statement is pulling data from my DB and is being "Grouped By" city. All of the cities totals are perfect, except one (Des Moines). It is listed twice in the recordset, despite being spelled exactly the same. There is one city that has been omitted from the dataset (Los Angeles). The data is weekly totals broken down week to week. Upon closer review, I found that the first 2 weeks of both Des Moines rows are identical and are the actual correct numbers for Des Moines. But weeks 3 and 4 are different when comparing the 2 Des Moines rows. The second Des Moines row has totals that actually match the Los Angeles data in weeks 3 and 4. The same SQL statement is pulling the data for all cities and the table is being populated dynamically from the SQL statement. Does anyone have any idea why the data might be getting scrambled? [File is attached]

[coachdan32]

Nobody has any guesses on this? I am not finding anything, need help!!

[coderitr]

Assuming you're using lookup tables for the city; i.e. the name of the city is not in the data but a code referring to a code in another table linked to the city name; there may be a duplicate entry for Des Moines in the lookup table with leading or trailing spaces on it -- making it non-distinct and having a different city code assigned to it. If you're not using a lookup table for the city, verify that there are no leading or trailing spaces in the city column in your data. Based on what you're saying, it looks like you have some data out of whack with the Des Moines / LA connection.

[coachdan32]

The lookup table assumption is correct, but there is only 1 Des Moines entry and there is a Los Angeles entry too. They both do not have spaces at the end or beginning. Both cities have data in the StdtProdAS table that should be reported.

I know this is a stupid observation because the db does not care, but it seems coincidental that both cities involved are 2 word cities with 3 letter words as the first. I know realistically the db doesn't care if you enter your ABC's there as long as it matches the datatype, but it's just weird that all the others (1 word cities) work just fine. I think it's possessed - know any demonic encantations to exorcise db hobgoblins?

But, seriously, if you have any other suggestions - I would love to here them because I am at my wits end on this one.

[coderitr]

I wish I did know some incantation that would magically solve database problems. There are days that I would sell my soul for some answers.

That is a bit odd that both the cities that you are having trouble with have two words in them. (Does San Francisco work? San Diego? Las Cruces?)

What language are you working in and what kind of access method are you using? Also, what report software are you working with? Would you be able to send me a sample of your work? I would keep it in strict confidentiality but I understand intellectual property too so if you can't that's fine.

[coderitr]

BTW: have you tried replacing the space in the name of the city with an underscore? Just wondering the the database engine or the report generator are having trouble with the space in the middle.

[coachdan32]

I am given pretty primitive tools to work with. They supply me with FrontPage or Notepad to use for an editor, and no tool for debugging. The db is Access and no reporting tool. The language being used is vbscript. The file is attached on the first post - I'm not sure what other code you might need, but I am willing to provide any you feel may help.

[coachdan32]

Tried using the underscore and it didn't make any difference.

[coderitr]

I'm sorry but what I know about the reporting embedded in Access could be written on the back of a postage stamp.

However, don't you have to have a saved query to run the report? If so, if you run the query from the Access interface, does the same thing happen? I wouldn't think the results would differ but then again, I don't really know.

Sorry I can't be more help.

[coderitr]

Try http://www.dbforums.com

Some really knowledgeable people there who I'm sure can give you more help than I.