Query last record in web database

[rc-alex]

Hello,

I need to query the last record in a table in a web database. I cannot figure out a solution to this.

tbl_Employees
EmpID
EmpName
EmpPhone....

tbl_update
updateID
EmpID "applies to"
EmpID "approver"
NewInfo

qry_LatestUpdate
UpdateID
EmpName (applies to)
EmpName (approver)

The query should show the last record for each employee, so if we have 50 employees there might be 300 records in tbl_Update, but only 50 show in qry_LatestUpdate.

Thanks.

 

[pbaldy]

Does this technique work for you?

http://www.baldyweb.com/LastValue.htm

 

[rc-alex]

Paul,

Web queries don't allow the option of using the "Totals" row in query designer, so you can't do group, last, etc.

It seems there defiantely has to be a way to find the last record, as it would be a common requirement. I'm just not finding it.

Thanks,
Alex

 

[rc-alex]

Is there any creative way to do this, with sorting or anything?

Or some way to write the salary change as a new record in the tbl_SalaryChagnes and also to a field in the tbl_Employees?

 

[pbaldy]

Sorry, I forgot about the lack of the Totals option on web queries. I have only played with web databases, never designed a production application in one. Two thoughts come to mind. One is to use a data macro to keep a separate table updated with the most recent record for each person. Another would be this technique, which I think will work in a web query (Method 1):

http://support.microsoft.com/kb/153747/en-us

Using my example from above, this returns the same result:

SELECT VehicleMiles.CarNum, VehicleMiles.SpeedoEnd, VehicleMiles.DorDate
FROM VehicleMiles
WHERE (((VehicleMiles.DorDate) In (SELECT TOP 1 DorDate FROM VehicleMiles AS Alias WHERE Alias.CarNum = VehicleMiles.CarNum ORDER BY DorDate Desc)));

That would obviously depend on there only be one record per date.

 

[rc-alex]

Paul,

That mentions using the grouping column but there is no grouping in Web queries either.

Is there a way around this or a way to still use it.

I think keeping one table of all salary changes and one table of just the most recent change for each employee is the exact correct approach. Just not sure how to implement it.

I readily admit that I'm a newbie, but I've been reading/searching non-stop trying to find a solution to this. I have to show the DB in 1 day, so I have to get this working ASAP.

Thank you,
Alex

 

[rc-alex]

Paul,

I uploaded a screenshot trying that method, with my tables. I believe it's right but I could be wrong. Access tells me that this is not compatible with web databases.

Is this right?

If so, do you have a link to anything explaining
"One is to use a data macro to keep a separate table updated with the most recent record for each person."

The data macro sounds great to me. I'm just not familiar with it, and need to read up on it fast as I have to have this running tomorrow.

Thanks,
Alex

 

[rc-alex]

It looks like this might be the right thing? I'm going to give it a try now and see.

http://blogs.office.com/b/microsoft...ss-2010-data-macros-to-create-aggregates.aspx

I am having a little trouble following this, so if anyone has basic tutorial or anything, it would help a lot.

Thanks

 

[rc-alex]

Ok,
Here is what I have as a macro (attached as image).

As I understand it, when the TeamMember table is opened, that field would set the field Current_Salary to the first value in the SalaryChange table which has an TeamMemberID matching the row in the TeamMember table. So as long as the SalaryChange table is sorted with newest on top, all 50 employees' latest assigned salary will show in the TeamMember table.

However, the field in the Team Member table is still blank?

Thanks.

 

[rc-alex]

Can anyone help with this data macro to pull the latest value?

I haven't been able to make it work still, and I need to have it up and running today. After I get the macro to work I have to do the same thing for other properties, and generate all the reports, etc.

Thank you!

 

[rc-alex]

Ok, I have a marco in SalaryChange table to write the new value to the TeamMember table.

I also entered an AfterInsert macro in the SalaryChange table to run the macro when a new value is entered. At this point, it would not let me enter new records in SalaryChange table. Which has me believing something is wrong with my syntax maybe? But I can't find the error.

It said my DB was too large to post (5.4 MB) so here are screenshots of the relevane 2 tables and 2 macro builders.

Attachments:
TeamMember table
SalaryChange table
Named Macro in SalaryChange table
AfterInset Macro in SalaryChange table.

Thank you!!

 

[rc-alex]

Ok, I have the macro up and running well. It works on insert (new rows) as well as update of existing rows.

However, I'm having a little trouble with reverting to the old value if a value is delete. Let's say a new row is intered because a promotion is supposed to happen, but for some reason does not, so the new row should be completely deleted (to show old salary, old date, old approver, etc.).

Is there a way to revert back to the last value?

The MS example is similar, but since they are summing, they just subtracted the deleted value. I am replacing the value each time with the new value, so just need to revert back.

Thanks!

 

[ncraigg]

Would you mind posting copies of your macros that got you as far as generating new records? I am trying to accomplish something very similar, and it would save me quite a bit of headache. Thanks

 

[rc-alex]

Craig,
Do you mean to post the new value the user just input in one table via a form, to a second table which the form does not post to?

Please confirm that I understand you and I'll go dig it up.

I've been on other projects the last few weeks so I didn't get it to replace the value with the previous value if the new value is deleted, but at least maybe I can give you a good starting point.

 

[rc-alex]

Craig,

TeamMembers is the table with employee info where current salary is stored. This would be much simpler if a you could use the "Totals" line in web queries as you could simpyl find the LAST value for the given employee.

SalaryChange is the table where each salary change is recorded. The reason this was done is so all salary changes can be displayed later (instead of just overwriting the current salary each time).

The first attachment is the actual macro.
The 2nd attachment goes in the afterInsert and AfterUpdate events, so run the update macro.

I just had a thought. This would probably work better the other way around. Have the form write a new value to the field in the TeamMember table. Then, use the afterUpdate event to write the new parameters to the SalaryChange table.

Hope this helps. If you develop this further please post back! Thanks!

 

[ncraigg]

Thanks for the quick reply and providing your hard work! I'm actually going to try this in the same direction that you did, ie, populate the history table via the form and use the entry to update the main table. I'm working on a simple CRM web database, and I only want to update the main table if one of the fields in the history table = either of 2 values (Interested or Declined), but not if the entry includes any of the other 6 (and potentially more at a later date) values.

I'll let you know how it works out.

 

[rc-alex]

Craig,
I'm looking at CRM options for SharePoint and (unforunately) probably need to make a decision by tomorrow what route to take.

How is the web database working out for you. Have you been able to set up parent and child accounts? Contacts tied to each? If you tie a CFO to the parent company, can you display it when viewing the child company? (Less control of relationships in web databases).

Did you create a table such as "opportunities" to store those? How are you handling tasks?

I don't think it's possible to link to non-web tables is it? We already have a lot of proposals and such in SharePoint lists, further complicating things.

Thanks!

 

[ncraigg]

I'm afraid I can't be of too much help yet. I just upgraded to Access 2010 last week, and my SharePoint host is supposed to upgrade our account to SharePoint 2010 in the next couple of weeks, so my web database is not yet finished, let alone published. I'm slowly working through the various bits of functionality that we need, so I'm not yet sure that it will all be possible. However, we are pretty cash poor right now, so one way or another, I have to figure out how to get something going using existing resources.

As for the 'last' functionality, the macros that you created work well when there is already an existing record, but won't create a new record. Do you have any thoughts on this? It seems that I should be able to do something along the lines of using the LookUpRecord function, but if Where Condition doesn't return a record, trigger the CreateRecord function, but I have no idea what the syntax would be. Maybe something with the If/Else function? Any thoughts? Thanks

 

[rc-alex]

FYI if it helps you with testing/designing the web database, as long as you run the "compatibility checker" on the file tab and it says it is compatible with Access Services, then you should be able to navigate around the database in Access just as you would in SharePoint 2010. So you can go ahead and test now before you get SP 2010.

I'm not sure about the macro. I thought I remembered it working to create a new record (afterInsert event), but maybe not. I got sidetracked before I got too far on that project. Sorry I can't be of more help on that.

Alex