Excel to MS access

[jobbie]

I have 2 excel files at work where I maintain the rates of assets and the dates when the rates were issued. Another excel file has the list of assets and the dates when they were sold. So one excel file has the following columns: Asset------Rate------Rate_Issued_On 1. X-------1500------21-Apr-2014 2. X-------2000------28-Aug-2013 3. Z-------2200------11-Jan-2014 4. X-------3000------1-Jan-2014 The other excel file has (let's suppose): Asset-----Sold_Date 1. X------1-Dec-2013 2. Z------12-Mar-2014 Now since the sold date of Asset X lies between 1-Jan-2014 and 28-Aug-2013 it should take the rate of 2000. If for example the sold date was 22-Apr-2014 it should take the rate as 1500. If the sold date is 27-Aug-2013 it should display a blank record. So basically the sold date should be greater than the latest Issued date and rate will correspond to that particular date. I can easily get this working in excel but the problem is that the excel file has now become so large that it runs very slow. So I just want this incorporated in ms access. Is this possible? (I am a novice in ms access so kindly requesting you to go a little easy on me) Thanks

[lfpm062010]

If you build your table as you have,

Table: [rates of assets]
Columns: Asset, Rate, Rate_Issued_On

Table: [issue date]
Columns: Asset, Sold_Date

This query should give you the result that you are looking for.

SELECT [issue date].Asset, [issue date].Sold_Date, DMax("Rate","[rates of assets]","[rates of assets].Asset = """ & [issue date].[Asset] & """ AND [rates of assets].Rate_Issued_On <= #" & [issue date].[Sold_Date] & "#") AS Rate
FROM [issue date];

[jobbie]

If you build your table as you have,

Table: [rates of assets]
Columns: Asset, Rate, Rate_Issued_On

Table: [issue date]
Columns: Asset, Sold_Date

This query should give you the result that you are looking for.

SELECT [issue date].Asset, [issue date].Sold_Date, DMax("Rate","[rates of assets]","[rates of assets].Asset = """ & [issue date].[Asset] & """ AND [rates of assets].Rate_Issued_On <= #" & [issue date].[Sold_Date] & "#") AS Rate
FROM [issue date];

Hi,

Can u pls break up the code and explain it? I would sincerely appreciate it... thanks

[rpeare]

I'm going to disagree with lfpm062010's approach and say do not use domain functions (dmax, dsum, dcount, davg) in queries, they are extremely memory intensive and if you have a large dataset the query will be extremely slow.

Try this example:
jobbie.zip

[jobbie]

I'm going to disagree with lfpm062010's approach and say do not use domain functions (dmax, dsum, dcount, davg) in queries, they are extremely memory intensive and if you have a large dataset the query will be extremely slow.

Try this example:
jobbie.zip

Hi,

thanks for ur concern. I ran the Qry_Final_Query from ur example but it displays all the records. Also I don't understand the purpose of Rate_PK, Asset_Pk, etc. Do I have enter their values manually as that would be impossible as I lots of records.

[rpeare]

I gave you a generic example you can adapt your query to look at a specific set of data if that is your goal.

In any table there should be a primary key (PK), if the table references something in another table the table should also contain the foreign key (FK) to that table.

So for instance in the example I gave you each asset has an ID
Each Asset has multiple rate changes, so in the rate table I have the Asset_FK which links to the Asset_PK in the assets table. This is a basic concept of relational databases.

I gave you a simple example that you can adapt to your situation. I do not expect your tables are the same as mine.

[lfpm062010]

I do agree with rpeare that DMAX will be slow in a large data set.
I also want to apologize that my query that will not work 100% of the time.

The idea behind the query is to find the highest “rate_issued_on” date.
SELECT [issue date].Asset, [issue date].Sold_Date, Max([rates of assets].Rate_Issued_On) AS MaxOfRate_Issued_On
FROM [issue date] LEFT JOIN [rates of assets] ON [issue date].Asset = [rates of assets].Asset
WHERE ((([rates of assets].Rate_Issued_On)<=[issue date].[Sold_Date]))
GROUP BY [issue date].Asset, [issue date].Sold_Date;

I modified to be OUTTER JOIN instead of INNER JOIN in the zip file.

Then match back to “rate_issued_on” table to get the rate (inner join with Asset and rate_issued_on fields).
SELECT [Max of Rate Issued On].Asset, [Max of Rate Issued On].Sold_Date, [Max of Rate Issued On].MaxOfRate_Issued_On AS Rate_Issued_On, [rates of assets].Rate
FROM [Max of Rate Issued On] INNER JOIN [rates of assets] ON ([Max of Rate Issued On].MaxOfRate_Issued_On = [rates of assets].Rate_Issued_On) AND ([Max of Rate Issued On].Asset = [rates of assets].Asset);

[jobbie]

I do agree with rpeare that DMAX will be slow in a large data set.
I also want to apologize that my query that will not work 100% of the time.

The idea behind the query is to find the highest “rate_issued_on” date.
SELECT [issue date].Asset, [issue date].Sold_Date, Max([rates of assets].Rate_Issued_On) AS MaxOfRate_Issued_On
FROM [issue date] LEFT JOIN [rates of assets] ON [issue date].Asset = [rates of assets].Asset
WHERE ((([rates of assets].Rate_Issued_On)<=[issue date].[Sold_Date]))
GROUP BY [issue date].Asset, [issue date].Sold_Date;

I modified to be OUTTER JOIN instead of INNER JOIN in the zip file.

Then match back to “rate_issued_on” table to get the rate (inner join with Asset and rate_issued_on fields).
SELECT [Max of Rate Issued On].Asset, [Max of Rate Issued On].Sold_Date, [Max of Rate Issued On].MaxOfRate_Issued_On AS Rate_Issued_On, [rates of assets].Rate
FROM [Max of Rate Issued On] INNER JOIN [rates of assets] ON ([Max of Rate Issued On].MaxOfRate_Issued_On = [rates of assets].Rate_Issued_On) AND ([Max of Rate Issued On].Asset = [rates of assets].Asset);

thank you. I have not yet understood the code but it seems to work

[jobbie]

I do agree with rpeare that DMAX will be slow in a large data set.
I also want to apologize that my query that will not work 100% of the time.

The idea behind the query is to find the highest “rate_issued_on” date.
SELECT [issue date].Asset, [issue date].Sold_Date, Max([rates of assets].Rate_Issued_On) AS MaxOfRate_Issued_On
FROM [issue date] LEFT JOIN [rates of assets] ON [issue date].Asset = [rates of assets].Asset
WHERE ((([rates of assets].Rate_Issued_On)<=[issue date].[Sold_Date]))
GROUP BY [issue date].Asset, [issue date].Sold_Date;

I modified to be OUTTER JOIN instead of INNER JOIN in the zip file.

Then match back to “rate_issued_on” table to get the rate (inner join with Asset and rate_issued_on fields).
SELECT [Max of Rate Issued On].Asset, [Max of Rate Issued On].Sold_Date, [Max of Rate Issued On].MaxOfRate_Issued_On AS Rate_Issued_On, [rates of assets].Rate
FROM [Max of Rate Issued On] INNER JOIN [rates of assets] ON ([Max of Rate Issued On].MaxOfRate_Issued_On = [rates of assets].Rate_Issued_On) AND ([Max of Rate Issued On].Asset = [rates of assets].Asset);

Hi, I finally spent some time trying to understand your code and I have one question. What if there is an asset say "D" sold and there is an entry in the table Table: [issue date] but there is no entry in the table [rates of assets] which means that an asset is sold but it doesn't have a rate as of yet. So how do I display this record with a null value when I run the query? I tried using an outer left join in the query but it doesn't display it.

Thanks

[lfpm062010]

Sorry for the late replay.

If I understand you correctly, the following query should solve your issue. Of course, I don't know if it will cause other issues since I don't have time to test all the possilble data.

Changed query "Max of Rate Issued On" to
SELECT [issue date].Asset, [issue date].Sold_Date, Max([rates of assets].Rate_Issued_On) AS MaxOfRate_Issued_On
FROM [issue date] LEFT JOIN [rates of assets] ON [issue date].Asset = [rates of assets].Asset
WHERE ((([rates of assets].Rate_Issued_On)<=[issue date].[Sold_Date])) OR ((([rates of assets].Rate_Issued_On) Is Null))
GROUP BY [issue date].Asset, [issue date].Sold_Date;

Change query "qry_issue_rate_rpt" to
SELECT [Max of Rate Issued On].Asset, [Max of Rate Issued On].Sold_Date, [Max of Rate Issued On].MaxOfRate_Issued_On AS Rate_Issued_On, [rates of assets].Rate
FROM [Max of Rate Issued On] LEFT JOIN [rates of assets] ON ([Max of Rate Issued On].MaxOfRate_Issued_On = [rates of assets].Rate_Issued_On) AND ([Max of Rate Issued On].Asset = [rates of assets].Asset);

[jobbie]

Sorry for the late replay.

If I understand you correctly, the following query should solve your issue. Of course, I don't know if it will cause other issues since I don't have time to test all the possilble data.

Changed query "Max of Rate Issued On" to
SELECT [issue date].Asset, [issue date].Sold_Date, Max([rates of assets].Rate_Issued_On) AS MaxOfRate_Issued_On
FROM [issue date] LEFT JOIN [rates of assets] ON [issue date].Asset = [rates of assets].Asset
WHERE ((([rates of assets].Rate_Issued_On)<=[issue date].[Sold_Date])) OR ((([rates of assets].Rate_Issued_On) Is Null))
GROUP BY [issue date].Asset, [issue date].Sold_Date;

Change query "qry_issue_rate_rpt" to
SELECT [Max of Rate Issued On].Asset, [Max of Rate Issued On].Sold_Date, [Max of Rate Issued On].MaxOfRate_Issued_On AS Rate_Issued_On, [rates of assets].Rate
FROM [Max of Rate Issued On] LEFT JOIN [rates of assets] ON ([Max of Rate Issued On].MaxOfRate_Issued_On = [rates of assets].Rate_Issued_On) AND ([Max of Rate Issued On].Asset = [rates of assets].Asset);

Thanks for your reply but I resolved the second issue on my own. I created another query which did a left outer join between the table [issue date] and the query "qry_issue_rate_rpt" and which seems to work fine. but nevertheless full credit goes to you for resolving my main problem... Thanks

[jobbie]

I do agree with rpeare that DMAX will be slow in a large data set.
I also want to apologize that my query that will not work 100% of the time.

The idea behind the query is to find the highest “rate_issued_on” date.
SELECT [issue date].Asset, [issue date].Sold_Date, Max([rates of assets].Rate_Issued_On) AS MaxOfRate_Issued_On
FROM [issue date] LEFT JOIN [rates of assets] ON [issue date].Asset = [rates of assets].Asset
WHERE ((([rates of assets].Rate_Issued_On)<=[issue date].[Sold_Date]))
GROUP BY [issue date].Asset, [issue date].Sold_Date;

I modified to be OUTTER JOIN instead of INNER JOIN in the zip file.

Then match back to “rate_issued_on” table to get the rate (inner join with Asset and rate_issued_on fields).
SELECT [Max of Rate Issued On].Asset, [Max of Rate Issued On].Sold_Date, [Max of Rate Issued On].MaxOfRate_Issued_On AS Rate_Issued_On, [rates of assets].Rate
FROM [Max of Rate Issued On] INNER JOIN [rates of assets] ON ([Max of Rate Issued On].MaxOfRate_Issued_On = [rates of assets].Rate_Issued_On) AND ([Max of Rate Issued On].Asset = [rates of assets].Asset);

I know this thread is a bit old but I have a question. Is it possible to use just one query instead of 2 queries to get the desired output?