Table Merge Logic Question

[Macros]

Hoping that someone may have a suggestion on a table merge question that I'm running into based on how some of our billing is setup. 

One table lists the what each of our Invoices contain.  They can be from 1 to n rows.

Table1: InvoiceLines

Unit	Amount	Invoice
10	$   10.00	IR222
11	$       5.00	IR222
10	$   20.00	IR223
5	$   50.00	IR224
9	$ 100.00	IR224
10	$   50.00	IR224

We have a second table that lists when a specific invoice may have been sent.  One invoice may be sent between 1 and n times (such as in the case of a subscription).

Table2:InvoiceRecords

Amount	Invioce	Date
$     15.00	IR222	1/15/2019
$     20.00	IR223	1/20/2019
$   200.00	IR224	1/21/2019
$     15.00	IR222	1/30/2019
$   200.00	IR224	2/1/2019

I'm trying to merge the two tables to allow for additional analysis in one of our reports.  The end result of merging the two above would show every sent date with every line that was included on the invoice matched off the Invoice. 

MergedTable: Hopeful Result

Date	Invoice	Unit	Amount
1/15/2019	IR222	10	$   10.00
1/15/2019	IR222	100	$       5.00
1/20/2019	IR223	10	$   20.00
1/21/2019	IR224	5	$   50.00
1/21/2019	IR224	9	$ 100.00
1/21/2019	IR224	10	$   50.00
1/30/2019	IR222	10	$   10.00
1/30/2019	IR222	11	$       5.00
2/1/2019	IR224	5	$   20.00
2/1/2019	IR224	9	$   50.00
2/1/2019	IR224	10	$ 100.00

I hope this makes sense.  I've tried the merge queries in the query editor but I can't figure out a way to get them to show correctly.  Thank you in advance!

[HotChilli · Accepted solution]

I think you can 'Merge as new' on the Invoice.  Use Inner Join.

Expand the InvoiceRecords to bring back the Date.

Re-arrange as required.

There's a 100 in the Units of the desired tabled outcome - is that a mistake?

[Macros]

Thanks @HotChilli I'll give that a shot.  And yes, you are correct, the $100 was incorrect when making the sample data.  I didn't actually realize that I could expand the columns.  I think that was the key part as it was removing the columns I needed when I tried joining them.

I'll try that out and update.  Thanks again!