Export to Excell won't match information

[Michael Cross via AccessMonster.com]

I have two access data bases that I export into and excell spreadsheet and
then use a piviot table report to get the info I need. The problem is one
of the databases comes from a linked table that seems to add "hidden info"
to of of the fields so when I use the piviot table it won't match (what
looks like) identical information. How do I get them to match?

Michael Cross

 

[John Nurick]

I have two access data bases that I export into and excell spreadsheet and
then use a piviot table report to get the info I need. The problem is one
of the databases comes from a linked table that seems to add "hidden info"
to of of the fields so when I use the piviot table it won't match (what
looks like) identical information. How do I get them to match?

Hi Michael,

There are so many typos in your message that it's hard to understand and
doesn't inspire confidence in your ability to distinguish identical data
and near-identical data.

Forget about Excel for the moment. Work within Access, using the Find
Unmatched query wizard and different kinds of join within queries to
work out why apparently the values you think should match in fact don't.
One possibility is that some are stored as text and others as numbers.

 

[Michael Cross via AccessMonster.com]

Hello John,

Sorry about the typos. I was trying to do ten things at once and didn't
read what I had written. Let me explain better. I have an exsisting linked
table that gets its data from another system. It makes reports of my sales
for the day. I made another database that I copied off of the original to
allow me to enter sales not generated by the system. I have checked all the
formats and they are identical. The field I have for Rep ID is filled in
automaticly by the original program. The same field is filled in manualy on
my database. When I try to get the same field to match from my database it
shows them as to seperate entries. My excel report will print A214 and then
another entry as A214. I'm sure the source program has added a string or
something like that to the Rep Id field but I can't see it. What I really
want to do is make my database replicate the original database so the
entries will match up. I new to Access so please bear with me.

Thank you so much for your time.

Michael Cross

 

[John Nurick]

Michael,

You still haven't made it clear whether this problem is in Access or
Excel. Are you finding
(1) that apparently identical [Rep ID] values in the two tables are
being seen as different by Access,
(2) that Access sees the values as identical but Excel sees them as
different?

If (1), I don't think it's possible to have "invisible strings" in an
Access text field. Conceivably the source program included trailing
spaces or other invisible characters (tabs, linefeeds) in the data. You
can check that by running this query, replacing XXX with your actual
tablename.

SELECT [Rep ID], Len([Rep ID]) AS FieldLength FROM XXX;

(create a new query, switch it to SQL view, and paste in the above
string). Then run the query and observe whether the displayed length
matches the visible number of characters.

If you haven't already done so, create a query in Access that joins the
two tables on [Rep ID]. (Create a new query. Add both tables to it. Drag
the [Rep ID] field from one table to the other. Then drag the two RepID
fields and some others from the tables into the query design grid.) WHen
you run the query, do the records from the two tables line up right(i.e.
does the query correctly display values from corresponding records in
the two tables)? Are there any records missing?

If the records line up properly and there are no records missing, the
[Rep ID] values are OK and the problem is almost certainly in Excel, or
in whatever process you are using to export the data to Excel.

If the records don't line up, or some are missing, there's a problem
with the [Rep ID] values. Use the Find Unmatched query wizard to find
the duds.