Query Design Help

[Guest]

I have two questions that I hope someone can help me on.

1. In one query a particular account can have up to five items on it. When I
run the query, an account with five items will list the account number five
times (one row for each item). Is there any way that I could make separate
columns for each item in the account so that there is only one row for each
account number? Like make columns: Item 1, Item 2, Item 3, Item 4, Item 5 so
that if there is only one item then it would show up in the Item 1 column and
the rest will be blank?

2. In another query, an account might have several different instructions to
it and all the instructions will show up when I run the query. Is there any
way I can run the query so that each account will just show the last modified
instruction? There is a TimeStamp field for the instruction in the query.

Any help will be greatly appreciated. Thanks.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1) Your suggestion violates the First Normal Form rule: "each cell must
be atomic," i.e., there must be only one "item" per column. If you want
to display an account number and the "items" in one row, do it in the
display layer (IOW, use VBA in a report or form to line the "items" up
after one account number). If you really, want to do it in a query
there are kludges out on this newsgroup's archives that describe how to
do it.

2) SELECT account_nbr, instruction
FROM AccountInstructions As AI
WHERE instruction_modified = (SELECT MAX(instruction_modified)
FROM AccountInstructions
WHERE account_nbr = AI.account_nbr)

The "instruction_modified" is the "timestamp" column (aka field).
Change the table/column names as required.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK65dYechKqOuFEgEQLvGwCgk7mUQYoEsP1WDnN4FlS5BEWU5wYAoNnO
Mz79YSVK2cwf7kRyUk4xl9oI
=LuLZ
-----END PGP SIGNATURE-----