Querying from somewhat unrelated tables (hard to title this post)

[bluekable]

Hi all,

I have a challenge that will take some explaining so please bear with me. I am trying this in Access 2013.

I will try to keep tables to "TableName" and fields to [FieldName]
I have been asked to produce a report for my company that requires figures from two different sources, one is a group of tables that are all linked to one another, the second is a single table that is not linked to the first group in a manner that would make this easy but is in the same database.

The structure of the tables is as below:

15588

"Invoices" holds all of the invoices our company has ever had, "InvoiceDetails" holds what products each invoice contained and "InvoiceResources" holds what sub-products each of those subjects held specific to each invoice. "InvoiceResources" also holds the sale price that each subjects was sold for at the time of each invoice but it does not hold the cost price of each resource at that time. If it did there would be no problem, as I could list the sales prices and cost prices next to each other in a query and find a balance between the two.

In the fourth table "ResourcePrices", there is a record of every time the cost price of each of our resources has been updated over the history of our company selling that resource. An example is below:

15589

As you can see the resource Animal farm has changed in price over time.

I now need a way to show every [Resource] and its [SalePrice] from the "InvoiceResources" table but the problem I face is getting the correct cost price for that resource at the time of the invoice.

What I did first was to try and show the cost prices for every resource where the [ResourcePriceDate] was less than the [InvoiceDate] from the "Invoices" table that corresponds to that resource, but this would obviously sometimes give me multiple cost prices as there may have been multiple price updates before an invoice's date. So I then tried to use totals and set the [ResourcePriceDate] field to 'Last' and then 'Max' - neither option worked.

I spoke to a friend of mine who had some more experience with VBA and access than I do and he suggested using a function to select the relevant cost price based on the invoice date of each returned value in my query however I am not sure how exactly a function can select a field from a table based on the result of a query.

Does anyone have any advice or any ideas?

Thanks a lot :-)
Bluekable

[jonh]

You can write a public function and call it from your query



Public Function price(dt As Date) As Currency
SQL = "SELECT TOP 1 price " & _
"FROM pricelist " & _
"WHERE pricedate <=#" & Format(dt, "mm/dd/yyyy") & "#" & _
"ORDER BY pricedate DESC"
price = DBEngine(0)(0).OpenRecordset(SQL)(0)
End Function



SELECT invdate, price([invdate]) AS price FROM invoice


you can also use a sub query



SELECT invdate,
(SELECT TOP 1 price
FROM pricelist
WHERE pricedate<=inv.invdate
ORDER BY pricedate DESC) as price
FROM invoice as inv

[bluekable]

Hi John,

Really appreciate your help! Managed to get it done with a sub-query :-)

Kind regards
Bluekable