Multiplying Records

  • Thread starter Thread starter Alan Nicoll
  • Start date Start date
A

Alan Nicoll

I have set up a Select Query but in looking at the
results, each record from the database is included
multiple times.

The problem is in how I've set up the links between the
tables. I am processing data output from another database
system in which the Shift and Unit data are based on codes
which are repeated for the three organizational divisions
that I'm working with. For example, Code "E" under
Division "A" means one thing, and Code "E" under
Division "O" means another thing. In the tables I've set
up to decode these into "human-readable form," I have a
column for Division, a column for the input code, and a
column for the output.

Now, in the Query I have two links from the database to
the decoding table, and that seems to result in the
multiplying records. How do I get the decoding to work
correctly? I think this will probably solve the
multiplying records I'm getting.

Help!

Alan Nicoll
 
Seems like what I need is a Lookup function; can I do this
in a Query? Or where does it go? I'm working without a
book and the help system is difficult to understand.

Thanks!

Alan
 
-----Original Message-----
Seems like what I need is a Lookup function; can I do this
in a Query? Or where does it go? I'm working without a
book and the help system is difficult to understand.
Click to expand...

I'm trying the following, but it doesn't work:

DLookUp([Units]![Unit Name],[RawReport],[Units]![Division]=
[RawReport]![Div] And [Units]![Unit Code]=[RawReport]!
[Unit])

Regardless of what I put as a domain name (i.e.,
[RawReport] in the above), it keeps asking for the
domain. RawReport is an Excel file that is linked to
Access and forms the major data input of the database.

Please help.

Alan
 
-----Original Message-----
Seems like what I need is a Lookup function; can I do this
in a Query? Or where does it go? I'm working without a
book and the help system is difficult to understand.
Click to expand...

I'm trying the following, but it doesn't work:

DLookUp([Units]![Unit Name],[RawReport],[Units]![Division]=
[RawReport]![Div] And [Units]![Unit Code]=[RawReport]!
[Unit])

Regardless of what I put as a domain name (i.e.,
[RawReport] in the above), it keeps asking for the
domain. RawReport is an Excel file that is linked to
Access and forms the major data input of the database.

Please help.

Alan
Click to expand...

The three arguments to DLookUp must be TEXT STRINGS. If you want to
look up the field UnitName from the table Units - I have no idea what
RawReport might be - the syntax would be (mind the quotes):

=DLookUp("[Unit Name]", "[Units]", "[Division] = " & RawReport.Div & "
AND [Unit Code] = '" & RawReport.Unit & "'")

I'm assuming that Division is a numeric field and Unit Code is text -
if that's not the case, use the ' marks for Text fields and no
delimiter for number fields. And if I've misinterpreted the
relationship between [Units] and [Raw Report] please explain!
 
John, thanks very much for your help.

I pasted your code into my query table and got the
following error message when I tried to go to Datasheet
view:

The expression you entered as a query parameter produced
this error: 'Microsoft Access can't find the name 'A' you
entered in the expression.

'A' is apparently data, a division code. I'll explain
something about the tables involved. RawReport is a link
to an Excel spreadsheet. All the fields (Excel cells)
involved contain text, and in particular, columns "Div,"
and "Unit" contain single alphanumeric characters. I need
to translate the Unit to a human-readable form.

The difficulty (as I see it) is that I need to reference
two columns in RawReport (and in the decoding
table, "Units") to get the right lookup. That is, there
are three divisions involved (Div) and dozens of units, so
Div determines how Unit is to be decoded, like this:

Div Unit Result desired
A A AOU
A B DOU
A C ... etc.
E A ER
E B ... etc.
O A Not Used
O B ... etc.

The decoding table (called Units) essentially is in the
form shown above. The columns (fields) in Units are named
Division, Unit Code, and Unit Name.

I want to do this without getting into Visual Basic,
because I originally created this system in Excel with
very extensive VBA programming, and realized that when I'm
done with this job, realistically, nobody would be able to
maintain the system. If I can do it all in Access, it
should be easy to maintain.

Thanks again for your help.

Alan
 
John, your code has (after my reformatting):

"[Division] = " &
RawReport.Div &
"AND [Unit Code] = '" &
RawReport.Unit &
"'"

Seems like the syntax should be the same for both parts of
the AND. I don't see why one of the RawReport items
should have single quotes around it but the other not.

Alan
 
John, your code has (after my reformatting):

"[Division] = " &
RawReport.Div &
"AND [Unit Code] = '" &
RawReport.Unit &
"'"

Seems like the syntax should be the same for both parts of
the AND. I don't see why one of the RawReport items
should have single quotes around it but the other not.

Alan
Click to expand...

Because, as I said:

I'm assuming that Division is a numeric field and Unit Code is text -
if that's not the case, use the ' marks for Text fields and no
delimiter for number fields. And if I've misinterpreted the
relationship between [Units] and [Raw Report] please explain!

You did not say in your message whether the fields were numeric or
text. I guessed. I obviously guessed wrong.

The field or fields that are of Text type should have quotemarks. The
field or fields that are numeric should not have quotemarks. YOU know
which datatype your fields are; apply the quotes appropriately and
it'll work!
 
John, it worked, thanks!

I put single quotes around the Div specification:

Expr2: DLookUp("[Unit Name]","[Units]","[Division] = '" &
[RawReport].[Div] & "' AND [Unit Code] = '" & [RawReport].
[Unit] & "'")

This is some mind-boggling syntax to do something
seemingly pretty simple.

BTW, I did address your questions... see my previous
message (not the one you replied to, but the other at the
same level in the list).

Thanks again... I'm sure I'll be back.

Alan
 
Yes, it works, but it's way too slow... when it gets up to
5 minutes (to process ~900 records), I break it off.
Doing the same task and much more in Excel under Visual
Basic goes in the blink of an eye.

Is a Select Query the proper place to do this simple
decoding thing? I stripped down the query to just two
items, and it still takes forever.

I can think of one way that would make it faster: combine
the two codes (Div and Unit) into a single code. This
turns it into a one-term lookup, which I've seen goes much
faster. But I'd rather not do this if I can avoid it,
since I'd also have to do it for my Shift codes, which
present the same problem.

Seeking guidance.

Alan
 
Yes, it works, but it's way too slow... when it gets up to
5 minutes (to process ~900 records), I break it off.
Doing the same task and much more in Excel under Visual
Basic goes in the blink of an eye.

Is a Select Query the proper place to do this simple
decoding thing? I stripped down the query to just two
items, and it still takes forever.

I can think of one way that would make it faster: combine
the two codes (Div and Unit) into a single code. This
turns it into a one-term lookup, which I've seen goes much
faster. But I'd rather not do this if I can avoid it,
since I'd also have to do it for my Shift codes, which
present the same problem.

Seeking guidance.
Click to expand...

Yes, if you can create a Query joining the lookup table it will be
more efficient. The Div and Code fields should be indexed in both
tables; you can join on the two fields (up to ten fields actually) so
combining them into one field would be neither necessary nor a good
idea!

Just create a Query joining the two tables. Probably the simplest and
best solution!
 
-----Original Message-----
Yes, if you can create a Query joining the lookup table it will be
more efficient. The Div and Code fields should be indexed in both
tables; you can join on the two fields (up to ten fields actually) so
combining them into one field would be neither necessary nor a good
idea!

Just create a Query joining the two tables. Probably the simplest and
best solution!
Click to expand...

John, it works to a point. I'm not getting all the
records from the original table, but (I think) only one of
each unique combination of Employee and Unit. I get 346
records out of 972.

Then when I add the Shift codes, I get multiplying records
(bringing us full circle back to my original post), 1117
total.

All I want to do is substitute the unreadable code with a
human-readable string. I want each record otherwise
unchanged. I'm doing a Select Query, but the MakeTable
seems to work the same way.

Thanks again.

Alan
 
John, it works to a point. I'm not getting all the
records from the original table, but (I think) only one of
each unique combination of Employee and Unit. I get 346
records out of 972.

Then when I add the Shift codes, I get multiplying records
(bringing us full circle back to my original post), 1117
total.
Click to expand...

Please post the SQL of your query.
 
-----Original Message-----
Please post the SQL of your query.
Click to expand...

This gives me 346 records:
SELECT RawReport.Name AS Expr1, Units.[Unit Name]
FROM RawReport INNER JOIN Units ON (RawReport.Div =
Units.Division) AND (RawReport.Unit = Units.[Unit Code])
GROUP BY RawReport.Name, Units.[Unit Name]
ORDER BY RawReport.Name;

This gives me 1117:
SELECT RawReport.Name AS Expr1, Units.[Unit Name], Shifts.
[Shift Name]
FROM (RawReport INNER JOIN Units ON (RawReport.Div =
Units.Division) AND (RawReport.Unit = Units.[Unit Code]))
INNER JOIN Shifts ON (RawReport.Shift = Shifts.[Shift
Code]) AND (RawReport.Div = Shifts.Division)
GROUP BY RawReport.Name, Units.[Unit Name], Shifts.[Shift
Name]
ORDER BY RawReport.Name;

I tried making an outer join but kept getting something
about "ambiguous" outer joins. Didn't understand the help
screen about this, either.

Thanks.

Alan
 
-----Original Message-----
Please post the SQL of your query.
Click to expand...

This gives me 346 records:
SELECT RawReport.Name AS Expr1, Units.[Unit Name]
FROM RawReport INNER JOIN Units ON (RawReport.Div =
Units.Division) AND (RawReport.Unit = Units.[Unit Code])
GROUP BY RawReport.Name, Units.[Unit Name]
ORDER BY RawReport.Name;

This gives me 1117:
SELECT RawReport.Name AS Expr1, Units.[Unit Name], Shifts.
[Shift Name]
FROM (RawReport INNER JOIN Units ON (RawReport.Div =
Units.Division) AND (RawReport.Unit = Units.[Unit Code]))
INNER JOIN Shifts ON (RawReport.Shift = Shifts.[Shift
Code]) AND (RawReport.Div = Shifts.Division)
GROUP BY RawReport.Name, Units.[Unit Name], Shifts.[Shift
Name]
ORDER BY RawReport.Name;

I tried making an outer join but kept getting something
about "ambiguous" outer joins. Didn't understand the help
screen about this, either.
Click to expand...

Sorry... I've been fighting a cold and a deadline and have neglected
this problem. How many records SHOULD you get? I suspect (indeed I'm
pretty confident) that there are only 346 records ghat match between
RawReport and Units; I'm a bit less sure but I think it's likely that
there are multiple Shifts records for each RawReport record and that
grouping by the three fields is correctly returning 1117 records. If
you actually look at the data returned by the query, are you not
seeing multiple duplicate values of Expr1 and of UnitName?
 
Back
Top