Scrubbing Tables...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Yay I found this place!

I have a question that perhaps some of yoru expertise can answer.
The project has 2 tables.
Table A is the "master" table
Table B is a table which acquires info from an input device

I need to create a way to take information in Table B, compare it to Table A, and produce a report of all records which are present in Table A, but NOT present in Table B. (shortage report)

What makes things interesting is that the input device only enters in data that corresponds only to one field in the master table. I can do this with my eyes closed in php using mysql, but umm... that doesn't help me here.

I currently do NOT have MSSQL installed, although I could quite easily if it will make the solution easier. ALl of your help and advice is very much appreciated, and I thank you in advance. =p
 
Incendius said:
Yay I found this place!

I have a question that perhaps some of yoru expertise can answer.
The project has 2 tables.
Table A is the "master" table
Table B is a table which acquires info from an input device

I need to create a way to take information in Table B, compare it to
Table A, and produce a report of all records which are present in
Table A, but NOT present in Table B. (shortage report)

What makes things interesting is that the input device only enters in
data that corresponds only to one field in the master table. I can do
this with my eyes closed in php using mysql, but umm... that doesn't
help me here.

I currently do NOT have MSSQL installed, although I could quite
easily if it will make the solution easier. ALl of your help and
advice is very much appreciated, and I thank you in advance. =p
Click to expand...

The "Find Unmatched" query wizard will help you build a query to return
the records you want, and then you can base your report on that query.
The SQL of such a query will look something like this (allowing for the
fact that I don't know the table or field names involved):

SELECT tblMaster.*
FROM tblMaster LEFT JOIN tblInput
ON tblMaster.IDField = tblInput.IDField
WHERE tblInput.IDField Is Null;
 
Incendius said:
Thank you so much Dirk... I was just looking into that when I came
across another *small* problem...

The problem is that in the "master table" we have 2 different columns
which, COMBINED, is the value the input device enters. Meaning: Table
has job# and id#. Where the job# looks like "123-1234" and the id# is
as "01". The input device simply enters "123-123401" I am exhausted
in trying to combine the job# and id# into another table or query
*combined*. It seems as though this should be first, as ALL records
in the "master" have the same job number, but NOT the same id number.
Ugh. I would LOVE some help in this area, as I am pressed for time
and over-stressed hehe. Thanks again, and in advance again also. =p
Click to expand...

This is not a problem. You can join the tables on an expression that
concatenates the job# and id# fields from the Master table. It won't be
as efficient as a join on an indexed field, but it will work just fine:

SELECT tblMaster.*
FROM tblMaster LEFT JOIN tblInput
ON tblMaster.[Job#] & "-" & tblMaster.[ID#] = tblInput.JobID
WHERE tblInput.JobID Is Null;
 
Dirk, I love ya. Although that didn't work. hehe. Umm..

Basically, that allowed for the ID field to be inserted into the table (or query)... perhaps I should be a bit more clear..

I need the Job# AND the ID# to be combined in one field. I.e.;

Job# | ID
------------------------
123-1234 | 1

needs to be as

NewJob
-------------------
123-12341

Is this possible

Thanks so much for the exclusive help.. I managed to postpone this problem another day today, so *some* stress is lifted, and *especially* since there is someone out there that is nice enough to take the time to help a complete moron. hehe

Thanks again, I am in total appreciation.
 
Incendius said:
Dirk, I love ya. Although that didn't work. hehe. Umm...
Click to expand...

"Didn't work" is pretty vague.
Basically, that allowed for the ID field to be inserted into the
table (or query)... perhaps I should be a bit more clear...
Click to expand...

I'd appreciate it.
I need the Job# AND the ID# to be combined in one field. I.e.;

Job# | ID#
-------------------------
123-1234 | 15

needs to be as:

NewJob#
Click to expand...

Of course, but I'm not sure I understand what you're trying to do.
Since you've got a bit of a reprieve, let me ask a few questions.

Are you trying to modify the Master table to combine the two fields Job#
and ID# into one field? If so, why? Normally it's not a good idea to
store two pieces of information in one field. A query can always
combine fields, but it's not as easy or efficient to split them, and
normal SQL operations become difficult.

If you are asking to combine these fields just for the purpose of
matching against the IDs provided by your input device, the query I gave
you would be the right approach and should have been at least close to
working (after having the table and field names suitably adjusted). If
it didn't, please explain exactly in what way it didn't work. It might
also help if you posted the correct table and field names involved.
 
ugh hehe. Ok... where to start....

This is what I am trying to do:

I have a table which is exported from another application as an .xls file and linked in the .mdb. (export.xls)
In that table, there are 2 fields we are concerned with: "jobnumber" and "itemnumber".

I have a table in the mdb, which gets its records from a barcode scanner, and there is only one field:
"barcode". (table: barcode) Basically, this data includes the "jobnumber+itemnumber".

Example: <export.xls> jobnumber | itemnumber
123-456 | 15

Example: <table "barcode"> barcode
123-45615

Problem: I need to bounce "123-45615" against records in the linked table (export.xls) and create 2 reports.
Report a) Records in of items that have been scanned.
Report b) Records for which have yet to be scanned.

I hope this helps.
Thanks again for all of your help. I am losing my hair.
 
Incendius said:
ugh hehe. Ok... where to start....

This is what I am trying to do:

I have a table which is exported from another application as an .xls
file and linked in the .mdb. (export.xls) In that table, there are 2
fields we are concerned with: "jobnumber" and "itemnumber".

I have a table in the mdb, which gets its records from a barcode
scanner, and there is only one field: "barcode". (table: barcode)
Basically, this data includes the "jobnumber+itemnumber".

Example: <export.xls> jobnumber | itemnumber
123-456 | 15

Example: <table "barcode"> barcode
123-45615

Problem: I need to bounce "123-45615" against records in the linked
table (export.xls) and create 2 reports. Report a) Records in of
items that have been scanned.
Report b) Records for which have yet to be scanned.

I hope this helps.
Thanks again for all of your help. I am losing my hair.
Click to expand...

OK, I think I get the picture -- I believe I had things backwards
before. You didn't give me a name for the linked table, so I'll call it
Export. First, let's make a query that will extract distinct, composed
"barcodes" from this table. I don't know whether the itemnumber field
is numeric or text, and I don't know if it should always have two
digits. This is something you'll have to check and tell me, so we can
adjust the query if necessary. However, here's a starting point. Make
a new query named "qryExportBarcodes" and give it this SQL:

SELECT jobnumber & itemnumber AS barcode
FROM Export;

Having created and saved this query, open it as a datasheet and check
the results to see if they look correct.

Assuming the query appears correct, we can now use it in queries to get
Export items that have been scanned or have not been scanned.

Here is SQL for a query to get items that have not been scanned:

SELECT qryExportBarcodes.*
FROM qryExportBarcodes LEFT JOIN barcode
ON qryExportBarcodes.barcode = barcode.barcode
WHERE barcode.barcode Is Null;


Here is SQL for a query to get items that *have* been scanned:

SELECT DISTINCT qryExportBarcodes.barcode
FROM qryExportBarcodes INNER JOIN barcode
ON qryExportBarcodes.barcode = barcode.barcode;

Note -- the second of the two queries above returns only the barcodes of
the items. You didn't mention any other fields, and although it occurs
to me that you may want other data from table Export as well, I can't
properly design the query without knowing what fields you want. For the
moment, try these and tell me how close they come to giving you what you
want.
 
Nice work! That worked absolutely fantastically! Cool... You mentioned that I would like to include more data in the results of the queries, and you are correct. All of the fields from the "export" need to be spat out to the results.

The only anticipated problem I see might be the dash in the number system.

I might have to filter out that dash, simply because the scanner cannot be programmed to include the dash.
I also cannot set the external application to omit it. I wonder if we can eliminate it during the union of number+id?

Let me know what you think, and thanks a million for your assistance!!!
 
Incendius said:
Nice work! That worked absolutely fantastically! Cool... You
mentioned that I would like to include more data in the results of
the queries, and you are correct. All of the fields from the "export"
need to be spat out to the results.

The only anticipated problem I see might be the dash in the number
system.

I might have to filter out that dash, simply because the scanner
cannot be programmed to include the dash.
I also cannot set the external application to omit it. I wonder if we
can eliminate it during the union of number+id?
Click to expand...

So let me see if I have this straight: table [barcode] will *not* have
the dash in the barcode field, while table [Export] *will* have the dash
(in the Job# field)? We must change qryExportBarcodes both to include
all fields from table [Export], and to drop the dash. If you're using
Access 2002 or later, or some later updates of Access 2000, you can
rewrite qryExportBarcodes like this:

SELECT
Replace(jobnumber & itemnumber, "-", "") AS barcode,
Export.*
FROM Export;

If you're using an earlier version of Access, such that the Replace
function isn't defined, let me know and I'll post a substitute for the
Replace() function.

Once you've modified qryExportBarcodes as described above, try this for
the query to return items that have been scanned:

SELECT DISTINCT qryExportBarcodes.*
FROM qryExportBarcodes
WHERE qryExportBarcodes.barcode IN
(SELECT barcode.barcode FROM barcode);

The query to return items that have *not* been scanned shouldn't need to
be changed.
 
Incendius said:
Bleh! MSACCESS2000 and Undefined Function: Replace. Hmm. I hate to be
a pest.

That's the stump I am sitting on. =(
Click to expand...

Paste the following function into a standard module:

'----- start of code -----
Function fncReplace(Expression As String, _
Find As String, _
Replace As String, _
Optional ByVal Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
' when that function is not available.

Dim strResult As String
Dim lngReplaceCount As Long
Dim lngPos As Long

If Len(Find) > 0 Then
Do
lngPos = InStr(Start, Expression, Find, Compare)
If (lngPos) Then
If Count > -1 Then
lngReplaceCount = lngReplaceCount + 1
If lngReplaceCount > Count Then
Exit Do
End If
End If
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) & _
Replace
Start = lngPos + Len(Find)
End If
Loop Until lngPos = 0
End If

fncReplace = strResult & Mid$(Expression, Start)

End Function
'----- end of code -----

Then use "fncReplace" in the query where I previously said "Replace".
 
SELECT
fncReplace([JobNumber] & [ID],"-","") AS barcode,Export.*
FROM Export;

Gives "undefined function fncReplace" error =(( I thought I did something wrong, but I cannot seem to locate the cause of this problem.

Should I start banging my head on the desk now? heh. Thanks again, Dirk.. this does mean a lot to me.
 
Incendius said:
SELECT
fncReplace([JobNumber] & [ID],"-","") AS barcode,Export.*
FROM Export;

Gives "undefined function fncReplace" error =(( I thought I did
something wrong, but I cannot seem to locate the cause of this
problem.

Should I start banging my head on the desk now? heh.
Click to expand...

Hold off just a bit; I think we're almost there.

Did you paste the function into a *standard* module, or into a form or
class module? It must be in a standard module.
 
Incendius said:
If it helps any... the characters will always be 3 digits before the
dash!
Click to expand...

If that's completely reliable, it enables us to write this:

SELECT
Left(jobnumber, 3) & Mid(jobnumber, 5) & itemnumber
AS barcode,
Export.*
FROM Export;

However, there's no reason the fncReplace() function shouldn't work.
 
I simply clicked "New" in the Modules tab and pasted the following

'Replace Module Start
Function fncReplace(Expression As String,
Find As String,
Replace As String,
Optional ByVal Start As Long = 1,
Optional Count As Long = -1,
Optional Compare As Long = vbBinaryCompare)
As Strin

' Functional equivalent of the A2K Replace() function, to be use
' when that function is not available

Dim strResult As Strin
Dim lngReplaceCount As Lon
Dim lngPos As Lon

If Len(Find) > 0 The
D
lngPos = InStr(Start, Expression, Find, Compare
If (lngPos) The
If Count > -1 The
lngReplaceCount = lngReplaceCount +
If lngReplaceCount > Count The
Exit D
End I
End I
strResult = strResult &
Mid$(Expression, Start, lngPos - Start) &
Replac
Start = lngPos + Len(Find
End I
Loop Until lngPos =
End I

fncReplace = strResult & Mid$(Expression, Start

End Functio
'Replace Module End

::: After that, I opened up the "qryExportBarcodes" Query, and typed in

SELECT fncReplace(jobnumber & ID, "-", "") AS barcode, Export.*

Upon running the Query, I get a dialog error of
"Undefined function 'fncReplace' in expression

I noticed also that [brackets] were added to
([jobnumber] & [ID], "-", "") as such. I deleted them, thinking it would make a difference, but they were persistent! =

That's what's going on at the moment..
 
Incendius said:
I simply clicked "New" in the Modules tab and pasted the following:


'Replace Module Start'
Function fncReplace(Expression As String, _
Find As String, _
Replace As String, _
Optional ByVal Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
' when that function is not available.

Dim strResult As String
Dim lngReplaceCount As Long
Dim lngPos As Long

If Len(Find) > 0 Then
Do
lngPos = InStr(Start, Expression, Find, Compare)
If (lngPos) Then
If Count > -1 Then
lngReplaceCount = lngReplaceCount + 1
If lngReplaceCount > Count Then
Exit Do
End If
End If
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) &
_ Replace
Start = lngPos + Len(Find)
End If
Loop Until lngPos = 0
End If

fncReplace = strResult & Mid$(Expression, Start)

End Function
'Replace Module End'

SELECT fncReplace(jobnumber & ID, "-", "") AS barcode, Export.*;

Upon running the Query, I get a dialog error of:
"Undefined function 'fncReplace' in expression"

I noticed also that [brackets] were added to:
([jobnumber] & [ID], "-", "") as such. I deleted them, thinking it
would make a difference, but they were persistent! =p


That's what's going on at the moment...
Click to expand...

Did you save the module after pasting in the function code? Do so, and
save it under some name other than "fncReplace" -- "Module1" or
"basUtilities" are equally acceptable. That ought to be enough to let
the function be recognized. If it isn't, something odd is going on.
SELECT fncReplace(jobnumber & ID, "-", "") AS barcode, Export.*;
Click to expand...

This isn't the complete query, I trust -- it's certainly not all of what
I posted. The bracketing is normal, so don't worry about it.
 
Dirk, definately an MVP in my eyes. It works great! I am still in the process of checking it all out, but as far as I can see already, it seems to be working marvelously! Thank you sooo much for your attention, skill, and patience

I know already that I have an important decision to make about this database -so I will need some sound advice. I should post it in a new thread, however, as it's quite apparent that this issue is resolved

Thanks again Dirk, it seems there are several resources in this post alone that could help many. Wouldn't you agree

=p
 
Back
Top