MS Access Query help please

Caporegime
Joined
9 May 2004
Posts
28,551
Location
Leafy outskirts of London
I'm updating an old query to make it more useful, but have hit a stumbling block.

There is a single table holding bucketloads of data (Sales Service History)

I have a normal query that brings back all the BookingIDs when the booking was confirmed and on what date.

There is then an update query that previously was used to update a blank field (Ext Tool New Business Transaction) in the original Sales Service History table with the text "Confirmed".

This meant that any instance of that BookingID would have "Confirmed" in that field.

I'm trying to make this more useful by not excluding any activity that takes place after the date the booking was confirmed, as anything before confirmation is considered Sales activity, and any post is Service activity.

I have my new query as such:



What I need it to do is update the Ext Tool New Business Transaction field to be whatever the Activity Date is in the Qry Bookings to BK results.

I can't figure out how to do this, it only allows me to enter a blanket text to be updated on all BookingIDs, rather than a dynamic update based on the Activity Date in the Qry results.

Any ideas?

Thanks
 
Associate
Joined
14 Mar 2007
Posts
1,659
Location
Winchester
I'm updating an old query to make it more useful, but have hit a stumbling block.

There is a single table holding bucketloads of data (Sales Service History)

I have a normal query that brings back all the BookingIDs when the booking was confirmed and on what date.

There is then an update query that previously was used to update a blank field (Ext Tool New Business Transaction) in the original Sales Service History table with the text "Confirmed".

This meant that any instance of that BookingID would have "Confirmed" in that field.

I'm trying to make this more useful by not excluding any activity that takes place after the date the booking was confirmed, as anything before confirmation is considered Sales activity, and any post is Service activity.

I have my new query as such:



What I need it to do is update the Ext Tool New Business Transaction field to be whatever the Activity Date is in the Qry Bookings to BK results.

I can't figure out how to do this, it only allows me to enter a blanket text to be updated on all BookingIDs, rather than a dynamic update based on the Activity Date in the Qry results.

Any ideas?

Thanks

As ever with these things really difficult to know exactly what you want but I think you can do what you need. You need to have your the query updating your new business filed running off the actual query bookings query. If you are only comfortable using the design builder then when you have the option to add table etc when you start building the update query select the add query tab then select the qry bookings query instead.

It's pretty difficult to know what to do without knowing your table structures, As it is probably easier to just give you a SQL statement. From a technical point of view you need to create a correlated sub query.
 
Last edited:
Caporegime
OP
Joined
9 May 2004
Posts
28,551
Location
Leafy outskirts of London
Thanks, but I'm still struggling.

I'll try and explain in a clearer fashion.

I have a huge table with loads of activity data.

I currently have a query that creates a list of all the bookings that have been confirmed, and what date that happened on.

I then have an update query that would update a blank field in the original large table to 'Confirmed' for all the booking ids that match the first query. So each row of data in the main table will either have 'Confirmed' or be blank in that field, denoting if a booking was confirmed that week.

I need to change it so that rather than just "Confirmed" being updated to the table, that the actual activity date from the first query results is used, so now all the records will either be blank, or contain the date at which they were confirmed.

TL;DR

Old update query:
If [Sales Service History].[Booking ID] exists in [Qry Bookings to BK], update [Sales Service History].[Ext Tool New Business Transaction] to "Confirmed"

What I need the new query to do:
If [Sales Service History].[Booking ID] exists in [Qry Bookings to BK], update [Sales Service History].[Ext Tool New Business Transaction] to [Qry Bookings to BK].[Activity Date]

Now from what I understand, I can't use an actual Update Query to do the above, but need to use an Append Query?

Sorry, haven't used Access since 2001, haha.
 
Back
Top Bottom