Results 1 to 4 of 4
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Joining Records in a single rreport

    I have a table that is imported from a fixed-width flat file that is exported from an IBM AS400 system. (Attached) The table contains work orders (WO). Some orders are a single record, and some are 3 records, with the certain fields changed for different information. For example, one might have a single instruction field, and the others may have 3 instruction fields, but with the same WO number.



    EXAMPLE 1: ID 2 is a WO with a single record
    EXAMPLE 2: IDs 3, 4 , and 5 have the same information and WO number, except for different in fields PZLineType and PZDate

    I am trying to generate a WO/report for each WO number that fills in the PZLineType and PZDate for that order, regardless of whether it is a 1 line or 3 lines.

    I am confused on how to differentiate the repeating fields. That is how to have the first PZLineType and PZDate in a pair of text boxes, then the second PZLineType and PZDate in another pair of text boxes, etc.

    Should I query and combine to a temp table, then run the report from the temp? I am not sure what the syntax would be for this. I know it needs a JOIN, but not sure about how to combine the 3 records into 1 with numerated fields (PZLineType1 and PZDate1, PZLineType2 and PZDate2, etc.).

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am confused on how to differentiate the repeating fields.
    I have to say the data makes no sense to me but it looks to me that linetype is telling you what type of value is in the PZDate field (which itself seems a confusing name, given the contents). So my interpretation is that you need to populate a table with fields for WO plus all the different linetypes (there are 9 of them in your test data). Once you have done this you then need to write a number of queries to populate that table with each of the values in the PZDate column. Note that Date, Name and Year are all reserved words, so you should take the opportunity to rename them to something more meaningful (e.g. WODate, WOName, WOYear)

    PZLineType PZDate
    DATE 4-20-80
    DATE 9-10-19
    FONTCHOICE OSTRICHSAN
    INITIAL H
    LINE2 Established 2014
    NAME Hughes
    NAME Hughes Family
    NAME Mary & James
    NAME Noah James
    NAME Nora Riley
    NAME1 Emily
    NAME1 Mary
    NAME2 James
    NAME2 Pete
    NOPERS NOPERS
    YEAR 2014
    YEAR 2015

    Edit: You could use a crosstab query - select rowheading for all columns you want except PZlinetype and PZDate, select column heading for PZLineType and Value for PZDate for which also change the group by to first, last, max or min

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    There is a PZDataSeqNo field that contains each line number for a particular work order. I am wondering if there is a way where I can use that for populating the report. Any ideas? Would it be possible to set the control source for each PZLineType and PZDate text box as a query that selects these fields based on the WO number and then PZDataSeqNo, getting the WO number from the WONo textbox? Or does it not refresh that way?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no idea, it's your data. I have no idea what is supposed to represent or what you are trying to achieve - you did see I updated my post?

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

Similar Threads

  1. Records in s single row
    By Leonel in forum Access
    Replies: 10
    Last Post: 12-11-2017, 01:26 PM
  2. Replies: 4
    Last Post: 12-18-2013, 06:52 PM
  3. Linking Mutiple Records to Single Records
    By LukeJ Innov in forum Access
    Replies: 3
    Last Post: 04-23-2013, 08:59 AM
  4. Replies: 4
    Last Post: 04-03-2013, 08:49 PM
  5. Mutiple records into a single record
    By lwhatford in forum Database Design
    Replies: 3
    Last Post: 02-14-2012, 11:32 AM

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