Sabtu, 29 November 2014

[MS_AccessPros] Queries Work Then They Don't

 

I have an application where I reload a number of property related tables from a list maintained on a spreadsheet by the users. The list contains all of the details for the property, id, name, region, country, city, etc. I take all of the attributes into separate lists, de-dup them, add a numeric id for each, delete the existing items in that attributes tab le (e.g. region), and load each of the newly derived id/name pairs into their own table.

 

This works fine, all of the tables are loaded and look fine.

 

I also have some simple queries to produce lists of all of these attributes that I can use in an excel app. A typical SQL for one of these list is as follows

 

SELECT TOP 1  0 AS RegionID, "[All Regions]" AS Region

From Region

 

UNION ALL SELECT reg.RegionID, reg.Region

FROM Region AS reg

ORDER BY 2;

 

The first time I run this query, it works fine. If I then reload the property table, something odd happens. All of the tables look fine, but some of those list queries return nothing – the table has all of the details I would expect, but the query doesn't work.

 

If I delete the table, and reload them all it works, but the next reload we are back to missing lists.

 

Region seems to be the worst culprit, but Country and City and showing similar behaviour. Each of these tables are simple numeric ID and text name columns, primary key on the ID. I don't have the relationships setup in the database yet.

 

Anyone have any idea what is going on, and how I can prevent it?

__._,_.___

Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

.

__,_._,___

Tidak ada komentar:

Posting Komentar