In Data Access Page only 32 records show data in all fields.

[Guest]

Access 2002 sp3
Two data bases, MSDS and CAS. Ten fields in MSDS contain numbers equal to
numbers found in primary key in CAS. Numbers are entered as text. A query has
MSDS linked to ten copies of CAS. Each number field is tied independently to
the key in CAS. This is so I can have a DAP that shows all of the numbers
that a chemical solution contains and beside the number is the chemical name
the number represents.

The problem now is that when I use the DAP either through Access or IE only
the first 32 records display the data properly. I am using a pull down list
for the solution name (It's the primary key) and the first 32 records are
displaying perfectly. After that only the solution name appears. The numbers,
names, and other data about the solutions is missing. I deleted a record and
the number was still 32 records.

 

[Sylvain Lafontaine]

Maybe you have hit some sort of limitation on the number of external links.
However, having splitted your schema into two databases and using ten copies
of the second database, CAS, is a strange indication here. You should give
us more information about the queries that you are using.

S. L.

 

[Guest]

MSDS contains the inventory of cleaners, solvents, etc. around the building.
Each record has a list of the chemicals used in the solutions. Some may
contain as many as 10 different chemicals. The CAS data base has over 3100
records of chemicals. (The American Chemical Society has set these numbers.
They have over 4 million numbers and add to it every day.) In my data base
the chemicals are listed by a unique number, then the chemical name. Some of
the names are very long, up to 95 characters so far. Instead of typing in the
chemical name I just use the number given on the MSDS sheet. I tried lots of
different queries to get the names to show on the Data Access Page but this
was the only thing I found that worked. The MSDS database has a unique name
for each cleaner, etc. and that is the primary key. The CAS numbers are
unique, so it is the primary key. The query is set up with all of the MSDS
fields showing. Under the link section I have the MSDS on the left side.
There are ten copies of CAS, each one tied to a different number field in
MSDS. MSDS!cas1 tied to CAS!number, MSDS!cas2 tied to
CAS!number....MSDS!cas10 tied to CAS!number. I know this seems like a lot of
work to do a query, but nothing else gave me the results I wanted. I am using
a DAP because every employee needs to get to the data, but they don't all
have Microsoft Access on their computers.

 

[Sylvain Lafontaine]

Hi,

For the ten CAS fields in the MSDS sheet, the usual solution is to
create a third table relating all MSDS cleaners/solvents to their chemicals.
This give you the possibility of having a varying number of chemicals going
from 0 to near inifinity but will give you some trouble is you want the
display to be columns based instead of rows based (Transforming a row based
display to a column based one is what's called making a Crosstab query
(sometimes also called a Transform query under Access)).

However, your solution of having 10 fields, one for each CAS chemical, is
also perfectly valid and shouldn't cause this problem of having only the
first 32 records to be correctly displayed. Possibly, the error may come
from the code used to create the display.

Is it possible that you give us the query or piece of code that you are
using to create the recordset of your DAP page?

S. L.

 

[Guest]

Sylvain,
This first part is the main query I am working with. The second part is the
query for the CAS numbers. The third part is the VB code from the DAP. Seems
like a big file to include here, but there is nothing proprietary about it.
It's too long to post. I'll try sending it separate.

---part 1---
SELECT DISTINCT [MSDS/CAS].CHEMICALNAME, [MSDS/CAS].DESCRIPTION,
[MSDS/CAS].MSDS, [MSDS/CAS].MANUFACTURER, [MSDS/CAS].PRODUCTNAMENUMBER,
[MSDS/CAS].CURRENTUSE, [MSDS/CAS].[DISPOSAL DATE], [MSDS/CAS].PURCASEDATE,
[MSDS/CAS].LOCATION, [MSDS/CAS].TYPE, [MSDS/CAS].STATE, [MSDS/CAS].HEALTH,
[MSDS/CAS].FIRE, [MSDS/CAS].REACTIVITY, [MSDS/CAS].SPECIALPRECAUTIONS,
[MSDS/CAS].COMMENTS, [MSDS/CAS].PDF, [MSDS/CAS].CASNUMBER1,
[MSDS/CAS].CASNUMBER2, [MSDS/CAS].CASNUMBER3, [MSDS/CAS].CASNUMBER4,
[MSDS/CAS].CASNUMBER5, [MSDS/CAS].CASNUMBER6, [MSDS/CAS].CASNUMBER7,
[MSDS/CAS].CASNUMBER8, [MSDS/CAS].CASNUMBER9, [MSDS/CAS].CASNUMBER10, [CAS
Query1].CHEMCIAL, [CAS Query2].CHEMCIAL, [CAS Query3].CHEMCIAL, [CAS
Query4].CHEMCIAL, [CAS Query5].CHEMCIAL, [CAS Query6].CHEMCIAL, [CAS
Query7].CASNUMBER7, [CAS Query7].CHEMCIAL, [CAS Query8].CHEMCIAL, [CAS
Query10].CHEMCIAL
FROM [CAS Query10] RIGHT JOIN ([CAS Query9] RIGHT JOIN ([CAS Query8] RIGHT
JOIN ([CAS Query7] RIGHT JOIN ([CAS Query6] RIGHT JOIN ([CAS Query5] RIGHT
JOIN ([CAS Query4] RIGHT JOIN ([CAS Query3] RIGHT JOIN ([CAS Query2] RIGHT
JOIN ([CAS Query1] RIGHT JOIN [MSDS/CAS] ON [CAS Query1].CAS =
[MSDS/CAS].CASNUMBER1) ON [CAS Query2].CAS = [MSDS/CAS].CASNUMBER2) ON [CAS
Query3].CAS = [MSDS/CAS].CASNUMBER3) ON [CAS Query4].CAS =
[MSDS/CAS].CASNUMBER4) ON [CAS Query5].CAS = [MSDS/CAS].CASNUMBER5) ON [CAS
Query6].CAS = [MSDS/CAS].CASNUMBER6) ON [CAS Query7].CAS =
[MSDS/CAS].CASNUMBER7) ON [CAS Query8].CAS = [MSDS/CAS].CASNUMBER8) ON [CAS
Query9].CAS = [MSDS/CAS].CASNUMBER9) ON [CAS Query10].CAS =
[MSDS/CAS].CASNUMBER10;

---part 2---
SELECT DISTINCTROW CAS.CAS, CAS.CHEMCIAL, MSDS.CASNUMBER9
FROM CAS INNER JOIN MSDS ON CAS.CAS = MSDS.CASNUMBER9;

 

[Guest]

Sylvain,

Still too long. What parts of the code would be helpful? Is there another
way to send this?

 

[Sylvain Lafontaine]

Excerpt for the unusual high number of Rigth Join, your piece of code looks
perfectly valid to me.

Is there is any particular reason that you must have 10 CAS fields in the
MSDS table (and linking them with Right Join) instead of a more usual third
table with ordinary Inner Join?

S. L.

 

[Guest]

No. I have used several database programs, but I am fairley new to Access. I
had trouble setting up the third table so I went this route instead. I would
like to try the third table.

Excerpt for the unusual high number of Rigth Join, your piece of code looks
perfectly valid to me.

Is there is any particular reason that you must have 10 CAS fields in the
MSDS table (and linking them with Right Join) instead of a more usual third
table with ordinary Inner Join?

S. L.

Sylvain,
This first part is the main query I am working with. The second part is
the
query for the CAS numbers. The third part is the VB code from the DAP.
Seems
like a big file to include here, but there is nothing proprietary about
it.
It's too long to post. I'll try sending it separate.

---part 1---
SELECT DISTINCT [MSDS/CAS].CHEMICALNAME, [MSDS/CAS].DESCRIPTION,
[MSDS/CAS].MSDS, [MSDS/CAS].MANUFACTURER, [MSDS/CAS].PRODUCTNAMENUMBER,
[MSDS/CAS].CURRENTUSE, [MSDS/CAS].[DISPOSAL DATE], [MSDS/CAS].PURCASEDATE,
[MSDS/CAS].LOCATION, [MSDS/CAS].TYPE, [MSDS/CAS].STATE, [MSDS/CAS].HEALTH,
[MSDS/CAS].FIRE, [MSDS/CAS].REACTIVITY, [MSDS/CAS].SPECIALPRECAUTIONS,
[MSDS/CAS].COMMENTS, [MSDS/CAS].PDF, [MSDS/CAS].CASNUMBER1,
[MSDS/CAS].CASNUMBER2, [MSDS/CAS].CASNUMBER3, [MSDS/CAS].CASNUMBER4,
[MSDS/CAS].CASNUMBER5, [MSDS/CAS].CASNUMBER6, [MSDS/CAS].CASNUMBER7,
[MSDS/CAS].CASNUMBER8, [MSDS/CAS].CASNUMBER9, [MSDS/CAS].CASNUMBER10, [CAS
Query1].CHEMCIAL, [CAS Query2].CHEMCIAL, [CAS Query3].CHEMCIAL, [CAS
Query4].CHEMCIAL, [CAS Query5].CHEMCIAL, [CAS Query6].CHEMCIAL, [CAS
Query7].CASNUMBER7, [CAS Query7].CHEMCIAL, [CAS Query8].CHEMCIAL, [CAS
Query10].CHEMCIAL
FROM [CAS Query10] RIGHT JOIN ([CAS Query9] RIGHT JOIN ([CAS Query8] RIGHT
JOIN ([CAS Query7] RIGHT JOIN ([CAS Query6] RIGHT JOIN ([CAS Query5] RIGHT
JOIN ([CAS Query4] RIGHT JOIN ([CAS Query3] RIGHT JOIN ([CAS Query2] RIGHT
JOIN ([CAS Query1] RIGHT JOIN [MSDS/CAS] ON [CAS Query1].CAS =
[MSDS/CAS].CASNUMBER1) ON [CAS Query2].CAS = [MSDS/CAS].CASNUMBER2) ON
[CAS
Query3].CAS = [MSDS/CAS].CASNUMBER3) ON [CAS Query4].CAS =
[MSDS/CAS].CASNUMBER4) ON [CAS Query5].CAS = [MSDS/CAS].CASNUMBER5) ON
[CAS
Query6].CAS = [MSDS/CAS].CASNUMBER6) ON [CAS Query7].CAS =
[MSDS/CAS].CASNUMBER7) ON [CAS Query8].CAS = [MSDS/CAS].CASNUMBER8) ON
[CAS
Query9].CAS = [MSDS/CAS].CASNUMBER9) ON [CAS Query10].CAS =
[MSDS/CAS].CASNUMBER10;

---part 2---
SELECT DISTINCTROW CAS.CAS, CAS.CHEMCIAL, MSDS.CASNUMBER9
FROM CAS INNER JOIN MSDS ON CAS.CAS = MSDS.CASNUMBER9;

 

[Sylvain Lafontaine]

Then my first suggestion would be that you use an Autoincrement ID as the
primary key for your MSDS table. This is not mandatory but using names as
primary key is asking for trouble because you cannot change the name later.
Also, using MSDS and CAS both as the name of the table and the name of one
of the field can lead to confusion, too.

Create a table MsdsCas with the following three fields:

IdMsdsCas --> the primary key for this new table
Msds --> the foreigh key for the table MSDS
Cas --> the foreigh key for the table CAS (by the way, would not be a
bad idea to rename it IdCas)

You can also use Msds and Cas to roll out a primary key with two fields
(Msds and Cas) but again, this may could more confusion than help.

Finally, you select clause will be something like:

Select M.Msds, M.Manufacturer, ...., C.Cas, C.ChemicalName,
C.Description, ...., MC.IdMsdsCas
From Cas as C inner join (MSDS as M inner join MsdsCas MC on M.Msds =
MC.Msds) on C.Cas = MC.Cas

This is much simpler.

Also, after a second reading of your previous message, I see that you didn't
provide a definition for the table (or query?) that you call « [MSDS/CAS] ».
If necessary, modify the above query to take care of this table.

S. L.

No. I have used several database programs, but I am fairley new to Access.
I
had trouble setting up the third table so I went this route instead. I
would
like to try the third table.

Excerpt for the unusual high number of Rigth Join, your piece of code
looks
perfectly valid to me.

Is there is any particular reason that you must have 10 CAS fields in the
MSDS table (and linking them with Right Join) instead of a more usual
third
table with ordinary Inner Join?

S. L.

Sylvain,
This first part is the main query I am working with. The second part is
the
query for the CAS numbers. The third part is the VB code from the DAP.
Seems
like a big file to include here, but there is nothing proprietary about
it.
It's too long to post. I'll try sending it separate.

---part 1---
SELECT DISTINCT [MSDS/CAS].CHEMICALNAME, [MSDS/CAS].DESCRIPTION,
[MSDS/CAS].MSDS, [MSDS/CAS].MANUFACTURER, [MSDS/CAS].PRODUCTNAMENUMBER,
[MSDS/CAS].CURRENTUSE, [MSDS/CAS].[DISPOSAL DATE],
[MSDS/CAS].PURCASEDATE,
[MSDS/CAS].LOCATION, [MSDS/CAS].TYPE, [MSDS/CAS].STATE,
[MSDS/CAS].HEALTH,
[MSDS/CAS].FIRE, [MSDS/CAS].REACTIVITY, [MSDS/CAS].SPECIALPRECAUTIONS,
[MSDS/CAS].COMMENTS, [MSDS/CAS].PDF, [MSDS/CAS].CASNUMBER1,
[MSDS/CAS].CASNUMBER2, [MSDS/CAS].CASNUMBER3, [MSDS/CAS].CASNUMBER4,
[MSDS/CAS].CASNUMBER5, [MSDS/CAS].CASNUMBER6, [MSDS/CAS].CASNUMBER7,
[MSDS/CAS].CASNUMBER8, [MSDS/CAS].CASNUMBER9, [MSDS/CAS].CASNUMBER10,
[CAS
Query1].CHEMCIAL, [CAS Query2].CHEMCIAL, [CAS Query3].CHEMCIAL, [CAS
Query4].CHEMCIAL, [CAS Query5].CHEMCIAL, [CAS Query6].CHEMCIAL, [CAS
Query7].CASNUMBER7, [CAS Query7].CHEMCIAL, [CAS Query8].CHEMCIAL, [CAS
Query10].CHEMCIAL
FROM [CAS Query10] RIGHT JOIN ([CAS Query9] RIGHT JOIN ([CAS Query8]
RIGHT
JOIN ([CAS Query7] RIGHT JOIN ([CAS Query6] RIGHT JOIN ([CAS Query5]
RIGHT
JOIN ([CAS Query4] RIGHT JOIN ([CAS Query3] RIGHT JOIN ([CAS Query2]
RIGHT
JOIN ([CAS Query1] RIGHT JOIN [MSDS/CAS] ON [CAS Query1].CAS =
[MSDS/CAS].CASNUMBER1) ON [CAS Query2].CAS = [MSDS/CAS].CASNUMBER2) ON
[CAS
Query3].CAS = [MSDS/CAS].CASNUMBER3) ON [CAS Query4].CAS =
[MSDS/CAS].CASNUMBER4) ON [CAS Query5].CAS = [MSDS/CAS].CASNUMBER5) ON
[CAS
Query6].CAS = [MSDS/CAS].CASNUMBER6) ON [CAS Query7].CAS =
[MSDS/CAS].CASNUMBER7) ON [CAS Query8].CAS = [MSDS/CAS].CASNUMBER8) ON
[CAS
Query9].CAS = [MSDS/CAS].CASNUMBER9) ON [CAS Query10].CAS =
[MSDS/CAS].CASNUMBER10;

---part 2---
SELECT DISTINCTROW CAS.CAS, CAS.CHEMCIAL, MSDS.CASNUMBER9
FROM CAS INNER JOIN MSDS ON CAS.CAS = MSDS.CASNUMBER9;

 

[Guest]

I started to reply yesterday when the lights went out.

I tried the solutions you gave me. I re-structured the databases so no field
names were also used as the table name. I made a new autonumber primary key
for MSDS. The Chemicalname field is now indexed with no duplicates allowed.
The CAS database is still keyed on the same field, but it is renamed IdCAS.
That part's good.

I tried the new query with the third table. I think I entered everything
correctly, but I only get a blank table for a response.

Before I read the latest response I tried using a third table with all
fields from both of the other tables. I was able to make a query and design a
DAP that worked (sort of) from that. The thing I noticed is the "Filter
Toggle Button" was highlighted. When I clicked on that with a record that had
missing data the data would show up. I went back to the original DAP and
inserted the navigation toolbar. I copied and pasted the filter toggle button
to the record area of the page, then deleted the toolbar again. Now when a
record has missing data I can click on the filter toggle button 5-10 times
and the data appears. It's kind of sloppy, but for now it works. Does that
shed anymore light on this problem?

By the way I said that DAP sort of worked because the whole page needs to be
filtered to the pull-down at the top that selects the substance we want the
information about. Every thing worked except that, so I went back to the
original page. With the new page I still had missing data but it was after
100-200 records, not just 30.

Then my first suggestion would be that you use an Autoincrement ID as the
primary key for your MSDS table. This is not mandatory but using names as
primary key is asking for trouble because you cannot change the name later.
Also, using MSDS and CAS both as the name of the table and the name of one
of the field can lead to confusion, too.

Create a table MsdsCas with the following three fields:

IdMsdsCas --> the primary key for this new table
Msds --> the foreigh key for the table MSDS
Cas --> the foreigh key for the table CAS (by the way, would not be a
bad idea to rename it IdCas)

You can also use Msds and Cas to roll out a primary key with two fields
(Msds and Cas) but again, this may could more confusion than help.

Finally, you select clause will be something like:

Select M.Msds, M.Manufacturer, ...., C.Cas, C.ChemicalName,
C.Description, ...., MC.IdMsdsCas
From Cas as C inner join (MSDS as M inner join MsdsCas MC on M.Msds =
MC.Msds) on C.Cas = MC.Cas

This is much simpler.

Also, after a second reading of your previous message, I see that you didn't
provide a definition for the table (or query?) that you call « [MSDS/CAS] ».
If necessary, modify the above query to take care of this table.

S. L.

No. I have used several database programs, but I am fairley new to Access.
I
had trouble setting up the third table so I went this route instead. I
would
like to try the third table.

Excerpt for the unusual high number of Rigth Join, your piece of code
looks
perfectly valid to me.

Is there is any particular reason that you must have 10 CAS fields in the
MSDS table (and linking them with Right Join) instead of a more usual
third
table with ordinary Inner Join?

S. L.

Sylvain,
This first part is the main query I am working with. The second part is
the
query for the CAS numbers. The third part is the VB code from the DAP.
Seems
like a big file to include here, but there is nothing proprietary about
it.
It's too long to post. I'll try sending it separate.

---part 1---
SELECT DISTINCT [MSDS/CAS].CHEMICALNAME, [MSDS/CAS].DESCRIPTION,
[MSDS/CAS].MSDS, [MSDS/CAS].MANUFACTURER, [MSDS/CAS].PRODUCTNAMENUMBER,
[MSDS/CAS].CURRENTUSE, [MSDS/CAS].[DISPOSAL DATE],
[MSDS/CAS].PURCASEDATE,
[MSDS/CAS].LOCATION, [MSDS/CAS].TYPE, [MSDS/CAS].STATE,
[MSDS/CAS].HEALTH,
[MSDS/CAS].FIRE, [MSDS/CAS].REACTIVITY, [MSDS/CAS].SPECIALPRECAUTIONS,
[MSDS/CAS].COMMENTS, [MSDS/CAS].PDF, [MSDS/CAS].CASNUMBER1,
[MSDS/CAS].CASNUMBER2, [MSDS/CAS].CASNUMBER3, [MSDS/CAS].CASNUMBER4,
[MSDS/CAS].CASNUMBER5, [MSDS/CAS].CASNUMBER6, [MSDS/CAS].CASNUMBER7,
[MSDS/CAS].CASNUMBER8, [MSDS/CAS].CASNUMBER9, [MSDS/CAS].CASNUMBER10,
[CAS
Query1].CHEMCIAL, [CAS Query2].CHEMCIAL, [CAS Query3].CHEMCIAL, [CAS
Query4].CHEMCIAL, [CAS Query5].CHEMCIAL, [CAS Query6].CHEMCIAL, [CAS
Query7].CASNUMBER7, [CAS Query7].CHEMCIAL, [CAS Query8].CHEMCIAL, [CAS
Query10].CHEMCIAL
FROM [CAS Query10] RIGHT JOIN ([CAS Query9] RIGHT JOIN ([CAS Query8]
RIGHT
JOIN ([CAS Query7] RIGHT JOIN ([CAS Query6] RIGHT JOIN ([CAS Query5]
RIGHT
JOIN ([CAS Query4] RIGHT JOIN ([CAS Query3] RIGHT JOIN ([CAS Query2]
RIGHT
JOIN ([CAS Query1] RIGHT JOIN [MSDS/CAS] ON [CAS Query1].CAS =
[MSDS/CAS].CASNUMBER1) ON [CAS Query2].CAS = [MSDS/CAS].CASNUMBER2) ON
[CAS
Query3].CAS = [MSDS/CAS].CASNUMBER3) ON [CAS Query4].CAS =
[MSDS/CAS].CASNUMBER4) ON [CAS Query5].CAS = [MSDS/CAS].CASNUMBER5) ON
[CAS
Query6].CAS = [MSDS/CAS].CASNUMBER6) ON [CAS Query7].CAS =
[MSDS/CAS].CASNUMBER7) ON [CAS Query8].CAS = [MSDS/CAS].CASNUMBER8) ON
[CAS
Query9].CAS = [MSDS/CAS].CASNUMBER9) ON [CAS Query10].CAS =
[MSDS/CAS].CASNUMBER10;

---part 2---
SELECT DISTINCTROW CAS.CAS, CAS.CHEMCIAL, MSDS.CASNUMBER9
FROM CAS INNER JOIN MSDS ON CAS.CAS = MSDS.CASNUMBER9;

 

[Sylvain Lafontaine]

Without the exact structure of your tables, it's still hard to find what may
go wrong.

If your code is not confidential, then you can zip it and send it to me.

S. L.

I started to reply yesterday when the lights went out.

I tried the solutions you gave me. I re-structured the databases so no
field
names were also used as the table name. I made a new autonumber primary
key
for MSDS. The Chemicalname field is now indexed with no duplicates
allowed.
The CAS database is still keyed on the same field, but it is renamed
IdCAS.
That part's good.

I tried the new query with the third table. I think I entered everything
correctly, but I only get a blank table for a response.

Before I read the latest response I tried using a third table with all
fields from both of the other tables. I was able to make a query and
design a
DAP that worked (sort of) from that. The thing I noticed is the "Filter
Toggle Button" was highlighted. When I clicked on that with a record that
had
missing data the data would show up. I went back to the original DAP and
inserted the navigation toolbar. I copied and pasted the filter toggle
button
to the record area of the page, then deleted the toolbar again. Now when a
record has missing data I can click on the filter toggle button 5-10 times
and the data appears. It's kind of sloppy, but for now it works. Does that
shed anymore light on this problem?

By the way I said that DAP sort of worked because the whole page needs to
be
filtered to the pull-down at the top that selects the substance we want
the
information about. Every thing worked except that, so I went back to the
original page. With the new page I still had missing data but it was after
100-200 records, not just 30.

Then my first suggestion would be that you use an Autoincrement ID as the
primary key for your MSDS table. This is not mandatory but using names
as
primary key is asking for trouble because you cannot change the name
later.
Also, using MSDS and CAS both as the name of the table and the name of
one
of the field can lead to confusion, too.

Create a table MsdsCas with the following three fields:

IdMsdsCas --> the primary key for this new table
Msds --> the foreigh key for the table MSDS
Cas --> the foreigh key for the table CAS (by the way, would not
be a
bad idea to rename it IdCas)

You can also use Msds and Cas to roll out a primary key with two fields
(Msds and Cas) but again, this may could more confusion than help.

Finally, you select clause will be something like:

Select M.Msds, M.Manufacturer, ...., C.Cas, C.ChemicalName,
C.Description, ...., MC.IdMsdsCas
From Cas as C inner join (MSDS as M inner join MsdsCas MC on M.Msds =
MC.Msds) on C.Cas = MC.Cas

This is much simpler.

Also, after a second reading of your previous message, I see that you
didn't
provide a definition for the table (or query?) that you call «
[MSDS/CAS] ».
If necessary, modify the above query to take care of this table.

S. L.

No. I have used several database programs, but I am fairley new to
Access.
I
had trouble setting up the third table so I went this route instead. I
would
like to try the third table.

:

Excerpt for the unusual high number of Rigth Join, your piece of code
looks
perfectly valid to me.

Is there is any particular reason that you must have 10 CAS fields in
the
MSDS table (and linking them with Right Join) instead of a more usual
third
table with ordinary Inner Join?

S. L.

Sylvain,
This first part is the main query I am working with. The second part
is
the
query for the CAS numbers. The third part is the VB code from the
DAP.
Seems
like a big file to include here, but there is nothing proprietary
about
it.
It's too long to post. I'll try sending it separate.

---part 1---
SELECT DISTINCT [MSDS/CAS].CHEMICALNAME, [MSDS/CAS].DESCRIPTION,
[MSDS/CAS].MSDS, [MSDS/CAS].MANUFACTURER,
[MSDS/CAS].PRODUCTNAMENUMBER,
[MSDS/CAS].CURRENTUSE, [MSDS/CAS].[DISPOSAL DATE],
[MSDS/CAS].PURCASEDATE,
[MSDS/CAS].LOCATION, [MSDS/CAS].TYPE, [MSDS/CAS].STATE,
[MSDS/CAS].HEALTH,
[MSDS/CAS].FIRE, [MSDS/CAS].REACTIVITY,
[MSDS/CAS].SPECIALPRECAUTIONS,
[MSDS/CAS].COMMENTS, [MSDS/CAS].PDF, [MSDS/CAS].CASNUMBER1,
[MSDS/CAS].CASNUMBER2, [MSDS/CAS].CASNUMBER3, [MSDS/CAS].CASNUMBER4,
[MSDS/CAS].CASNUMBER5, [MSDS/CAS].CASNUMBER6, [MSDS/CAS].CASNUMBER7,
[MSDS/CAS].CASNUMBER8, [MSDS/CAS].CASNUMBER9,
[MSDS/CAS].CASNUMBER10,
[CAS
Query1].CHEMCIAL, [CAS Query2].CHEMCIAL, [CAS Query3].CHEMCIAL, [CAS
Query4].CHEMCIAL, [CAS Query5].CHEMCIAL, [CAS Query6].CHEMCIAL, [CAS
Query7].CASNUMBER7, [CAS Query7].CHEMCIAL, [CAS Query8].CHEMCIAL,
[CAS
Query10].CHEMCIAL
FROM [CAS Query10] RIGHT JOIN ([CAS Query9] RIGHT JOIN ([CAS Query8]
RIGHT
JOIN ([CAS Query7] RIGHT JOIN ([CAS Query6] RIGHT JOIN ([CAS Query5]
RIGHT
JOIN ([CAS Query4] RIGHT JOIN ([CAS Query3] RIGHT JOIN ([CAS Query2]
RIGHT
JOIN ([CAS Query1] RIGHT JOIN [MSDS/CAS] ON [CAS Query1].CAS =
[MSDS/CAS].CASNUMBER1) ON [CAS Query2].CAS = [MSDS/CAS].CASNUMBER2)
ON
[CAS
Query3].CAS = [MSDS/CAS].CASNUMBER3) ON [CAS Query4].CAS =
[MSDS/CAS].CASNUMBER4) ON [CAS Query5].CAS = [MSDS/CAS].CASNUMBER5)
ON
[CAS
Query6].CAS = [MSDS/CAS].CASNUMBER6) ON [CAS Query7].CAS =
[MSDS/CAS].CASNUMBER7) ON [CAS Query8].CAS = [MSDS/CAS].CASNUMBER8)
ON
[CAS
Query9].CAS = [MSDS/CAS].CASNUMBER9) ON [CAS Query10].CAS =
[MSDS/CAS].CASNUMBER10;

---part 2---
SELECT DISTINCTROW CAS.CAS, CAS.CHEMCIAL, MSDS.CASNUMBER9
FROM CAS INNER JOIN MSDS ON CAS.CAS = MSDS.CASNUMBER9;

 

[Guest]

I tried figuring out your e-mail. We'll see if it worked.

Without the exact structure of your tables, it's still hard to find what may
go wrong.

If your code is not confidential, then you can zip it and send it to me.

S. L.

I started to reply yesterday when the lights went out.

I tried the solutions you gave me. I re-structured the databases so no
field
names were also used as the table name. I made a new autonumber primary
key
for MSDS. The Chemicalname field is now indexed with no duplicates
allowed.
The CAS database is still keyed on the same field, but it is renamed
IdCAS.
That part's good.

I tried the new query with the third table. I think I entered everything
correctly, but I only get a blank table for a response.

Before I read the latest response I tried using a third table with all
fields from both of the other tables. I was able to make a query and
design a
DAP that worked (sort of) from that. The thing I noticed is the "Filter
Toggle Button" was highlighted. When I clicked on that with a record that
had
missing data the data would show up. I went back to the original DAP and
inserted the navigation toolbar. I copied and pasted the filter toggle
button
to the record area of the page, then deleted the toolbar again. Now when a
record has missing data I can click on the filter toggle button 5-10 times
and the data appears. It's kind of sloppy, but for now it works. Does that
shed anymore light on this problem?

By the way I said that DAP sort of worked because the whole page needs to
be
filtered to the pull-down at the top that selects the substance we want
the
information about. Every thing worked except that, so I went back to the
original page. With the new page I still had missing data but it was after
100-200 records, not just 30.

Then my first suggestion would be that you use an Autoincrement ID as the
primary key for your MSDS table. This is not mandatory but using names
as
primary key is asking for trouble because you cannot change the name
later.
Also, using MSDS and CAS both as the name of the table and the name of
one
of the field can lead to confusion, too.

Create a table MsdsCas with the following three fields:

IdMsdsCas --> the primary key for this new table
Msds --> the foreigh key for the table MSDS
Cas --> the foreigh key for the table CAS (by the way, would not
be a
bad idea to rename it IdCas)

You can also use Msds and Cas to roll out a primary key with two fields
(Msds and Cas) but again, this may could more confusion than help.

Finally, you select clause will be something like:

Select M.Msds, M.Manufacturer, ...., C.Cas, C.ChemicalName,
C.Description, ...., MC.IdMsdsCas
From Cas as C inner join (MSDS as M inner join MsdsCas MC on M.Msds =
MC.Msds) on C.Cas = MC.Cas

This is much simpler.

Also, after a second reading of your previous message, I see that you
didn't
provide a definition for the table (or query?) that you call «
[MSDS/CAS] ».
If necessary, modify the above query to take care of this table.

S. L.

No. I have used several database programs, but I am fairley new to
Access.
I
had trouble setting up the third table so I went this route instead. I
would
like to try the third table.

:

Excerpt for the unusual high number of Rigth Join, your piece of code
looks
perfectly valid to me.

Is there is any particular reason that you must have 10 CAS fields in
the
MSDS table (and linking them with Right Join) instead of a more usual
third
table with ordinary Inner Join?

S. L.

Sylvain,
This first part is the main query I am working with. The second part
is
the
query for the CAS numbers. The third part is the VB code from the
DAP.
Seems
like a big file to include here, but there is nothing proprietary
about
it.
It's too long to post. I'll try sending it separate.

---part 1---
SELECT DISTINCT [MSDS/CAS].CHEMICALNAME, [MSDS/CAS].DESCRIPTION,
[MSDS/CAS].MSDS, [MSDS/CAS].MANUFACTURER,
[MSDS/CAS].PRODUCTNAMENUMBER,
[MSDS/CAS].CURRENTUSE, [MSDS/CAS].[DISPOSAL DATE],
[MSDS/CAS].PURCASEDATE,
[MSDS/CAS].LOCATION, [MSDS/CAS].TYPE, [MSDS/CAS].STATE,
[MSDS/CAS].HEALTH,
[MSDS/CAS].FIRE, [MSDS/CAS].REACTIVITY,
[MSDS/CAS].SPECIALPRECAUTIONS,
[MSDS/CAS].COMMENTS, [MSDS/CAS].PDF, [MSDS/CAS].CASNUMBER1,
[MSDS/CAS].CASNUMBER2, [MSDS/CAS].CASNUMBER3, [MSDS/CAS].CASNUMBER4,
[MSDS/CAS].CASNUMBER5, [MSDS/CAS].CASNUMBER6, [MSDS/CAS].CASNUMBER7,
[MSDS/CAS].CASNUMBER8, [MSDS/CAS].CASNUMBER9,
[MSDS/CAS].CASNUMBER10,
[CAS
Query1].CHEMCIAL, [CAS Query2].CHEMCIAL, [CAS Query3].CHEMCIAL, [CAS
Query4].CHEMCIAL, [CAS Query5].CHEMCIAL, [CAS Query6].CHEMCIAL, [CAS
Query7].CASNUMBER7, [CAS Query7].CHEMCIAL, [CAS Query8].CHEMCIAL,
[CAS
Query10].CHEMCIAL
FROM [CAS Query10] RIGHT JOIN ([CAS Query9] RIGHT JOIN ([CAS Query8]
RIGHT
JOIN ([CAS Query7] RIGHT JOIN ([CAS Query6] RIGHT JOIN ([CAS Query5]
RIGHT
JOIN ([CAS Query4] RIGHT JOIN ([CAS Query3] RIGHT JOIN ([CAS Query2]
RIGHT
JOIN ([CAS Query1] RIGHT JOIN [MSDS/CAS] ON [CAS Query1].CAS =
[MSDS/CAS].CASNUMBER1) ON [CAS Query2].CAS = [MSDS/CAS].CASNUMBER2)
ON
[CAS
Query3].CAS = [MSDS/CAS].CASNUMBER3) ON [CAS Query4].CAS =
[MSDS/CAS].CASNUMBER4) ON [CAS Query5].CAS = [MSDS/CAS].CASNUMBER5)
ON
[CAS
Query6].CAS = [MSDS/CAS].CASNUMBER6) ON [CAS Query7].CAS =
[MSDS/CAS].CASNUMBER7) ON [CAS Query8].CAS = [MSDS/CAS].CASNUMBER8)
ON
[CAS
Query9].CAS = [MSDS/CAS].CASNUMBER9) ON [CAS Query10].CAS =
[MSDS/CAS].CASNUMBER10;

---part 2---
SELECT DISTINCTROW CAS.CAS, CAS.CHEMCIAL, MSDS.CASNUMBER9
FROM CAS INNER JOIN MSDS ON CAS.CAS = MSDS.CASNUMBER9;

 

[Guest]

The repairs you made to the code worked perfectly. Thanks for your help.

I tried figuring out your e-mail. We'll see if it worked.

Without the exact structure of your tables, it's still hard to find what may
go wrong.

If your code is not confidential, then you can zip it and send it to me.

S. L.

I started to reply yesterday when the lights went out.

I tried the solutions you gave me. I re-structured the databases so no
field
names were also used as the table name. I made a new autonumber primary
key
for MSDS. The Chemicalname field is now indexed with no duplicates
allowed.
The CAS database is still keyed on the same field, but it is renamed
IdCAS.
That part's good.

I tried the new query with the third table. I think I entered everything
correctly, but I only get a blank table for a response.

Before I read the latest response I tried using a third table with all
fields from both of the other tables. I was able to make a query and
design a
DAP that worked (sort of) from that. The thing I noticed is the "Filter
Toggle Button" was highlighted. When I clicked on that with a record that
had
missing data the data would show up. I went back to the original DAP and
inserted the navigation toolbar. I copied and pasted the filter toggle
button
to the record area of the page, then deleted the toolbar again. Now when a
record has missing data I can click on the filter toggle button 5-10 times
and the data appears. It's kind of sloppy, but for now it works. Does that
shed anymore light on this problem?

By the way I said that DAP sort of worked because the whole page needs to
be
filtered to the pull-down at the top that selects the substance we want
the
information about. Every thing worked except that, so I went back to the
original page. With the new page I still had missing data but it was after
100-200 records, not just 30.

:

Then my first suggestion would be that you use an Autoincrement ID as the
primary key for your MSDS table. This is not mandatory but using names
as
primary key is asking for trouble because you cannot change the name
later.
Also, using MSDS and CAS both as the name of the table and the name of
one
of the field can lead to confusion, too.

Create a table MsdsCas with the following three fields:

IdMsdsCas --> the primary key for this new table
Msds --> the foreigh key for the table MSDS
Cas --> the foreigh key for the table CAS (by the way, would not
be a
bad idea to rename it IdCas)

You can also use Msds and Cas to roll out a primary key with two fields
(Msds and Cas) but again, this may could more confusion than help.

Finally, you select clause will be something like:

Select M.Msds, M.Manufacturer, ...., C.Cas, C.ChemicalName,
C.Description, ...., MC.IdMsdsCas
From Cas as C inner join (MSDS as M inner join MsdsCas MC on M.Msds =
MC.Msds) on C.Cas = MC.Cas

This is much simpler.

Also, after a second reading of your previous message, I see that you
didn't
provide a definition for the table (or query?) that you call «
[MSDS/CAS] ».
If necessary, modify the above query to take care of this table.

S. L.

No. I have used several database programs, but I am fairley new to
Access.
I
had trouble setting up the third table so I went this route instead. I
would
like to try the third table.

:

Excerpt for the unusual high number of Rigth Join, your piece of code
looks
perfectly valid to me.

Is there is any particular reason that you must have 10 CAS fields in
the
MSDS table (and linking them with Right Join) instead of a more usual
third
table with ordinary Inner Join?

S. L.

Sylvain,
This first part is the main query I am working with. The second part
is
the
query for the CAS numbers. The third part is the VB code from the
DAP.
Seems
like a big file to include here, but there is nothing proprietary
about
it.
It's too long to post. I'll try sending it separate.

---part 1---
SELECT DISTINCT [MSDS/CAS].CHEMICALNAME, [MSDS/CAS].DESCRIPTION,
[MSDS/CAS].MSDS, [MSDS/CAS].MANUFACTURER,
[MSDS/CAS].PRODUCTNAMENUMBER,
[MSDS/CAS].CURRENTUSE, [MSDS/CAS].[DISPOSAL DATE],
[MSDS/CAS].PURCASEDATE,
[MSDS/CAS].LOCATION, [MSDS/CAS].TYPE, [MSDS/CAS].STATE,
[MSDS/CAS].HEALTH,
[MSDS/CAS].FIRE, [MSDS/CAS].REACTIVITY,
[MSDS/CAS].SPECIALPRECAUTIONS,
[MSDS/CAS].COMMENTS, [MSDS/CAS].PDF, [MSDS/CAS].CASNUMBER1,
[MSDS/CAS].CASNUMBER2, [MSDS/CAS].CASNUMBER3, [MSDS/CAS].CASNUMBER4,
[MSDS/CAS].CASNUMBER5, [MSDS/CAS].CASNUMBER6, [MSDS/CAS].CASNUMBER7,
[MSDS/CAS].CASNUMBER8, [MSDS/CAS].CASNUMBER9,
[MSDS/CAS].CASNUMBER10,
[CAS
Query1].CHEMCIAL, [CAS Query2].CHEMCIAL, [CAS Query3].CHEMCIAL, [CAS
Query4].CHEMCIAL, [CAS Query5].CHEMCIAL, [CAS Query6].CHEMCIAL, [CAS
Query7].CASNUMBER7, [CAS Query7].CHEMCIAL, [CAS Query8].CHEMCIAL,
[CAS
Query10].CHEMCIAL
FROM [CAS Query10] RIGHT JOIN ([CAS Query9] RIGHT JOIN ([CAS Query8]
RIGHT
JOIN ([CAS Query7] RIGHT JOIN ([CAS Query6] RIGHT JOIN ([CAS Query5]
RIGHT
JOIN ([CAS Query4] RIGHT JOIN ([CAS Query3] RIGHT JOIN ([CAS Query2]
RIGHT
JOIN ([CAS Query1] RIGHT JOIN [MSDS/CAS] ON [CAS Query1].CAS =
[MSDS/CAS].CASNUMBER1) ON [CAS Query2].CAS = [MSDS/CAS].CASNUMBER2)
ON
[CAS
Query3].CAS = [MSDS/CAS].CASNUMBER3) ON [CAS Query4].CAS =
[MSDS/CAS].CASNUMBER4) ON [CAS Query5].CAS = [MSDS/CAS].CASNUMBER5)
ON
[CAS
Query6].CAS = [MSDS/CAS].CASNUMBER6) ON [CAS Query7].CAS =
[MSDS/CAS].CASNUMBER7) ON [CAS Query8].CAS = [MSDS/CAS].CASNUMBER8)
ON
[CAS
Query9].CAS = [MSDS/CAS].CASNUMBER9) ON [CAS Query10].CAS =
[MSDS/CAS].CASNUMBER10;

---part 2---
SELECT DISTINCTROW CAS.CAS, CAS.CHEMCIAL, MSDS.CASNUMBER9
FROM CAS INNER JOIN MSDS ON CAS.CAS = MSDS.CASNUMBER9;