Run 1 query on many tables

[Guest]

I have a bunch of companies whose financials I upload into access from excel.
Each quarter of financials has up to 20000 lines. Currently I uses one table
that I import to, run a query to extract the 1000 or so lines I use and then
export that to an excel spreadsheet. I want to have add the query results to
a table for each company. How can i then write a query to extract the same
info from the different tables based on the company I choose?

This example say company A,B and C. Import Company A, then run a query and
save it in Company A "consolidated table". I do the same for B and C.

How do I now run a query on each of them depending whether i want data from
A, B, or C?

I guess my simple question is, how do I select what table my query is going
to pull info off of?

 

[Jeff Boyce]

I may not be following your description correctly. Are you saying you have
a separate table in Access for EACH company? If so, you're finding the
difficulty of trying to use the features/functions of a relational database
when your table structure is ... a spreadsheet!

Assuming you do have one table per company, but the same information for
each, create a new table. Include all the fields you now have in each
company table. Now add one more field -- the "company" field. To make even
better use of Access' relational strengths, first create a new table that
holds CompanyName, company info, and a CompanyID. Then, that extra field
holds CompanyID, pointing to the Company table to find other company-related
info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

Thanks for getting back to me. I actually had intended to do it the way you
suggested below but was not sure if 1 table could hold all the data. I have
about 50 companies that once the info is extracted, will have about 2000
lines of data and the statements are filed 4 times a year. That is 400,000
lines of data and it is my understanding that a table can only hold 65,000,
like excel. I thought the best way to have all the data in the system was to
use a different table for each company.
Let me describe the current process and I would greatly appreciate you
suggestions on how improve it.

1. I get a text file, and paste that into an excel spreadsheet starting with
B1. This has between 20k and 30k lines.
Since this data is very rough I need to create unique identifiers for the
lines of data I extract
2. I perform the following calculations create the identifier.
a. I need to take out the following characters from column C via a find all
and replace all
Find All ], replace all with nothing
Find all ., replace with nothing.
B. In A2, I run following fuction (via a macro all the way down to line
60,000)
=IF(A2="D",A1,B2)
D in the above expression is a specific character
C. Once that is done, I cut and paste the “values only†to sheetB and
delete the values in sheet A to free up memory
D. I save the file.
3. In access use the macro to import the data from the sheetB to a tableQ.
(I tried to link it but since it is about 35 columns and each area has
different values it wasn’t importing correctly)
A. I run query1 to pull all the data from tableQ and perform some calc to
get a final indentifer.
B. I also have another TABLE which is the Key for assigning all the
different classes of investments to each line based on the identifier.
C. I run a query2 based on query1, where I pull the important data from Q1,
and also match up each investment to its class by using the identifiers which
are related with the asset identifiers in Table KEY
D. I then export the query to excel where I store the data per company and
run my analysis. It has about 2000 lines
4. Once back in excel and via pivot tables I extract the data I need.

The reason for all of this is that the original text file does not have the
same values lined up in each column. For example, asset class “1†will have
the book value in E:E and the asset class “2†has book value in G:G.

I know this is a lot and if you do not feel like responding don’t worry
about it. I have just spent many hours and many dead ends to come up with a
system that is functional.
If nothing else, I would like to be able to import the text file directly
into Access and perform the calc in 2.B., which is crucial to create the
final identifiers. This would make the process much quicker. I read somewhere
that these types of excel calcs can be done in datasheet view some how. Right
now I only know how to do calc with query’s, and I have yet to be able to do
so since each one relies on the data in the cell above.

An example of the data manipulating I achieve in step 2 is below. B and C
are what I paste in. D is my unique identifer.

IDENTIFER = (CONCENTRATE A2,C2)
ROW COLUMN A COLUMN B COLUMN C IDENTIFIER
1 [P2004JURAT1 [P2004JURAT1 1 [P2004JURAT11
2 [P2004JURAT1 D 2
3 [P2004JURAT2 [P2004JURAT2 1
4 [P2004JURAT2 D 2
5 [P2004JURAT3 [P2004JURAT3 1
6 [P2004JURAT3 D 2
7 [P2004JURAT3 D 3
8 [P2004JURAT3 D 4
9 [P2004JURAT3 D 5
10 [P2004JURAT3 D 6
11 [P2004JURAT3 D 7

 

[Jeff Boyce]

I don't know from whom you heard that, but Access does NOT have a row limit.
Check Access HELP for "specifications" to see what limits there are. Doing
it the way you described in your original post will make both you and Access
work MUCH harder than you need to.

Strongly consider returning to your earlier plan.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for getting back to me. I actually had intended to do it the way
you
suggested below but was not sure if 1 table could hold all the data. I
have
about 50 companies that once the info is extracted, will have about 2000
lines of data and the statements are filed 4 times a year. That is 400,000
lines of data and it is my understanding that a table can only hold
65,000,
like excel. I thought the best way to have all the data in the system was
to
use a different table for each company.
Let me describe the current process and I would greatly appreciate you
suggestions on how improve it.

1. I get a text file, and paste that into an excel spreadsheet starting
with
B1. This has between 20k and 30k lines.
Since this data is very rough I need to create unique identifiers for the
lines of data I extract
2. I perform the following calculations create the identifier.
a. I need to take out the following characters from column C via a find
all
and replace all
Find All ], replace all with nothing
Find all ., replace with nothing.
B. In A2, I run following fuction (via a macro all the way down to line
60,000)
=IF(A2="D",A1,B2)
D in the above expression is a specific character
C. Once that is done, I cut and paste the "values only" to sheetB and
delete the values in sheet A to free up memory
D. I save the file.
3. In access use the macro to import the data from the sheetB to a tableQ.
(I tried to link it but since it is about 35 columns and each area has
different values it wasn't importing correctly)
A. I run query1 to pull all the data from tableQ and perform some calc to
get a final indentifer.
B. I also have another TABLE which is the Key for assigning all the
different classes of investments to each line based on the identifier.
C. I run a query2 based on query1, where I pull the important data from
Q1,
and also match up each investment to its class by using the identifiers
which
are related with the asset identifiers in Table KEY
D. I then export the query to excel where I store the data per company and
run my analysis. It has about 2000 lines
4. Once back in excel and via pivot tables I extract the data I need.

The reason for all of this is that the original text file does not have
the
same values lined up in each column. For example, asset class "1" will
have
the book value in E:E and the asset class "2" has book value in G:G.

I know this is a lot and if you do not feel like responding don't worry
about it. I have just spent many hours and many dead ends to come up with
a
system that is functional.
If nothing else, I would like to be able to import the text file directly
into Access and perform the calc in 2.B., which is crucial to create the
final identifiers. This would make the process much quicker. I read
somewhere
that these types of excel calcs can be done in datasheet view some how.
Right
now I only know how to do calc with query's, and I have yet to be able to
do
so since each one relies on the data in the cell above.

An example of the data manipulating I achieve in step 2 is below. B and C
are what I paste in. D is my unique identifer.

IDENTIFER = (CONCENTRATE A2,C2)
ROW COLUMN A COLUMN B COLUMN C IDENTIFIER
1 [P2004JURAT1 [P2004JURAT1 1 [P2004JURAT11
2 [P2004JURAT1 D 2
3 [P2004JURAT2 [P2004JURAT2 1
4 [P2004JURAT2 D 2
5 [P2004JURAT3 [P2004JURAT3 1
6 [P2004JURAT3 D 2
7 [P2004JURAT3 D 3
8 [P2004JURAT3 D 4
9 [P2004JURAT3 D 5
10 [P2004JURAT3 D 6
11 [P2004JURAT3 D 7

I may not be following your description correctly. Are you saying you
have
a separate table in Access for EACH company? If so, you're finding the
difficulty of trying to use the features/functions of a relational
database
when your table structure is ... a spreadsheet!

Assuming you do have one table per company, but the same information for
each, create a new table. Include all the fields you now have in each
company table. Now add one more field -- the "company" field. To make
even
better use of Access' relational strengths, first create a new table that
holds CompanyName, company info, and a CompanyID. Then, that extra field
holds CompanyID, pointing to the Company table to find other
company-related
info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I don't know from whom you heard that, but Access does NOT have a row limit.
Check Access HELP for "specifications" to see what limits there are. Doing
it the way you described in your original post will make both you and Access
work MUCH harder than you need to.

Strongly consider returning to your earlier plan.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for getting back to me. I actually had intended to do it the way
you
suggested below but was not sure if 1 table could hold all the data. I
have
about 50 companies that once the info is extracted, will have about 2000
lines of data and the statements are filed 4 times a year. That is 400,000
lines of data and it is my understanding that a table can only hold
65,000,
like excel. I thought the best way to have all the data in the system was
to
use a different table for each company.
Let me describe the current process and I would greatly appreciate you
suggestions on how improve it.

1. I get a text file, and paste that into an excel spreadsheet starting
with
B1. This has between 20k and 30k lines.
Since this data is very rough I need to create unique identifiers for the
lines of data I extract
2. I perform the following calculations create the identifier.
a. I need to take out the following characters from column C via a find
all
and replace all
Find All ], replace all with nothing
Find all ., replace with nothing.
B. In A2, I run following fuction (via a macro all the way down to line
60,000)
=IF(A2="D",A1,B2)
D in the above expression is a specific character
C. Once that is done, I cut and paste the "values only" to sheetB and
delete the values in sheet A to free up memory
D. I save the file.
3. In access use the macro to import the data from the sheetB to a tableQ.
(I tried to link it but since it is about 35 columns and each area has
different values it wasn't importing correctly)
A. I run query1 to pull all the data from tableQ and perform some calc to
get a final indentifer.
B. I also have another TABLE which is the Key for assigning all the
different classes of investments to each line based on the identifier.
C. I run a query2 based on query1, where I pull the important data from
Q1,
and also match up each investment to its class by using the identifiers
which
are related with the asset identifiers in Table KEY
D. I then export the query to excel where I store the data per company and
run my analysis. It has about 2000 lines
4. Once back in excel and via pivot tables I extract the data I need.

The reason for all of this is that the original text file does not have
the
same values lined up in each column. For example, asset class "1" will
have
the book value in E:E and the asset class "2" has book value in G:G.

I know this is a lot and if you do not feel like responding don't worry
about it. I have just spent many hours and many dead ends to come up with
a
system that is functional.
If nothing else, I would like to be able to import the text file directly
into Access and perform the calc in 2.B., which is crucial to create the
final identifiers. This would make the process much quicker. I read
somewhere
that these types of excel calcs can be done in datasheet view some how.
Right
now I only know how to do calc with query's, and I have yet to be able to
do
so since each one relies on the data in the cell above.

An example of the data manipulating I achieve in step 2 is below. B and C
are what I paste in. D is my unique identifer.

IDENTIFER = (CONCENTRATE A2,C2)
ROW COLUMN A COLUMN B COLUMN C IDENTIFIER
1 [P2004JURAT1 [P2004JURAT1 1 [P2004JURAT11
2 [P2004JURAT1 D 2
3 [P2004JURAT2 [P2004JURAT2 1
4 [P2004JURAT2 D 2
5 [P2004JURAT3 [P2004JURAT3 1
6 [P2004JURAT3 D 2
7 [P2004JURAT3 D 3
8 [P2004JURAT3 D 4
9 [P2004JURAT3 D 5
10 [P2004JURAT3 D 6
11 [P2004JURAT3 D 7

I may not be following your description correctly. Are you saying you
have
a separate table in Access for EACH company? If so, you're finding the
difficulty of trying to use the features/functions of a relational
database
when your table structure is ... a spreadsheet!

Assuming you do have one table per company, but the same information for
each, create a new table. Include all the fields you now have in each
company table. Now add one more field -- the "company" field. To make
even
better use of Access' relational strengths, first create a new table that
holds CompanyName, company info, and a CompanyID. Then, that extra field
holds CompanyID, pointing to the Company table to find other
company-related
info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a bunch of companies whose financials I upload into access from
excel.
Each quarter of financials has up to 20000 lines. Currently I uses one
table
that I import to, run a query to extract the 1000 or so lines I use and
then
export that to an excel spreadsheet. I want to have add the query
results
to
a table for each company. How can i then write a query to extract the
same
info from the different tables based on the company I choose?

This example say company A,B and C. Import Company A, then run a query
and
save it in Company A "consolidated table". I do the same for B and C.

How do I now run a query on each of them depending whether i want data
from
A, B, or C?

I guess my simple question is, how do I select what table my query is
going
to pull info off of?

 

[Guest]

Jeff,

Wow, that is perfect. I don’t know why I thought that. I guess I let my
Excel knowledge spill over to my new Access skills.

So when import, I just keep adding it to the same table. That will be much
easier

Is there any chance you can help me with the second part regarding the
formula I only know how to run in excel? It is in step 2 below and it is the
first calc that needs to be done in order to create the first part of the
identifier. Just to clarify, the text file I currently receive only has a
section identifier which is in the same column as the "D"s. I need to use the
formula to say if the cell to the right is not a "D", then it equals that
cell, if it is a "D", use the result of the cell above. Is this possible in
access?

My perfect scenario would to import the test file directly into Access,
perform the necessary calcs and then export the consolidated info to Excel.

One more ?. Is there a way I can choose what format each column is in when
exporting. The first 10 of the 30+ columns is a combo of text and numbers, so
leaving it in text format is fine. But the 20-30 is all numbers and currently
it exports all of it in text. Once I get the number in excel, I have the
error smart tag saying it is a number formatted as a text. I then select the
30 or so columns, and have it convert to numbers, but with so many lines, it
really takes a while. I feel like there has to be a way to avoid this step.
Also, I pull data from Access to Excel using SQL or something; will I be able
to select the format?

Once again I really appreciate your help. I've tried to get these answers
from my IT department for a while now and they have yet provide any helpful
response.

I don't know from whom you heard that, but Access does NOT have a row limit.
Check Access HELP for "specifications" to see what limits there are. Doing
it the way you described in your original post will make both you and Access
work MUCH harder than you need to.

Strongly consider returning to your earlier plan.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for getting back to me. I actually had intended to do it the way
you
suggested below but was not sure if 1 table could hold all the data. I
have
about 50 companies that once the info is extracted, will have about 2000
lines of data and the statements are filed 4 times a year. That is 400,000
lines of data and it is my understanding that a table can only hold
65,000,
like excel. I thought the best way to have all the data in the system was
to
use a different table for each company.
Let me describe the current process and I would greatly appreciate you
suggestions on how improve it.

1. I get a text file, and paste that into an excel spreadsheet starting
with
B1. This has between 20k and 30k lines.
Since this data is very rough I need to create unique identifiers for the
lines of data I extract
2. I perform the following calculations create the identifier.
a. I need to take out the following characters from column C via a find
all
and replace all
Find All ], replace all with nothing
Find all ., replace with nothing.
B. In A2, I run following fuction (via a macro all the way down to line
60,000)
=IF(A2="D",A1,B2)
D in the above expression is a specific character
C. Once that is done, I cut and paste the "values only" to sheetB and
delete the values in sheet A to free up memory
D. I save the file.
3. In access use the macro to import the data from the sheetB to a tableQ.
(I tried to link it but since it is about 35 columns and each area has
different values it wasn't importing correctly)
A. I run query1 to pull all the data from tableQ and perform some calc to
get a final indentifer.
B. I also have another TABLE which is the Key for assigning all the
different classes of investments to each line based on the identifier.
C. I run a query2 based on query1, where I pull the important data from
Q1,
and also match up each investment to its class by using the identifiers
which
are related with the asset identifiers in Table KEY
D. I then export the query to excel where I store the data per company and
run my analysis. It has about 2000 lines
4. Once back in excel and via pivot tables I extract the data I need.

The reason for all of this is that the original text file does not have
the
same values lined up in each column. For example, asset class "1" will
have
the book value in E:E and the asset class "2" has book value in G:G.

I know this is a lot and if you do not feel like responding don't worry
about it. I have just spent many hours and many dead ends to come up with
a
system that is functional.
If nothing else, I would like to be able to import the text file directly
into Access and perform the calc in 2.B., which is crucial to create the
final identifiers. This would make the process much quicker. I read
somewhere
that these types of excel calcs can be done in datasheet view some how.
Right
now I only know how to do calc with query's, and I have yet to be able to
do
so since each one relies on the data in the cell above.

An example of the data manipulating I achieve in step 2 is below. B and C
are what I paste in. D is my unique identifer.

IDENTIFER = (CONCENTRATE A2,C2)
ROW COLUMN A COLUMN B COLUMN C IDENTIFIER
1 [P2004JURAT1 [P2004JURAT1 1 [P2004JURAT11
2 [P2004JURAT1 D 2
3 [P2004JURAT2 [P2004JURAT2 1
4 [P2004JURAT2 D 2
5 [P2004JURAT3 [P2004JURAT3 1
6 [P2004JURAT3 D 2
7 [P2004JURAT3 D 3
8 [P2004JURAT3 D 4
9 [P2004JURAT3 D 5
10 [P2004JURAT3 D 6
11 [P2004JURAT3 D 7

I may not be following your description correctly. Are you saying you
have
a separate table in Access for EACH company? If so, you're finding the
difficulty of trying to use the features/functions of a relational
database
when your table structure is ... a spreadsheet!

Assuming you do have one table per company, but the same information for
each, create a new table. Include all the fields you now have in each
company table. Now add one more field -- the "company" field. To make
even
better use of Access' relational strengths, first create a new table that
holds CompanyName, company info, and a CompanyID. Then, that extra field
holds CompanyID, pointing to the Company table to find other
company-related
info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a bunch of companies whose financials I upload into access from
excel.
Each quarter of financials has up to 20000 lines. Currently I uses one
table
that I import to, run a query to extract the 1000 or so lines I use and
then
export that to an excel spreadsheet. I want to have add the query
results
to
a table for each company. How can i then write a query to extract the
same
info from the different tables based on the company I choose?

This example say company A,B and C. Import Company A, then run a query
and
save it in Company A "consolidated table". I do the same for B and C.

How do I now run a query on each of them depending whether i want data
from
A, B, or C?

I guess my simple question is, how do I select what table my query is
going
to pull info off of?

 

[Jeff Boyce]

I'll recommend that you post your follow-on questions to the appropriate
newsgroups as new threads. That way, you get many more folks seeing your
questions and (potentially) responding.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Wow, that is perfect. I don't know why I thought that. I guess I let my
Excel knowledge spill over to my new Access skills.

So when import, I just keep adding it to the same table. That will be much
easier

Is there any chance you can help me with the second part regarding the
formula I only know how to run in excel? It is in step 2 below and it is
the
first calc that needs to be done in order to create the first part of the
identifier. Just to clarify, the text file I currently receive only has a
section identifier which is in the same column as the "D"s. I need to use
the
formula to say if the cell to the right is not a "D", then it equals that
cell, if it is a "D", use the result of the cell above. Is this possible
in
access?

My perfect scenario would to import the test file directly into Access,
perform the necessary calcs and then export the consolidated info to
Excel.

One more ?. Is there a way I can choose what format each column is in when
exporting. The first 10 of the 30+ columns is a combo of text and numbers,
so
leaving it in text format is fine. But the 20-30 is all numbers and
currently
it exports all of it in text. Once I get the number in excel, I have the
error smart tag saying it is a number formatted as a text. I then select
the
30 or so columns, and have it convert to numbers, but with so many lines,
it
really takes a while. I feel like there has to be a way to avoid this
step.
Also, I pull data from Access to Excel using SQL or something; will I be
able
to select the format?

Once again I really appreciate your help. I've tried to get these answers
from my IT department for a while now and they have yet provide any
helpful
response.

I don't know from whom you heard that, but Access does NOT have a row
limit.
Check Access HELP for "specifications" to see what limits there are.
Doing
it the way you described in your original post will make both you and
Access
work MUCH harder than you need to.

Strongly consider returning to your earlier plan.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for getting back to me. I actually had intended to do it the way
you
suggested below but was not sure if 1 table could hold all the data. I
have
about 50 companies that once the info is extracted, will have about
2000
lines of data and the statements are filed 4 times a year. That is
400,000
lines of data and it is my understanding that a table can only hold
65,000,
like excel. I thought the best way to have all the data in the system
was
to
use a different table for each company.
Let me describe the current process and I would greatly appreciate you
suggestions on how improve it.

1. I get a text file, and paste that into an excel spreadsheet starting
with
B1. This has between 20k and 30k lines.
Since this data is very rough I need to create unique identifiers for
the
lines of data I extract
2. I perform the following calculations create the identifier.
a. I need to take out the following characters from column C via a find
all
and replace all
Find All ], replace all with nothing
Find all ., replace with nothing.
B. In A2, I run following fuction (via a macro all the way down to line
60,000)
=IF(A2="D",A1,B2)
D in the above expression is a specific character
C. Once that is done, I cut and paste the "values only" to sheetB and
delete the values in sheet A to free up memory
D. I save the file.
3. In access use the macro to import the data from the sheetB to a
tableQ.
(I tried to link it but since it is about 35 columns and each area has
different values it wasn't importing correctly)
A. I run query1 to pull all the data from tableQ and perform some calc
to
get a final indentifer.
B. I also have another TABLE which is the Key for assigning all the
different classes of investments to each line based on the identifier.
C. I run a query2 based on query1, where I pull the important data from
Q1,
and also match up each investment to its class by using the identifiers
which
are related with the asset identifiers in Table KEY
D. I then export the query to excel where I store the data per company
and
run my analysis. It has about 2000 lines
4. Once back in excel and via pivot tables I extract the data I need.

The reason for all of this is that the original text file does not have
the
same values lined up in each column. For example, asset class "1" will
have
the book value in E:E and the asset class "2" has book value in G:G.

I know this is a lot and if you do not feel like responding don't worry
about it. I have just spent many hours and many dead ends to come up
with
a
system that is functional.
If nothing else, I would like to be able to import the text file
directly
into Access and perform the calc in 2.B., which is crucial to create
the
final identifiers. This would make the process much quicker. I read
somewhere
that these types of excel calcs can be done in datasheet view some how.
Right
now I only know how to do calc with query's, and I have yet to be able
to
do
so since each one relies on the data in the cell above.

An example of the data manipulating I achieve in step 2 is below. B and
C
are what I paste in. D is my unique identifer.

IDENTIFER = (CONCENTRATE A2,C2)
ROW COLUMN A COLUMN B COLUMN C
IDENTIFIER
1 [P2004JURAT1 [P2004JURAT1 1 [P2004JURAT11
2 [P2004JURAT1 D 2
3 [P2004JURAT2 [P2004JURAT2 1
4 [P2004JURAT2 D 2
5 [P2004JURAT3 [P2004JURAT3 1
6 [P2004JURAT3 D 2
7 [P2004JURAT3 D 3
8 [P2004JURAT3 D 4
9 [P2004JURAT3 D 5
10 [P2004JURAT3 D 6
11 [P2004JURAT3 D 7

:

I may not be following your description correctly. Are you saying you
have
a separate table in Access for EACH company? If so, you're finding
the
difficulty of trying to use the features/functions of a relational
database
when your table structure is ... a spreadsheet!

Assuming you do have one table per company, but the same information
for
each, create a new table. Include all the fields you now have in each
company table. Now add one more field -- the "company" field. To
make
even
better use of Access' relational strengths, first create a new table
that
holds CompanyName, company info, and a CompanyID. Then, that extra
field
holds CompanyID, pointing to the Company table to find other
company-related
info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a bunch of companies whose financials I upload into access
from
excel.
Each quarter of financials has up to 20000 lines. Currently I uses
one
table
that I import to, run a query to extract the 1000 or so lines I use
and
then
export that to an excel spreadsheet. I want to have add the query
results
to
a table for each company. How can i then write a query to extract
the
same
info from the different tables based on the company I choose?

This example say company A,B and C. Import Company A, then run a
query
and
save it in Company A "consolidated table". I do the same for B and
C.

How do I now run a query on each of them depending whether i want
data
from
A, B, or C?

I guess my simple question is, how do I select what table my query
is
going
to pull info off of?

 

[Guest]

Just to clarify, the text file I currently receive only has a

section identifier which is in the same column as the "D"s. I need to use the
formula to say if the cell to the right is not a "D", then it equals that
cell, if it is a "D", use the result of the cell above. Is this possible in
access?

There are no cells in Access. Each record in a table is separate from every
other record. If you are repeating the same text, that is a good indicator
that you need to pull that information out into a separate table, and use a
foreign key to refer to it. It sounds, from this post and others, that you
need to develop a normalized database before you get much further into your
project.