form/subform-type functionality in datasheet view

[jackiepatti]

I am using Access 2000. I have two tables like this...

art
artid (primary key)
title
description
artist

color
colorid (primary key)
artid (foreign key)
RGB

....where there are always exactly three records in color corresponding
to each record in art.

I am trying to allow the user to print out all of the art records and
their associated colors. If I come at this as a form/subform, it won't
let me show continuous forms, only single forms. Since there can be
hundreds of art records, printing out one at a time is not a useful
idea.

So I am trying to create a form in datasheet view like this:
art.artid, art.title, art.description, art.artist, color.RGB(1),
color.RGB(2), color.RGB(3)

In other words, instead of a form/subform scenario, I want the three
records from the color table to be "fields" in each form "record".

I tried to figure out how to use a cross-tab query, but I get hundreds
and hundreds of records since there can be so many different RGB
values.

I'm somehow not thinking this through properly, likely due to being
unfamilair with Access.

Help?

 

[tina]

forms are for displaying and interacting with data, reports are for
displaying and printing data. create a query based on the art and color
tables, correctly linked on the primary/foreign key pair, and including all
the fields you want to see from both tables. then create a report bound to
that query. Group the report on the art ID field, so that the title,
description, and artist are listed only once, in the group header section of
the report, and put the color controls in the detail section of the report.
that way the report will display each art record, with it's color records
directly below it.

hth

 

[jackiepatti]

forms are for displaying and interacting with data, reports are for
displaying and printing data. create a query based on the art and color
tables, correctly linked on the primary/foreign key pair, and including all
the fields you want to see from both tables. then create a report bound to
that query. Group the report on the art ID field, so that the title,
description, and artist are listed only once, in the group header section of
the report, and put the color controls in the detail section of the report.
that way the report will display each art record, with it's color records
directly below it.

Ah, that's the rub. The reason I'm trying to do this in a form is to
allow the three colors to be editable, and a report won't buy me that.

 

[Douglas J. Steele]

You don't need (nor want) a cross-tab query.

Create a new query. Add art to the query once, and add color to the query 3
times. I'm assuming that you have some way of being able to distinguish
between the 3 records in the color table so that you can set appropriate
criteria so that the first join to the color table returns RGB(1), the
second join returns RGB(2) and the third join returns RGB(3).

 

[jackiepatti]

You don't need (nor want) a cross-tab query.

Create a new query. Add art to the query once, and add color to the query 3
times. I'm assuming that you have some way of being able to distinguish
between the 3 records in the color table so that you can set appropriate
criteria so that the first join to the color table returns RGB(1), the
second join returns RGB(2) and the third join returns RGB(3).

No, there's not really anyway to distinguish them. The colors could be
added any old way. Your idea would work if I had color1, color2 and
color3 as fields in my art table as foreign keys to three color
records. I have lots of queries of that sort.

It's trying to do it the other way around that's giving me
difficulties. At least in Access, I'm pretty sure I could write a
workable query in T-SQL, but I can't seem to locate anything analogous
to "set rowcount" in Access's version of SQL.

My problem is another application accesses the database, so I can't
just change the table structure. It's actually a SQL Server database,
I'm trying to build an Access interface to it as an administrative
console. The main application is an ASP application... on one page, a
web form collects data that writes to the art table in a stored proc
that returns the scope identity, and then a couple pages later, three
records are written to the colors table completly independently. So
the table structure has to be how it is with the three color records as
foreign keys to the art table cause I don't have any color info when I
write the art record.

In the Access application, the administrator has to be able to edit all
these various fields and then approve the item. She needs to do a
print-out because she has to take this info into another app and do
some other stuff, then come back to Access and edit the records. There
will likely be scores of them to do daily, if not hundreds, hence me
wanting a datasheet view... or at least continuous forms.

I'm beginning to think I just can't get there from here within Access
though. I've been Googling like crazy and can't seem to figure out how
to solve this problem.

 

[Douglas J. Steele]

No, there's not really anyway to distinguish them. The colors could be
added any old way. Your idea would work if I had color1, color2 and
color3 as fields in my art table as foreign keys to three color
records. I have lots of queries of that sort.

Actually, on reflection I realized it's still possible.

If you create the query I'm talking about, the SQL will look something like

SELECT Art.ArtId, Art.Title, Color.ColorId, Color.RGB,
Color_1.ColorId, Color_1.RGB,
Color_2.ArtId, Color_2.RGB
FROM ((Art INNER JOIN Color ON Art.ArtId = Color.ArtId)
INNER JOIN Color AS Color_1 ON Art.ArtId = Color_1.ArtId)
INNER JOIN Color AS Color_2 ON Art.ArtId = Color_2.ArtId

If you run that, you'll get a cartesan product, which obviously isn't what
you want. However, assuming you don't care what order the 3 colours appear
in, you can add a simply WHERE clause to that query, and you should be okay:


SELECT Art.ArtId, Art.Title, Color.ColorId, Color.RGB,
Color_1.ColorId, Color_1.RGB,
Color_2.ArtId, Color_2.RGB
FROM ((Art INNER JOIN Color ON Art.ArtId = Color.ArtId)
INNER JOIN Color AS Color_1 ON Art.ArtId = Color_1.ArtId)
INNER JOIN Color AS Color_2 ON Art.ArtId = Color_2.ArtId
WHERE Color.ColorId < Color_1.ColorId
AND Color.ColorId < Color_2.ColorId
AND Color_1.ColorId < Color_2.ColorId

 

[jackiepatti]

If you create the query I'm talking about, the SQL will look something like

SELECT Art.ArtId, Art.Title, Color.ColorId, Color.RGB,
Color_1.ColorId, Color_1.RGB,
Color_2.ArtId, Color_2.RGB
FROM ((Art INNER JOIN Color ON Art.ArtId = Color.ArtId)
INNER JOIN Color AS Color_1 ON Art.ArtId = Color_1.ArtId)
INNER JOIN Color AS Color_2 ON Art.ArtId = Color_2.ArtId

If you run that, you'll get a cartesan product, which obviously isn't what
you want. However, assuming you don't care what order the 3 colours appear
in, you can add a simply WHERE clause to that query, and you should be okay:


SELECT Art.ArtId, Art.Title, Color.ColorId, Color.RGB,
Color_1.ColorId, Color_1.RGB,
Color_2.ArtId, Color_2.RGB
FROM ((Art INNER JOIN Color ON Art.ArtId = Color.ArtId)
INNER JOIN Color AS Color_1 ON Art.ArtId = Color_1.ArtId)
INNER JOIN Color AS Color_2 ON Art.ArtId = Color_2.ArtId
WHERE Color.ColorId < Color_1.ColorId
AND Color.ColorId < Color_2.ColorId
AND Color_1.ColorId < Color_2.ColorId

AH HA!

That's very clever, Doug.

That's the same type of logic I'd have used in a T-SQL statement. But
yours is much more elegant as I'd have run 3 seperate queries setting a
parameter equal to the colorid each time and then running either a SET
ROWOCUNT 1 or SELECT TOP 1 kind of statement and then cobbling it all
together at the end.

So I didn't just learn how to accomplish something is Access, but how
to write better SQL also.

Thank you.

 

[jackiepatti]

If you run that, you'll get a cartesan product, which obviously isn't what
you want. However, assuming you don't care what order the 3 colours appear
in, you can add a simply WHERE clause to that query, and you should be okay:


SELECT Art.ArtId, Art.Title, Color.ColorId, Color.RGB,
Color_1.ColorId, Color_1.RGB,
Color_2.ArtId, Color_2.RGB
FROM ((Art INNER JOIN Color ON Art.ArtId = Color.ArtId)
INNER JOIN Color AS Color_1 ON Art.ArtId = Color_1.ArtId)
INNER JOIN Color AS Color_2 ON Art.ArtId = Color_2.ArtId
WHERE Color.ColorId < Color_1.ColorId
AND Color.ColorId < Color_2.ColorId
AND Color_1.ColorId < Color_2.ColorId

Nope, you still get the cartesian product even with the WHERE clause.

My colors table currently has 102 records... and the result of this
query is 306 records.

I had to think about it a bit, but it becomes obvious after some
thinking... the WHERE clause is almost always true, the exception being
the last couple of records.

Color.ColorID is almost always less than *some* Color_1.ColorId -
there's the rub

 

[Douglas J. Steele]

Nope, you still get the cartesian product even with the WHERE clause.

My colors table currently has 102 records... and the result of this
query is 306 records.

I had to think about it a bit, but it becomes obvious after some
thinking... the WHERE clause is almost always true, the exception being
the last couple of records.

Color.ColorID is almost always less than *some* Color_1.ColorId -
there's the rub

That may be, but you've already constrained the tables to compare on ArtId
in the ON clauses.

While it shouldn't be necessary, see if this works any better:

SELECT Art.ArtId, Art.Title, Color.ColorId, Color.RGB,
Color_1.ColorId, Color_1.RGB,
Color_2.ArtId, Color_2.RGB
FROM ((Art INNER JOIN Color ON Art.ArtId = Color.ArtId)
INNER JOIN Color AS Color_1 ON Art.ArtId = Color_1.ArtId)
INNER JOIN Color AS Color_2 ON Art.ArtId = Color_2.ArtId
WHERE ((Color.ArtId = Color_1.ArtId)
AND (Color.ColorId < Color_1.ColorId))
AND ((Color.ArtId = Color_2.ArtId)
AND (Color.ColorId < Color_2.ColorId))
AND ((Color_1.ArtId = Color_2.ArtId)
AND (Color_1.ColorId < Color_2.ColorId))