SELECT Statement - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SELECT Statement

Michelle Maughan

3 years ago

Hi
I have a form with a subdorm that is working well.
I need to have a report to display this. I have created the report and sub report as per Richard's Expert 9, but i need a query to display =Forms!BookingF!BookingID.
When i created this for i pulled in fields from all different tables and didn't make a query.
The record source looks like this:

SELECT BookingT.BookingID, BookingT.BookingDate, BookingT.IsPaid, BookingT.CancelledCredit, BookingT.PropertyID, BookingT.PropertyName, BookingT.ArrivalDate, BookingT.DepartureDate, BookingT.ArrivalTime, BookingT.EntityID, BookingT.LastName, BookingT.FirstName, EntityT.EntityID AS EntityID_EntityT, PropertyT.PropertyID AS PropertyID_PropertyT, EntityT.FirstName AS FirstName_EntityT, EntityT.LastName AS LastName_EntityT, PropertyT.PropertyName AS PropertyName_PropertyT, BookingT.Notes, BookingT.Accomodation, BookingT.Bikes, BookingT.DOGS, BookingT.PaymentDetails, PropertyT.MinimumBooking, BookingT.CostPerPerson, BookingT.TotalCost, BookingT.MaxCustomerCount, BookingT.CalendarSent, BookingT.MapSent, BookingT.[UpdateProperty], BookingT.[ResendMap], BookingT.DateCalendarSent, BookingT.DateMapSent, BookingT.PropertyXAccomID FROM PropertyT INNER JOIN (EntityT INNER JOIN BookingT ON EntityT.EntityID = BookingT.EntityID) ON PropertyT.PropertyID = BookingT.PropertyID;

Happy to pay someone to help me make this into a query that i can use. I just don't have the time to watch lessons any more and frankly not smart enough to work it out.

Cheers Michelle

John Davy @Reply

3 years ago

Hi Michelle,

It looks like you are working with 4 tables - is that correct? If you can explain clearly in plain English exactly what you are trying to do, I will see if I can help you. Are you trying to create a report for 1 client based on the information in the above select statement? When you say I am trying to create a query to display =Forms!BookingF!BookingID. - I don't know what that means.
Is the database small enough to send to me? I do not want personal data, so you can fill the database with workable, dummy data. [email protected]

Michelle Maughan OP @Reply

3 years ago

Hi John
I did ask for this post to be taken down. I wrote it after consuming a bottle of wine, not very smart.
I could still use some help though if you are interested.
I am trying to open a report that has a subreport, from the open form that has a subform, trying to display certain info from the Form and the subform.
I have fixed my tables up so there is no longer duplicates.
I have created a report with a sub report in it.
The control source for the Form is no longer the above post but a query with =Forms!BookingF!BookingID in the criteria, there is a button on the form to open the specific report BUT it locks up the data base when I press the button. I do have nearly 19,000 records in my booking table. I have compact & repaired and downloaded updates. It seems to have a bug now where the ribbon flashes black when i try to select a new button. Too scared now to run in case i corrupt my database and lose all my business records. If you think you can help let me know. Cheers Michelle

John Davy @Reply

3 years ago

Hi Michelle,
From my experience, I have found that large, complex queries often cause problems, so I follow Rick's advise. Create simple queries, even id it takes 2, 3 or 4. Access is so fast you will not see any decrease in speed. In fact, it may be faster. After, making a backup (copy) of you database and securing it, see if you can make one simple, clean query that does 1 part of what you want. Then, use that query in the next query.
Yesterday, I had a report to do that involved Customers, Orders, OrderDetails and Payments. I did NOT make one query. Instead, I made a query to total each Payment by OrderID. Then, I made a query to total orders by Orderid and included the CustomerID. It was then very easy to create a query where I used each of my other queries to pull together what I needed for my report which was every order, totalamount and paymentamount as well as calculating the BalanceDue.
Try it and let me know how it goes. John

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View