Update Query needed....

[Bikini Browser]

I need an update query written. Are there any takers? It looks hard, but I
don't think an experienced query writer will have any problems with it.. Am
I wrong? I will be happy to send some money to the taker for this query via
PayPal. Please help me.



The goal here is to be able to sort out local numbers from long distance
numbers and write out a new table for each one, overwriting any existing
tables.



Please observe the architecture below...




Tablename "Prefixes" has four columns.



1 2 3
4

Column Names: "Zone" "City" "Prefix"
"Type"



Only fields I need to look at is the "Prefix" field and the "Type" Field.



The value of "Type" is either "Local" or "Isla" "Local" are Local Calls
and "Isla" are long distance.



Table name Posted_Listings has several columns but all I care about is the
column that is labeled says "FaxNumber".



I need an update query written that will look into the Prefixes table (the
Prefix column) and match the first 7 Charaters in Posted Listings Table (The
FaxNumber column)



Example: If the prefix is 787-722 in Prefixes table (the Prefix column) and
the fax number is 787-722-9846 in Posted Listings Table (The FaxNumber
column) it will be a match.



If you find a match, then look into the Prefixes table (the "Type" column)
and write the entire record from Posted_Listings to a new table called
LocalFaxNumbers.



The same for Long distance numbers, or Type "Isla" numbers. The table name
will be IslaFaxnumbers. However, if the Type is ISLA, then insert a "1-" in
front of the number for I IslaFaxnumbers, table "FaxNumber" Field.



There will be some cases that the prefix dos not match. Then (almost done)
if you find a prefix number in the "FaxNumber" column of the Posted_Listings
table that does not match any of the prefixes, in the Prefixes table, in the
Prefix Column, then write that record to the new table too.



Finally, if there is a way, I would like the query to count 2000 records
from each new table, and write new tables for them.



So I end up with tables like this.

Localfax1

Localfax2

Localfax3

Localfax4

Localfax5

Localfax6

And so on until all the records are written out from the table
LocalFaxNumbers.



The same for Isla Fax numbers.

Islafax1

Islafax2

Islafax3.

How do you do that?



I am guessing that this request will have to be split into several steps.
Am I right?



Do you have questions? Please write back soon.



Thanks


Dale

 

[John Vinson]

I need an update query written. Are there any takers? It looks hard, but I
don't think an experienced query writer will have any problems with it.. Am
I wrong? I will be happy to send some money to the taker for this query via
PayPal. Please help me.



The goal here is to be able to sort out local numbers from long distance
numbers and write out a new table for each one, overwriting any existing
tables.

WHY?

A Select query will work, feel, and function JUST like a table. Having
two identically structured tables for this purpose is neither
necessary nor beneficial.

Please observe the architecture below...
Tablename "Prefixes" has four columns.
1 2 3
4

Column Names: "Zone" "City" "Prefix"
"Type"



Only fields I need to look at is the "Prefix" field and the "Type" Field.



The value of "Type" is either "Local" or "Isla" "Local" are Local Calls
and "Isla" are long distance.



Table name Posted_Listings has several columns but all I care about is the
column that is labeled says "FaxNumber".



I need an update query written that will look into the Prefixes table (the
Prefix column) and match the first 7 Charaters in Posted Listings Table (The
FaxNumber column)



Example: If the prefix is 787-722 in Prefixes table (the Prefix column) and
the fax number is 787-722-9846 in Posted Listings Table (The FaxNumber
column) it will be a match.



If you find a match, then look into the Prefixes table (the "Type" column)
and write the entire record from Posted_Listings to a new table called
LocalFaxNumbers.

This is not an Update query (which updates existing records in a
table); it's either a MakeTable query, or (better) an Append query.
You'ld create the LocalFaxNumbers and IslaFaxNumbers tables, empty,
and run append queries to do so.

A SELECT query to do this matching would be

SELECT [FaxNumber] FROM [Posted_Listings]
INNER JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings], 7)
WHERE [Prefixes].[Type] = "Local";

The same for Long distance numbers, or Type "Isla" numbers. The table name
will be IslaFaxnumbers. However, if the Type is ISLA, then insert a "1-" in
front of the number for I IslaFaxnumbers, table "FaxNumber" Field.

SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings LEFT JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings], 7)
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;

There will be some cases that the prefix dos not match. Then (almost done)
if you find a prefix number in the "FaxNumber" column of the Posted_Listings
table that does not match any of the prefixes, in the Prefixes table, in the
Prefix Column, then write that record to the new table too.

The LEFT JOIN will do this.

Finally, if there is a way, I would like the query to count 2000 records
from each new table, and write new tables for them.

That's SIMPLY WRONG. You're treating tables as if they were
spreadsheets, or reports. Store the data in a Table - your *current
existing table* - and then use a TOP 2000 query to extract 2000
records, then a TOP 4000 query excluding the top 2000, and so on.

Could you explain WHY you want Tables, when queries would work
perfectly well? What use will you make of these?

John W. Vinson[MVP]

 

[Bikini Browser]

Hello John:

I use Winfax to Send faxes. Winfax needs to use tables and columns.

And Winfax will only allow 2000 faxes to go at a time or it will begin to
crash.

I have to have tables.

Dale

I need an update query written. Are there any takers? It looks hard, but
I
don't think an experienced query writer will have any problems with it..
Am
I wrong? I will be happy to send some money to the taker for this query
via
PayPal. Please help me.



The goal here is to be able to sort out local numbers from long distance
numbers and write out a new table for each one, overwriting any existing
tables.

WHY?

A Select query will work, feel, and function JUST like a table. Having
two identically structured tables for this purpose is neither
necessary nor beneficial.

Please observe the architecture below...
Tablename "Prefixes" has four columns.
1 2 3
4

Column Names: "Zone" "City" "Prefix"
"Type"



Only fields I need to look at is the "Prefix" field and the "Type" Field.



The value of "Type" is either "Local" or "Isla" "Local" are Local Calls
and "Isla" are long distance.



Table name Posted_Listings has several columns but all I care about is the
column that is labeled says "FaxNumber".



I need an update query written that will look into the Prefixes table (the
Prefix column) and match the first 7 Charaters in Posted Listings Table
(The
FaxNumber column)



Example: If the prefix is 787-722 in Prefixes table (the Prefix column)
and
the fax number is 787-722-9846 in Posted Listings Table (The FaxNumber
column) it will be a match.



If you find a match, then look into the Prefixes table (the "Type" column)
and write the entire record from Posted_Listings to a new table called
LocalFaxNumbers.

This is not an Update query (which updates existing records in a
table); it's either a MakeTable query, or (better) an Append query.
You'ld create the LocalFaxNumbers and IslaFaxNumbers tables, empty,
and run append queries to do so.

A SELECT query to do this matching would be

SELECT [FaxNumber] FROM [Posted_Listings]
INNER JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings], 7)
WHERE [Prefixes].[Type] = "Local";

The same for Long distance numbers, or Type "Isla" numbers. The table
name
will be IslaFaxnumbers. However, if the Type is ISLA, then insert a "1-"
in
front of the number for I IslaFaxnumbers, table "FaxNumber" Field.

SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings LEFT JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings], 7)
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;

There will be some cases that the prefix dos not match. Then (almost
done)
if you find a prefix number in the "FaxNumber" column of the
Posted_Listings
table that does not match any of the prefixes, in the Prefixes table, in
the
Prefix Column, then write that record to the new table too.

The LEFT JOIN will do this.

Finally, if there is a way, I would like the query to count 2000 records
from each new table, and write new tables for them.

That's SIMPLY WRONG. You're treating tables as if they were
spreadsheets, or reports. Store the data in a Table - your *current
existing table* - and then use a TOP 2000 query to extract 2000
records, then a TOP 4000 query excluding the top 2000, and so on.

Could you explain WHY you want Tables, when queries would work
perfectly well? What use will you make of these?

John W. Vinson[MVP]

 

[John Vinson]

Hello John:

I use Winfax to Send faxes. Winfax needs to use tables and columns.

And Winfax will only allow 2000 faxes to go at a time or it will begin to
crash.

I have to have tables.

Two suggestions:

1. Change the Select queries I posted into MakeTable queries, and set
the query's Top Values property to 2000 for the first one; then to
4000 with a subquery

NOT IN(SELECT TOP 2000 ...)

on the primary key for the second, and similarly (6000, 8000, etc.)
for subsequent queries.

2. See the links at http://www.granite.ab.ca; search for Fax, and
follow the links about OLE automation.

John W. Vinson[MVP]

 

[Bikini Browser]

If Winfax can use a MS Access Query object, I don't know how to do it.

Dale

 

[Bikini Browser]

I tried to run your queries. I get an error message that says "Join
Expression is not supported."

Any Ideas on what could be wrong?

SELECT "1-" & [FaxNumber] AS NewFax FROM Posted_Listings LEFT JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings], 7) WHERE [Prefixes].[Type] =
"Isla" OR [Prefixes].[Type] IS NULL;
and

SELECT [FaxNumber] FROM [Posted_Listings] INNER JOIN Prefixes ON
Prefixes.Prefix = Left([Posted_Listings], 7) WHERE [Prefixes].[Type] =
"Local";

Dale

 

[Chris2]

Hello John:

I use Winfax to Send faxes. Winfax needs to use tables and columns.

And Winfax will only allow 2000 faxes to go at a time or it will begin to
crash.

I have to have tables.

Dale

Bikini Browser,

So, WinFax can be fed an MS Access Table object, but not an MS
Access Query object?


Sincerely,

Chris O.

 

[John Vinson]

I tried to run your queries. I get an error message that says "Join
Expression is not supported."

rats... <g>

Try

SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings
LEFT JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings].[Fax Number], 7)
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;

or, if that doesn't work,

SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings
LEFT JOIN Prefixes
ON [Posted_Listings] LIKE Prefixes.Prefix & "*"
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;

John W. Vinson[MVP]

 

[Bikini Browser]

That works but I need it to write a new table as requested before...

All I can do is cut and paste. I don't know how to do anything else. Can
you write the actual query for me?

Thanks

Dale

I tried to run your queries. I get an error message that says "Join
Expression is not supported."

rats... <g>

Try

SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings
LEFT JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings].[Fax Number], 7)
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;

or, if that doesn't work,

SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings
LEFT JOIN Prefixes
ON [Posted_Listings] LIKE Prefixes.Prefix & "*"
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;

John W. Vinson[MVP]

 

[John Vinson]

All I can do is cut and paste. I don't know how to do anything else. Can
you write the actual query for me?

Sure. I'll send my consulting terms upon request. I have an eight hour
minimum, but my rates aren't extremely high.

I'd suggest instead that you use the F1 key and search for Help on
"Append" and "Make Table" queries. The query grid really does make
this pretty easy, if you look.

John W. Vinson[MVP]

 

[Chris2]

If Winfax can use a MS Access Query object, I don't know how to do it.

Dale

Bikini Browser,

I apologize that I have never used WinFax.

Do you use WinFax to select the Tables in MS Access to supply data
to WinFax? (Using a dialog box or other tyupe of management screen
of some sort?) If so, if you scroll down far enough, does it show
Query objects instead of Table objects? Or does it always show
Table objects only?


Sincerely,

Chris O.

 

[Bikini Browser]

John:

I tried to use the grid like you said to do, but it gives me an error.
Would you consider working with me for only 1 hour to fix my errors and
setup the query as I asked? You can access my comptuter remotely and I can
call you to explain the problem. I can pay you using PayPal.

Are you game? What is your fee?

The query is really close but has a bug in it.

Dale

 

[John Vinson]

John:

I tried to use the grid like you said to do, but it gives me an error.
Would you consider working with me for only 1 hour to fix my errors and
setup the query as I asked? You can access my comptuter remotely and I can
call you to explain the problem. I can pay you using PayPal.

Are you game? What is your fee?

The query is really close but has a bug in it.

I'm tied up this weekend but should be able to get a few minutes to
look at it next week. Please email me directly at jvinson <at>
wysard of info <dot> com (edit out the blanks and fix the punctuation)
and I'll send you terms.

Or... post the SQL of your query and the error, and I or another
volunteer may be able to spot the problem.

John W. Vinson[MVP]

 

[Bikini Browser]

Ok.

Dale

I'm tied up this weekend but should be able to get a few minutes to
look at it next week. Please email me directly at jvinson <at>
wysard of info <dot> com (edit out the blanks and fix the punctuation)
and I'll send you terms.

Or... post the SQL of your query and the error, and I or another
volunteer may be able to spot the problem.

John W. Vinson[MVP]

 

[John Vinson]

Ok.

ok... what?

Haven't gotten your email, and you haven't posted the SQL.

John W. Vinson[MVP]

 

[Bikini Browser]

I am using the same SQL you sent me. When I try to run the Make-table Query,
it says that the Join Function can't be supported in design view.

I can't find a way to avoid the design view unless I skip using a Make-table
Query. I really want to setup a time for you to access my computer remotely
and help me achieve the goals I need.

Do you also write vb code? I might need a modification to my database and I
think you need to write code for it. Additionally I need bugs fixed.

Please call me or write to my email address removing the Stop-Spam part of
the message.

Here are the two SQL queries I tried....

I need them to make tables with all the fields from Posted Listings in that
those tables, and remember I need to have 2000 records per table maximum.

Dale


Try
SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings
LEFT JOIN Prefixes
ON Prefixes.Prefix = Left([Posted_Listings].[Fax Number], 7)
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;

or, if that doesn't work,

SELECT "1-" & [FaxNumber] AS NewFax
FROM Posted_Listings
LEFT JOIN Prefixes
ON [Posted_Listings] LIKE Prefixes.Prefix & "*"
WHERE [Prefixes].[Type] = "Isla" OR [Prefixes].[Type] IS NULL;