Tag Info

New answers tagged

1

Its happening because variable name is not correctly placed inside update code and as both column name and variable name are same so according to your current code you are updating all the records. Try this sqlcmd = "UPDATE 1_tbl_Memberships SET [1_tbl_Memberships].Member = No WHERE [1_tbl_Memberships].GroupMembershipID = "& GroupMembershipID &"" ...


2

Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds: Option Compare Database Option Explicit Sub ...


1

Sorry, this may not be An Answer, but I couldn't add a comment. This is surely a 'solved problem' in that hotel and reservation systems are available commercially 'off the shelf'? Why develop your own? Even if you do decide for good reason that you need to, it might well be worth investigating the data models underlying a few commercial products, if you can ...


1

Cheers for that, why MS Access has to be different to everything else? Mmm. I got to the bottom of it like you said using : G1 = SUM(CASE WHEN GroupNum = 1 THEN 1 ELSE 0 END)*BoardStatus, G2 = SUM(CASE WHEN GroupNum = 2 THEN 1 ELSE 0 END)*BoardStatus, ..... G10 = SUM(CASE WHEN GroupNum = 10 THEN 1 ELSE 0 END)*BoardStatus, G11 = SUM(CASE WHEN GroupNum = 11 ...



Top 50 recent answers are included