Selecting only a certain format of cell

[Guest]

I have various downloads that I then put into Access tables. One of these is
a products database where each product has a code. these products are from 2
different sources and so have contasting formats (format may not be the
correct word to use but my sleep deprived brain can't think of the correct
one!).
What I need to know is, is there a way to set up a table that I can then
link into my queries so I can seperate the 2 product types out without having
to write it into each query which is how I'm doing it now.

e.g. To pick out the first format I look for cells that are like ?###* and
?R###*, and any that don't fit this format are classed as the other one.

The problem is that if I write this into each query (of which there are
quite a few) and the format changes I will have to change every query, so is
there an easy way around this??

 

[Joseph Meehan]

I have various downloads that I then put into Access tables. One of
these is a products database where each product has a code. these
products are from 2 different sources and so have contasting formats
(format may not be the correct word to use but my sleep deprived
brain can't think of the correct one!).
What I need to know is, is there a way to set up a table that I can
then link into my queries so I can seperate the 2 product types out
without having to write it into each query which is how I'm doing it
now.

e.g. To pick out the first format I look for cells that are like
?###* and ?R###*, and any that don't fit this format are classed as
the other one.

The problem is that if I write this into each query (of which there
are quite a few) and the format changes I will have to change every
query, so is there an easy way around this??

Access does not have cells. It has fields. You can change the data
either in a table or in a query calculation result so it might fit the
format you want. Once that is done you can base your other queries on the
result of the modified table data or on the query that holds the result of
calculation.

 

[Guest]

This won't work because I am working from data in tables that is downloaded
from other sources and is too big to go through changing it all into
different formats, which is why I was wondering if I could set up something
that will just reference the ID field and decide which source it is from.

As for the comment about the cells, I know they are not cells, but I
couldn't think of the correct terminology this morning.

 

[Joseph Meehan]

This won't work because I am working from data in tables that is
downloaded from other sources and is too big to go through changing
it all into different formats, which is why I was wondering if I
could set up something that will just reference the ID field and
decide which source it is from.

Using a query you should be able to either:

1. Make an update query that will update the field (make it a text field
if it is not already) of those records that you want to change. Use a
formula to create the desired format based on the existing format. You
could change the existing data or add a new field for the resulting change.

2. Just create a query that includes the table data AND a calculation
result. The calculated result being a reformat of your original data to the
format you want.

As for the comment about the cells, I know they are not cells, but I
couldn't think of the correct terminology this morning.

Good I was just trying to make sure you were using Access not Excel.

 

[Guest]

I don't think I'm getting myself across well. I'm not after changing the data
in the tables, I was trying to find a way around having to change the
hundreds of queries I have which are looking at the tables.

I have 2 sets of queries and within them I use wilcards to look for a
specific format in one of the columns of data (e.g. ?###* to look for a
letter followed by 3 numbers then anything). I have to seperate them out
because the data I get from the downloads are from different sources and so
are in different formats (eg one source is in the style of A758A and the
other is in the style of DEF555).

What I am after ideally is some sort of lookup table within access I can
just link into my queries that has the input mask I am looking for (?###*) so
if the format does change I only have to change that 1 table and not all the
queries that use the mask to query the data.

don't know if this clears up what I'm after any better??

 

[Joseph Meehan]

I don't think I'm getting myself across well. I'm not after changing
the data in the tables, I was trying to find a way around having to
change the hundreds of queries I have which are looking at the tables.

I have 2 sets of queries and within them I use wilcards to look for a
specific format in one of the columns of data (e.g. ?###* to look for
a letter followed by 3 numbers then anything). I have to seperate
them out because the data I get from the downloads are from different
sources and so are in different formats (eg one source is in the
style of A758A and the other is in the style of DEF555).

What I am after ideally is some sort of lookup table within access I
can just link into my queries that has the input mask I am looking
for (?###*) so if the format does change I only have to change that 1
table and not all the queries that use the mask to query the data.

How about building one query, and then basing the others on it?

don't know if this clears up what I'm after any better??