Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Easy report question

    I need a text box in a field to say what badge number to use. I have two tables for badges. I have the field with Visitor badge, and another with the number. Example: the record shows "Visitor Badge" and "1" The other table is Temp Worker Badge and badge number 1234. Now in my main table I have a field that tells if the badge is a Visitor Badge or Worker badge.

    I run a report which tells the secretary what badge to give. I need access to assign the number of whichever badge type they need. I can't figure it out.

    Any help would be very nice!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure I fully understand but there are multiple ways to do it.

    One way is to design a form where the user selects the badge number and then on the report, have a field which has a sourceobject of =Forms!MyBadgeForm!MyBadgeNumber.

    But your question needs a little more explanation. Keep in mind that you can also use the =Dlookup("[BadgeNumber]","MyTableName","[RecordID] = " & Reports!MyReportName!RecordID & "")

    ie...you could always do something like this (in the sourceobject of a field on your report)....

    =iif(isnull(Dlookup("[WorkerBadgeNumber]","MyMainTable","[RecordID] = " & Reports!MyReportName!RecordID & "")),"1234",Dlookup("[WorkerBadgeNumber]","MyMainTable","[RecordID] = " & Reports!MyReportName!RecordID & "")

    (Note: in the above, you're basically just looking to see if the WorkerBadgeNumber (not the type of Badge) has a value in the main table. If it does, then return that to the report, otherwise, it just puts in 1234. - ie. No need to determine what type of badge it is.) Otherwise you can replace "MyMainTable" above with the table that holds your WorkerBadgeNumbers and RecordID is possibly the WorkerID stored in that table (or whatever number type field determines who the worker is.)

    Unless there are multiple badge numbers for a specific worker, I'd just store the WorkerBadgeNumber in the main table that holds the other worker information (no need to complex it by having another separate table with 1 Badge Number for each worker.) Unless there's a specific reason, you normally want to hold 1-1 type information (ie. there's not multiple records for 1 specific record), in the same table as your main table.
    Last edited by pkstormy; 06-23-09 at 21:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •