473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Update a Field in One Table if There is Data in Another

i have been dabling with databases for years but never to this level.

i have set up a db with two tables linked via relationships on there ID, one for booking and one for catering; this all works fine, but no i would like to have on the booking table the information to say if there is catering or not without having to go back to the sub form on it or the data sheet view.

I can get queries and reports to check all this and that works fine. but i want it to update the table without using queires just to say yes or no.

tbl_Bookings
EventID - Autonumber
Meeting date - date
catering - yes/no
CateringRequests
EventID - number
location - text
qty - number

so if there is catering in the cateringrequest table the catering in the tbl_bookings to then show "yes" if not show "no"

any help?
Jun 22 '16 #1

✓ answered by Seth Schrock

My bad. I hadn't updated my code window. Try this
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
  3.         ", tbl_Bookings.Contact, IIF(Count(CateringRequests.EventID)>0, True, False) As Catering " & _
  4.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  5.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  6.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  7.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact " & _
  8.         "ORDER BY tbl_Bookings.EventID"

38 1351
Seth Schrock
2,965 Expert 2GB
What I would do would be to use a query to tell you this information and not have a field storing it. Just make a LEFT JOIN between tbl_Bookings and CateringRequests so that all records from tbl_Bookings show up and only those in CateringRequests that match. Make it an aggregate query, grouping on each of the fields in tbl_Bookings and then use the Count() statement to count the number of records in CateringRequests that match. If you simply want a yes/no, then use the IIF() function to test if the value is 0 or not.
Jun 22 '16 #2
not quite sure what you mean, and then wouldnt be sure how to get it to show in other windows.

As the db i am working from was from another document which i have been trying to alter to fit my purpose.

attached is the file, hope that make it a little clearer
Attached Files
File Type: zip lite version.zip (340.5 KB, 81 views)
Jun 23 '16 #3
PhilOfWalton
1,430 Expert 1GB
Many years ago I did a calendar program that looked similar to yours.
The substantial difference was that each of the dates was a subform rather than a text box. That meant that if there were several events on the same day, they could be colour coded. The same thing could happen for catering set to true or false. Additionally, though the entry could not be altered directly on the calendar, a double click could either open another form, or in your case, change the value of Catering from false to true.

Something along these lines?



A discussion about it can be found:
MS Access Calendar - Need help with parameters
Jun 23 '16 #4
cool will have a look at that see if that suits better! looks like it might.

i thought about doing loads of sub forms but looked like a very complicated way of doing it and didnt know if it did the same as hide the dates etc.. which are on the one i uploaded.

you think that Microsoft would have done a simple template for something like this. just stuggled to find anything.

but thanks again will have a look see if i can get that to do what i need!
Jun 23 '16 #5
PhilOfWalton
1,430 Expert 1GB
Be warned, it isn't simple, and very heavy on resourses. 42 subforms open at the same time really pushes Access. I have a pretty fast machine and it takes about 10 seconds to load the calendar.

Phil
Jun 23 '16 #6
the only one i can find on that thread looks to very similar to what i have already, or am i just looking at the wrong download?

also with that is there a way of making it start on the Monday rather than Sunday doesn't matter but is annoying.
Jun 23 '16 #7
PhilOfWalton
1,430 Expert 1GB
If you're looking at a calendar titled "Calendar for Walton & Frinton Yacht Club", that's the right one. I should think you can start any day of the week

Phil
Jun 23 '16 #8
can only see the image not db itself.
Jun 23 '16 #9
PhilOfWalton
1,430 Expert 1GB
Sorry, the DB is too complicated to send as it relies on 2 library databases, 2 back end databases and would take for ever to set up.

I just sent the image to show an alternative way of achieving what you wanted.

Phil
Jun 23 '16 #10
Seth Schrock
2,965 Expert 2GB
Try the following query.
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Bookings.EventID
  2.       ,[Meeting date]
  3.       ,IIF(Count(CateringRequests.*)>0, True, False) As CateringRequests
  4. FROM tbl_Bookings LEFT JOIN CateringRequests
  5.       ON tbl_Bookings.EventID = CateringRequests.EventID
  6. GROUP BY tbl_Bookings.EventID, [Meeting Date]
Jun 23 '16 #11
getting a syntax error in query expression
'IIF(count(cateringReuests,*)>0,True,Flase)'
Jun 23 '16 #12
Seth Schrock
2,965 Expert 2GB
You have typos in what you posted back. Please verify that you have copied and pasted what you had. If so, then you need to fix the CateringRequests (missing the q in your post) and the spelling of False at the end.
Jun 23 '16 #13
Yes sorry that was me typing back up the pop up error message
Jun 23 '16 #14
Seth Schrock
2,965 Expert 2GB
Can you copy and paste the query so that I can make sure that everything is correct?
Jun 23 '16 #15
double checked and it still came up with the same error:

Syntax error in query expression 'IIF(cound(cateringRequests.*)>0, True, Fasle)'.
Jun 24 '16 #16
Seth Schrock
2,965 Expert 2GB
You really need to double check your typing as that is where errors occur and then we can't help you because we don't know what you really have.

Again, can you please copy and paste your query code and post that (using code tags). Make sure that you are copying and pasting and not typing it out.
Jun 24 '16 #17
its a windows pop up error, cant copy and paste

screen shot attached

Attached Images
File Type: jpg screen shot.jpg (43.8 KB, 552 views)
Jun 24 '16 #18
Seth Schrock
2,965 Expert 2GB
Not the error, the query SQL itself.
Jun 24 '16 #19
its the query from above
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Bookings.EventID
  2.       ,[Meeting date]
  3.       ,IIF(Count(CateringRequests.*)>0, True, False) As CateringRequests
  4. FROM tbl_Bookings LEFT JOIN CateringRequests
  5.       ON tbl_Bookings.EventID = CateringRequests.EventID
  6. GROUP BY tbl_Bookings.EventID, [Meeting Date]
  7.  
Jun 24 '16 #20
Seth Schrock
2,965 Expert 2GB
Try this instead.
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Bookings.EventID
  2.       ,[Meeting date]
  3.       ,IIF(Count(CateringRequests.EventID)>0, True, False) As CateringRequests
  4. FROM tbl_Bookings LEFT JOIN CateringRequests
  5.       ON tbl_Bookings.EventID = CateringRequests.EventID
  6. GROUP BY tbl_Bookings.EventID, [Meeting Date]
  7.  
Jun 24 '16 #21
yes that runs and comes back with the results as -1 for the ones which have catering.

how would i implement that into the booking table to update the field catering
Jun 24 '16 #22
Seth Schrock
2,965 Expert 2GB
In access, True is represented by -1. You wouldn't update your catering field. You should never store calculated values because then you would have to update the table for every change. That is the point of the query. Every time that it is run, it gives the accurate information. See Database Normalization and Table Structures for more information on that.

If you want something different displayed, then you can simply change the True and False in the IIF() function to be "Yes" and "No" (including the double quotes) or whatever else you would want it to be.
Jun 24 '16 #23
i get that but with the code that i found that runs the bottom bit of the calendar it pulls the data from the Booking table, which is why i was trying to get it to update that.

is there a better way of doing so then? is there a way of altering the VBA code which populates that to include the query?

i believe the below is the code that populates that:

assuming that the strSQL2 is the query how would i alter that to make is include the calculated value not just the data direct from the table.

Expand|Select|Wrap|Line Numbers
  1. Private Sub PopulateEventsList(ctlDayBlock As Control)
  2. On Error GoTo Err_PopulateEventsList
  3. Dim strSQL2 As String
  4.  
  5. strSQL2 = "SELECT tbl_Bookings.[EventID], tbl_Bookings.[EventTitle] As [Course Title], tbl_Bookings.[Status], " & _
  6.             "tbl_Bookings.[StartTime] As [Start Time], tbl_Bookings.[EndTime] As [End Time], " & _
  7.             "tbl_Bookings.[Contact], tbl_Bookings.[Catering]" & _
  8.             "FROM tbl_Bookings WHERE tbl_Bookings.[MeetingDate] = " & _
  9.          ctlDayBlock.Tag & " ORDER BY tbl_Bookings.[EventID];"
  10.  
  11.  
  12.  
  13. lstEvents.RowSource = strSQL2
  14.  
  15. lblEventsOnDate.caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")
  16. lstEvents.Visible = True
  17. lblEventsOnDate.Visible = True
  18.  
  19. Exit_PopulateEventsList:
  20.   Exit Sub
  21.  
  22. Err_PopulateEventsList:
  23.   MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
  24.   Resume Exit_PopulateEventsList
  25. End Sub
  26.  
Jun 24 '16 #24
Seth Schrock
2,965 Expert 2GB
Try replacing lines 5 through 9 with the following.
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
  3.         ", tbl_Bookings.Contact, IIF(Count(CateringRequests.*)>0, True, False) As Catering " & _
  4.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  5.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  6.         "WHERE MeetingDate = ctlDayBlock.Tag " & _
  7.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact " & _
  8.         "ORDER BY tbl_Bookings.EventID"
Jun 24 '16 #25
that then seems to stop that box working all together?

but no error messages
Jun 27 '16 #26
Seth Schrock
2,965 Expert 2GB
Right before the lstEvents.RowSource = strSQL2 line, try putting Debug.Print strSQL2. This will put the concatenated string in the Immediate window (Press Ctrl + G to view it). Copy and paste it into a query designer and try running it to make sure that there are no errors.
Jun 27 '16 #27
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status , tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] , tbl_Bookings.Contact, IIF(Count(CateringRequests.*)>0, True, False) As Catering FROM tbl_Bookings LEFT JOIN CateringRequests ON tbl_Bookings.EventID = CateringRequests.EventID WHERE MeetingDate = ctlDayBlock.Tag GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact ORDER BY tbl_Bookings.EventID
  2.  
This is the code i got back from that, getting that same syntax error as before so changed the IIF(Count(CateringRequests.*)>0, True, False) to
Expand|Select|Wrap|Line Numbers
  1. IIF(Count(CateringRequests.EventID)>0, True, False) As CateringRequests
then get the message box ctlDayBlock.Tag even if run in the main VBA area.
Jun 27 '16 #28
Seth Schrock
2,965 Expert 2GB
My bad. I hadn't updated my code window. Try this
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time] " & _
  3.         ", tbl_Bookings.Contact, IIF(Count(CateringRequests.EventID)>0, True, False) As Catering " & _
  4.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  5.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  6.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  7.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact " & _
  8.         "ORDER BY tbl_Bookings.EventID"
Jun 27 '16 #29
cool! how would i then get the -1 and 0 to be Yes or No i tried
"yes" instead of true and "No" as false but that threw up errors.
Jun 27 '16 #30
Seth Schrock
2,965 Expert 2GB
I'm guessing that the problem was the use of the double quotes, so try replacing True with 'Yes' and False with 'No'. Note the use of single quotes instead of the double quotes.
Jun 27 '16 #31
amazing thank you that's sorted it. Thanks for all the help!

just to sort the start on Monday now!
Jun 28 '16 #32
just another quick one related to the query you constructed. How would I add in an extra field for example security

As every time I try and add another in it blacks it all?
Jun 29 '16 #33
Seth Schrock
2,965 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time], Security " & _
  3.         ", tbl_Bookings.Contact, IIF(Count(CateringRequests.EventID)>0, True, False) As Catering " & _
  4.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  5.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  6.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  7.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact " & _
  8.         "ORDER BY tbl_Bookings.EventID"
Note the Security field at the end of the second line.
Jun 29 '16 #34
that is what i thought, but every time i put something in it all goes blank???

is it something with the block that shows the data?
Jun 29 '16 #35
Seth Schrock
2,965 Expert 2GB
Follow the instructions in post #27 and make sure that the query works.
Jun 29 '16 #36
getting a pop up saying
'you tried to execute a query that does not include the specified expression 'Security' as part of an aggregate function'
Jun 29 '16 #37
Seth Schrock
2,965 Expert 2GB
Ah, I forgot that it is an aggregate query.
Expand|Select|Wrap|Line Numbers
  1. strSQL2 = "SELECT tbl_Bookings.EventID, tbl_Bookings.EventTitle As [Course Title], tbl_Bookings.Status " & _
  2.         ", tbl_Bookings.StartTime As [Start Time], tbl_Bookings.EndTime As [End Time], Security " & _
  3.         ", tbl_Bookings.Contact, IIF(Count(CateringRequests.EventID)>0, True, False) As Catering " & _
  4.         "FROM tbl_Bookings LEFT JOIN CateringRequests " & _
  5.         "ON tbl_Bookings.EventID = CateringRequests.EventID " & _
  6.         "WHERE MeetingDate = " & ctlDayBlock.Tag & " " & _
  7.         "GROUP BY tbl_Bookings.EventID, EventTitle, Status, StartTime, EndTime, Contact, Security " & _
  8.         "ORDER BY tbl_Bookings.EventID"
Note that I have now added it to the end of the GROUP BY line as well.
Jun 29 '16 #38
found the group thing withing the query but couldn't work out how to get it into the strSQL2

again thank you so much for the help!
Jun 29 '16 #39

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Philip Mette | last post by:
I have been searching many postings and I cant seem to find anyone that has this answer so I decided to post. I am using SQL (Transact-SQL).If I have 2 tables with columns...
3
by: Chris Morton | last post by:
Not sure if this is the right location for this question. I Saw several previous FP questions in this Group and did not see one for VFP.... If this is the wrong group, please direct me...
5
by: PAUL | last post by:
Hello, I have 2 tables with a relationship set up in the dataset with vb ..net. I add a new record to the parent table then edit an existing child record to have the new parent ID. However when I...
4
by: Farraige | last post by:
Hi I need your help... I am implementing the method that updates given table (table is represented as list of lists of strings) according to other table (some kind of merging)... This method...
0
by: quantumlady | last post by:
Hi, All. I am working on a project with another student that will be used on Oracle, MySWL and DB2 databases. We have completed all the basic functions, but I would like to see if we can improve...
1
by: MLH | last post by:
If the RecordSource for subform SF on main form MF is a query and I change one of the field values in the subform control from 75 to say - 13 and click on another record, the value in the...
1
by: JimDavie | last post by:
Okay guys, I'm struggling with understanding how to properly update a table with values from a temp table I created from an APPEND query which provides totals information off of an applicaion...
23
by: SusanK4305 | last post by:
I have a DB table that is linked to my new DB. I can't edited the linked table. So I created another DB w/ additional data however I still need to pull data over from one to the other. My DB has 2...
6
by: neelsfer | last post by:
I do race timing. I capture race numbers in a subform, a sequential lapnumber is created, and the racenumber and finishtime are appended in realtime to a specific lap number fields in another...
2
by: MyDanes | last post by:
update PHYS_COUNT_TAG set COUNT_QTY= (SELECT qty from MC_PART_LOCATION where MC_PART_LOCATION.part_id = PHYS_COUNT_TAG.PART_ID and MC_PART_LOCATION.location_id = PHYS_COUNT_TAG.LOCATION_ID) THE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.