Bob-
Are you saying that Region is a linked table from Excel? If not, I'm not seeing what you are doing.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Nov 29, 2014, at 4:20 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
A couple of points I didn't clarify earlier is that I cannot query the table using a simple SELECT fields query on these tables (although if I double-click Region in the Tables pane I see all of the data), and I am not using the Access UI, I just use the database to load and query from Excel.
I like the idea of the null values table, but it seems to me that the problem is not in the query but somehow the table has been compromised by the reload. Applying your suggestion made no difference, it still listed nothing.
Bob
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 29 November 2014 13:41
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Queries Work Then They Don't
Sent: 29 November 2014 13:41
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Queries Work Then They Don't
Bob-
It's always problematic trying to create an "All" value for the Row Source of a combo box. My technique is to create a dummy table with an AutoNumber Primary Key and one each of the data types that I might want to use as the "key" value for my dummy row. I call this table ztblNullID with a Long Integer (NullLong), Integer (NullInt), and Text (NullText) fields, and I make sure that the Text field has Allow Zero Length set to No. I then create one row by typing a space in the Text field and saving the row. This results in a Null value in the two number fields and the text field. Then in your situation I would do:
SELECT NullLong, "[All Regions]" As Region
FROM ztblNullID
UNION
SELECT reg.RegionID, reg.Region
FROM Region
ORDER BY Region;
Note that the "All Regions" value will never show in the combo box because the bound value is a Null, but the user can select that row to set the combo to Null to indicate you want all.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Nov 29, 2014, at 11:01 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar