Results 1 to 5 of 5
  1. #1
    Skarvion is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    13

    Combining query and define types

    Hi, sorry if the title is confusing, I don't know what to call this problem.

    I'm using a query that use 2 tables that are related, 1 table is the item for renting and the other table is for transaction that shows who rent what and the period.
    Now this query is already fine enough on its own.

    The problem is that I want a query that shows all of the item available for rent no matter the status and it has its own field that shows the status of each. And it shows that if there's no one renting it, it shows a certain text status. I assume that I should combine the previous mentioned query with an empty query that has empty fields to match it. So how to make these empty fields that has the same data types? Or is there a better way to achieve my goal?

    The aformentioned table that contains record of item for renting has no status field as I want to keep it simple and instead the status field is in the transaction table. Is it okay for the database design as well?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    still unclear. The item for rent should have a field , say ClientID. If null, the item is available, otherwise it belongs to the client.
    so items available are all rentals.clientid = null

    The transaction table i guess is the date/client/amt rented. But i dont understand what you want from the 2 joined.

  3. #3
    Skarvion is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Quote Originally Posted by ranman256 View Post
    still unclear. The item for rent should have a field , say ClientID. If null, the item is available, otherwise it belongs to the client.
    so items available are all rentals.clientid = null

    The transaction table i guess is the date/client/amt rented. But i dont understand what you want from the 2 joined.
    Hi, it's really hard for me to express the problem here. So I have made a sample database with all the relevant table and the query. Hopefully it will give you better idea what the problem is. I tried using left join but it doesn't give the intended result and union give duplicates of the record

    https://dl.dropboxusercontent.com/u/39216186/Test.accdb
    Sorry for using external service to upload, for some reason the attachment pop up is blank and so I can't use it

    Thank you for your reply.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First let me say that I would change the design of the "Booking" table. It is designed on the order of a spreadsheet.
    Lets say John wanted to rent a backhoe next Tuesday for 3 days. So you create a record and set the status to Booked.
    Where do you enter the date booked: the Checked Out field or the Checked in field?
    I would have one date field: "EntryDate".

    So at the end, there would be 3 records:
    STATUS________Entry Date
    ----------------------------
    Booked ________ 2/2/2016
    Checked Out____ 2/9/2016
    Checked In______2/12/2016


    I also added a table for statuses. Easier than calculating in the query. And easier if you wanted to add "Out of service" and "Returned to service".

    Look at Query2. It is based on Query1. (Catchy names - yes??? )
    Is that what you want?
    Attached Files Attached Files

  5. #5
    Skarvion is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Quote Originally Posted by ssanfu View Post
    First let me say that I would change the design of the "Booking" table. It is designed on the order of a spreadsheet.
    Lets say John wanted to rent a backhoe next Tuesday for 3 days. So you create a record and set the status to Booked.
    Where do you enter the date booked: the Checked Out field or the Checked in field?
    I would have one date field: "EntryDate".

    So at the end, there would be 3 records:
    STATUS________Entry Date
    ----------------------------
    Booked ________ 2/2/2016
    Checked Out____ 2/9/2016
    Checked In______2/12/2016


    I also added a table for statuses. Easier than calculating in the query. And easier if you wanted to add "Out of service" and "Returned to service".

    Look at Query2. It is based on Query1. (Catchy names - yes??? )
    Is that what you want?
    Wow, this exactly what I wanted. I had searched google to find the solution. Yours is much simpler and more flexible than I had mine imagined. So much better than expectation. Thanks a lot for your help. I will keep in mind with your suggestion.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to define field types in a make table
    By accessmatt in forum Queries
    Replies: 3
    Last Post: 07-09-2015, 11:54 AM
  2. Pre-Define query based on input data
    By twildt in forum Access
    Replies: 1
    Last Post: 04-21-2014, 08:36 AM
  3. Replies: 1
    Last Post: 03-07-2013, 03:21 PM
  4. Replies: 7
    Last Post: 10-25-2011, 08:32 PM
  5. Replies: 6
    Last Post: 07-22-2010, 05:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums