Portals and Relationships.... argh!

[crazybake]

These items just make me feel stupid...

I created a database with three tables: assets, clients and transactions. I plan to use for clients to checkout equipment from me.

I have created relationships between the tables. I have a Clients:ClientID matched to Transactions:TransClientID and Transactions:TransItemNumb to Asset:ItemNumber.

I am trying to have a simple layout using the transactions table that will allow me to type in multiple item numbers (like an invoice) and have it return the item name. I can do this with a single item either in a portal or simple related fields, but I cannot figure out how to do so with multiple.

Seems so simple! I am missing an important step here

[bcooney]

I think that you're missing a table, Transaction Line Items (TranLI). That would be the table that is used for a portal on the Transaction Form (Tran>TranLI by TranID). Each TranLI would store an AssetID. You can have the Asset Description lookup as the AssetID is entered in the TranLI record.

[crazybake]

That makes a ton of sense!

But one thing I am still unclear about:

"Each TranLI would store an AssetID. You can have the Asset Description lookup as the AssetID is entered in the TranLI record."

Are you saying that each RECORD within the TranLI would store a single line? And that I would add records to the LI table within the portal on the Transaactions table?

I guess where my confusion is... Do I want/need to have multiple line items per reccord?

[bcooney]

Well, you did say,

I am trying to have a simple layout using the transactions table that will allow me to type in multiple item numbers (like an invoice) and have it return the item name.

So, you'd need a line items table.

Are you saying that each RECORD within the TranLI would store a single line? And that I would add records to the LI table within the portal on the Transaactions table?

Yes.

Expand on your description of reqs, then you'll get more feedback on how to design the data model.

[crazybake]

Thanks for the help...

After REALLY reading and understanding what you told me, all is great. Thanks a ton for the help