Cannot figure out how to get data from two tables together!

[Lawrence_Ames]

Hi Guys,

After hours of trial and error, all errors, I cannot for the life of me figure out how to join data from two different tables.

Table 1 has INVOICE NUMBER, CUSTOMER NAME, and CUSTOMER EMAIL, one Invoice Number per row.

Table 2 has several rows with the same Invoice Number, and one item on each row. So, if the customer just bought one item, there would be only one row with Invoice Number and Item.

If customer bought three items, there would be three rows, with the same invoice number on each row and one item per row in the Item field.

What I want to do is to build a THIRD table that has Name, Email address, and Item, so I can then sort who bought a specific item, or all the items a specific client bought.

But try as I will, I can't figure out how to do it and ANY help would be VERY appreciated.

So, Table 1:

Invoice 1 - Client 1 - Client Email 1

Invoice 2 - Client 2 - Client Email 2

Table 2 has

Invoice 1 - Item 1

Invoice 1 - Item 2

Invoice 2 - Item 3

Invoice 3 - Item 1

Invoice 3 - Item 5

Invoice 3 - Item 6

What I would like is

Client 1 - Item 1

Client 2 - Item 2

Client 2 - Item 3

Client 3 - Item 1

Client 3 - Item 4

I hope this makes sense. THANK YOU in advance!!!

[autumn · Accepted solution]

It sounds like you may be able to get what you need without building a third table.

I suggest you create a linked record field on the Items table that will allow you to Item records in table 2 to a particular Client in table 1.

Then you can add Lookup fields in table 2 for each piece of Client info that you want to display/filter & sort by in the Items table (table 2).

If you test it by manually linking a couple of records and making sure that solution gets you what you need, it's possible to write an automation that will link the rest of your records by looking for a matching Order ID.

[Brad_Williams]

I believe you would build your client table (Table 3). Add a linked record field to your order table (Table 1) so that order is associated with a particular client and any associated lookup fields you might want to display on that table as well.  On Table 2 you could add a lookup field and pull info from client fields you added to Table 1.  

[amir_honarvar]

The best way is always to have a Persons/Companies Table, as they define a side of your business as buyer or seller, or in my solar distribution business, both sides warehouses are can be a third party company.

So the in your order tables, which each row is an invoice, you can easily add a customer by a linked record, and lookup the email and other info, which always is the same for the customer and next invoices.

And it is so right to have the detail of the invoices and group it by orders number, again you can use the same mindset to make an accounting system, or warehousing system, in which the summation of the quantities and total prices would show the total in and out and balance.

All three tables must be linked

In table 1, link Customer (New table)

In table 2, link table 1

also, I suggest making a table for the products and in the table 2 link product from its table.