Results 1 to 7 of 7
  1. #1
    WesHarding is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    60

    Continuous form that contains multiple pieces of data from the same table

    Hi Everyone,

    Is it possible to create a continuous form that draws multiple pieces of data from the same table?

    For example:



    A continuous form, that has a single farm owner, and a list of its owners (owners all coming from one contacts table linked to the farm)?

  2. #2
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Yep. you should be able to create a form with a subform. Or you could use a listbox and have that populated with a query that selects all owners for each farm specified.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can you give an example of the data in table and how you want data to look on the form?

  4. #4
    WesHarding is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    60
    As my data is internal I will have to make up example data, but yes of course.

    Farm: Wesley's Farm
    Owners: Wesley Harding Jim Johnson Josh Donaldson
    PID (property identification #) : 583450340, 2389532094, 238532509

    From this, the owners will all source from a contacts table, PID numbers will come from another PID table. All of these are linked properly, I just don't know how to get multiple contacts from the same table to populate in a list (without being a datasheet subform which doesn't work with continuous forms).

    I'm going to try what AccessPower suggested, it seems quite promising!

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So my understanding is you have multiple owner records for a specific farm and on your continuous form, you want all 3 owners to appear on the one Farm record? Is this the issue?

    Is there a max number of Owners for 1 farm or will it always vary? So in your Owners table you have FarmID and OwnerID so in your example would have 3 records in the Owners table for Wesley's Farm? Can you provide your table and fields or the query that links them all?

  6. #6
    WesHarding is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    60
    Yes that is precisely what the scenario is!

    I actually have a number of queries that lead up to a final stacked query, as I have more parameters that are filtered other than owner. These all however work like a charm. I'll send the SQL of the query if that works.

    SELECT tblNAICS.ID, tblNAICS.[2012 NAICS Code], tblNAICS.[2012 NAICS Title], [qry_farm/who_contacts_PID].idPID, [qry_farm/who_contacts_PID].Owner, [qry_farm/who_contacts_PID].PID, [qry_farm/who_contacts_PID].Person1ID, [qry_farm/who_contacts_PID].FirstName1, [qry_farm/who_contacts_PID].LastName1, [qry_farm/who_contacts_PID].AssociatedFarm1, [qry_farm/who_contacts_PID].FarmerID, [qry_farm/who_contacts_PID].Phone1C1, [qry_farm/who_contacts_PID].Phone2C1, [qry_farm/who_contacts_PID].Phone1C2, [qry_farm/who_contacts_PID].Phone1C3, [qry_farm/who_contacts_PID].FarmName, [qry_farm/who_contacts_PID].FarmPhone1, [qry_farm/who_contacts_PID].FarmPhone2, [qry_farm/who_contacts_PID].FarmCivicAddress, [qry_farm/who_contacts_PID].FarmCommunity, [qry_farm/who_contacts_PID].FarmPostalCode, [qry_farm/who_contacts_PID].Province, [qry_farm/who_contacts_PID].Email, [qry_farm/who_contacts_PID].Email, [qry_farm/who_contacts_PID].Website, [qry_farm/who_contacts_PID].Fax, [qry_farm/who_contacts_PID].Facebook, [qry_farm/who_contacts_PID].DateofEntry, [qry_farm/who_contacts_PID].Notes, [qry_farm/who_contacts_PID].RR, [qry_farm/who_contacts_PID].FarmDeatilID, [qry_farm/who_contacts_PID].Commodity, [qry_farm/who_contacts_PID].Quantity, [qry_farm/who_contacts_PID].AreaofSale, [qry_farm/who_contacts_PID].RegBus, [qry_farm/who_contacts_PID].RegFarm, [qry_farm/who_contacts_PID].Export, [qry_farm/who_contacts_PID].NAICS, [qry_farm/who_contacts_PID].ASN, [qry_farm/who_contacts_PID].AssessedValue, [qry_farm/who_contacts_PID].AssessedValue2, [qry_farm/who_contacts_PID].Municipality, [qry_farm/who_contacts_PID].AssessedTAX
    FROM [qry_farm/who_contacts_PID] LEFT JOIN tblNAICS ON [qry_farm/who_contacts_PID].NAICS = tblNAICS.ID
    WHERE ((([qry_farm/who_contacts_PID].PID) Is Null Or ([qry_farm/who_contacts_PID].PID) Like "*" & Forms!MainSearchForm!txtPID & "*") And (([qry_farm/who_contacts_PID].FirstName1) Is Null Or ([qry_farm/who_contacts_PID].FirstName1) Like "*" & Forms!MainSearchForm!txtfirst & "*") And (([qry_farm/who_contacts_PID].LastName1) Is Null Or ([qry_farm/who_contacts_PID].LastName1) Like "*" & Forms!MainSearchForm!txtlast & "*") And (([qry_farm/who_contacts_PID].FarmName) Is Null Or ([qry_farm/who_contacts_PID].FarmName) Like "*" & Forms!MainSearchForm!txtfarm & "*") And (([qry_farm/who_contacts_PID].FarmCivicAddress) Is Null Or ([qry_farm/who_contacts_PID].FarmCivicAddress) Like "*" & Forms!MainSearchForm!txtaddress & "*") And (([qry_farm/who_contacts_PID].FarmCommunity) Is Null Or ([qry_farm/who_contacts_PID].FarmCommunity) Like "*" & Forms!MainSearchForm!txtcommunity & "*") And (([qry_farm/who_contacts_PID].Commodity) Is Null Or ([qry_farm/who_contacts_PID].Commodity) Like "*" & Forms!MainSearchForm!txtcommodity & "*"))
    ORDER BY [qry_farm/who_contacts_PID].PID DESC , [qry_farm/who_contacts_PID].FirstName1 DESC , [qry_farm/who_contacts_PID].LastName1 DESC , [qry_farm/who_contacts_PID].FarmName DESC , [qry_farm/who_contacts_PID].FarmCivicAddress DESC , [qry_farm/who_contacts_PID].FarmCommunity DESC , [qry_farm/who_contacts_PID].Commodity DESC;


    Very messy indeed, my apologies. If you have any questions regarding relationships I have I am happy to give detail, your assistance is greatly appreciated

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Thinking you might have to create a Temp Table to hold the data for the report. Then create VBA code to run through your records and move the data to the temp table so it formats like you want. Then base your report on this temp table: so process would be something like:

    Remove all records from tblTemp
    Read each line in your query and move owners to a single row with Farm in the tblTemp
    Once done in same code, Open your Report (Report is based on tblTemp)

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

Similar Threads

  1. Replies: 4
    Last Post: 03-17-2016, 05:12 AM
  2. Adding multiple pieces of data to one record
    By craig1988 in forum Database Design
    Replies: 3
    Last Post: 07-16-2015, 10:29 AM
  3. Replies: 3
    Last Post: 02-14-2014, 10:46 AM
  4. Multiple filters on a continuous form
    By Ray67 in forum Forms
    Replies: 3
    Last Post: 01-26-2013, 10:57 PM
  5. Replies: 3
    Last Post: 03-09-2012, 09:00 AM

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