How to create this query

[CJA]

Hi,

I have two tables. One is a "sale_details" like this

sales# date item
1 16-12-2006 mobile phone serial#123
2 16-12-2006 computer serial#456
3 16-12-2006 printer serial#789


other is a "serial#" table like this

item serial# sales_date sales#
mobile phone 123
computer 456
printer 789

I need to create some procedure (update query???) for once a week update
table "serial#" with corresponding sales_date and sales# from table
"sale_details"

Thanks for helping!!!

CJA

 

[Ken Snell \(MVP\)]

Do I understand correctly that the "sales_details" table contains both the
name of the item and the serial number info for that item in the "item"
field? First thing to do is to split that field into two fields in that
table, one for item and one for serial number. Then it'll be much easier to
run a query as you wish to do -- in fact, you then may be able to eliminate
the "serial#" table entirely because you could always run a select query
that would produce the data that you currently store in the "serial#" table.

 

[CJA]

The problem is i cannot change anything in "sale_details" table because she
is part of a commercial software.
In this situation we have two programs. One commercial for manage the store
and another small one (in access and made by me)to control serial#
The actual problem is every friday someone have to pick all the invoices and
if there is a serial# have to manually insert that information in access
program.

Another thing... serial# dont have always the the same # of digits and
sometimes have letters.

Thanks!

 

[Ken Snell \(MVP\)]

How will ACCESS know what part of the text in the "item" field in
"sale_details" table is the serial number? Is it always preceded by the text
string "serial#"? In order to come up with a query, it's necessary to know
what must be parsed from the text.

 

[CJA]

I have this procedure in my mind but dont know how to do it.
Firts create a query to show only lines in "serial#" where 'sales_date' and
'sales#' are empty.(this is easy!!)
Second, in the same or another query search for lines in table
"sale_details" where in some part of the field 'item' (like *123*)
correspond a 'serial#' of "serial#" table
Next, update table "serial#" fields 'sales_date' and 'sales#' with the
values of the "sale_details"

Thanks!

 

[Ken Snell \(MVP\)]

Will the "item" field in the "sale_details" table always contain the text
string "serial#" just before the actual serial number?

 

[CJA]

No, it depends on what the user write... sometimes just "S/N" or "Serial" or
"#" or only the number "123"

Thanks!

 

[Ken Snell \(MVP\)]

Then it'll be nearly impossible to parse out the serial number unless you
can identify a logical method for the database to use. Even if you put a
serial number in a table and search for it in the 'item' field, you can get
erroneous matches. For example, suppose that the serial number were 'A123'.
If the item were named "Camera123 Snapshot" and the 'item' field contained
"Camera123 Snapshot S/N A123", you'd find the wrong text.

So, look at your data very carefully, and see how a human would make
decisions about how to find the serial number information in the data. Then,
write down the logic decisions that you use. Then, we can teach your
database to do the same after we know what those rules are.