Run a report for pending transactions

[Klartigue]

Hello, first off I am a brand new user to access.

In access, I have a table that contains the account number, account name, and primary description of many accounts. In excel, I have a list of accounts that have pending transactions (their settle date is listed in column R). If their settle date has not yet passed, the account has a pending transaction. I am a new user to access and was wondering if there is a way to connect the excel sheet to my access table and run a report in access that generates a report of all the pending trasactions, which are based on the settlement dates in my excel sheet?

Any help would be great I as try to get more accustomed to access.

Thanks

[hansup]

In Access, create a link to your Excel spreadsheet. From the Access 2003 main menu, the choices would be File -> Get External Data -> Link Tables. That will bring up a link dialog where you can select "Microsoft Excel" in the "Files of type" box (near the bottom of the wizard dialog). Then choose your workbook. Next step will be the "Link Spreadsheet Wizard" where you can select the target worksheet (or named range). That wizard will guide you through the remaining steps to complete the link process.

If yours is a different Access version, look through the user interface for a similar import/link option. Or tell us which Access version you're using and someone can likely give you the details.

So assuming you successfully linked to the worksheet, the link name is ExcelData, and both the worksheet and your Access table include a numeric field, acct_num, for account number ... create a query using YourAccessTable and ExcelData which joins them on acct_num. You also want a WHERE condition to limit the result set to only those rows where settle_date has not passed ... which I will interpret as settle_date greater than or equal to the date the query is run. I haven't tested this SQL, but I think it should be close to what you want.

SELECT y.*, x.settle_date
FROM
YourAccessTable AS y INNER JOIN ExcelData AS x
ON y.acct_num = x.acct_num
WHERE x.settle_date >= Date();

You should replace y.* with an explicit list of the YourAccessTable fields you want to see. I didn't know those names, so used *.

[Klartigue]

I have linked the worksheets. To do the rest do I do a simple query wizard?

[hansup]

I don't use the query wizard, so not sure about that. Use it if it works for you.

Alternatively, create a new query, switch to SQL View and paste in the sample I suggested. Change the field, link, and table names to match the names you're using. I suspect that might work; at least it should be quick to find out.

[Klartigue]

I pasted in the sample you gave me:

SELECT Account Number, x.settle_date
FROM
YourAccessTable As y INNER JOIN ExcelData As x
ON y.acct_num = x.acct_num
WHERE x.settle_date >= Date();


I am using Access 2007. In access, the field is labeled Account Number. In the excel sheet, the field consisting of the account numbers is labeled AllocCustomId..

how do i test this code? does it look correct?

[hansup]

I am using Access 2007. In access, the field is labeled Account Number. In the excel sheet, the field consisting of the account numbers is labeled AllocCustomId..

When you have a field name which includes a space, enclose the name with square brackets so the database engine will recognize it as one "thing" instead of two.

SELECT y.[Account Number], x.settle_date
FROM
YourAccessTable As y INNER JOIN ExcelData As x
ON y.[Account Number] = x.AllocCustomId
WHERE x.settle_date >= Date();

If that isn't close enough, try the wizardy thing instead.


how do i test this code? does it look correct?

Switch to datasheet view. I can't recall how that option is presented on the ribbon in Access 2007. Or if you can find an icon with a red exclamation point (which may be labelled as "Run", either as icon text or tooltip), click it.

[Klartigue]

As I go to run this, a parameter box pops up..i was hoping that this would just pull out all the accounts that have a settlement date from 10/24/2011 and forward?

[hansup]

When the database engine encounters something in the SQL which it doesn't recognize (as a field name, table name, SQL keyword, or function), it assumes that unknown thing is a parameter and asks you to give it a value for that parameter.

However, I'm puzzled as to how that could be the explanation here. If you used the query wizard to create the query which is asking for the parameter, I don't understand how that could happen. The wizard should guarantee you don't have anything spelled wrong.

[Klartigue]

ok it works great! thank you!! One more thing. I have accomplished pulling all the accounts with transactions still pending. But In my excel sheet, i also have a column for trade date, cusip, and quantity. Can I have these fields be pulled as well so not its not just the account number and settlement date being displayed after the report is run?

[Klartigue]

Solution to pulling trades with settlement date >=todays date:

SELECT y.[Account Number], x.setdt
FROM
Accounts As y INNER JOIN Blotter As x
ON y.[Account Number] = x.AllocCustomId
WHERE x.setdt >= Date0;

[Klartigue]

oh wait i figured it out, i just went to the design view and added those fields

[hansup]

But In my excel sheet, i also have a column for trade date, cusip, and quantity. Can I have these fields be pulled as well ... ?

Yes. The query designer is great for this.

Switch your query to design view, select the additional fields you want from the x (Blotter) table and drag them into the lower part of the query grid ... to the right of the fields you already have selected.

[hansup]

oh wait i figured it out, i just went to the design view and added those fieldsSorry, I didn't wait long enough. :hi: