Results 1 to 8 of 8
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Is there a way to create a table based on another table?


    This is going to sound strange. I am trying to mimic a process done with a word doc in Access. The word doc has multiple tables. Now the number of rows in each table is dependent on table 1. These tables are all blank accept for standard entries like: KG in one column of one table or Yes/No in one column of another table It looks kind of like this:


    I'm wanting these to be separate tables also. Is it possible to do this? If so, how?Click image for larger version. 

Name:	Screenshot.png 
Views:	24 
Size:	68.9 KB 
ID:	31130

  2. #2
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I'm thinking that maybe since the user will input a specific number in "Item Number" That I can do a query based on that number since they'll all be the same. (i.e. query with criteria base on [item number] make table) I could build 3 queries to make 3 tables? These tables would be temporary tables as they are only spots to fill in on the report that is printed.

  3. #3
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    Not sure what I'm looking at here. My guess is that 02 and 03 are steps in a process and that these would require their own table. This table may also require a field to differentiate the steps for process A from process B so that you can repeat the values 02 and 03. The field for 02/03 would need to be RTF format in order to color text (not sure if Access can replicate the highlighting). What follows 02 or 03 look like tables for observations for a step, but there is no consistency in terms of the number of rows or the names of those fields. Your example doesn't divulge if each table has the same number of fields. Is it coincidental that they both have 6? This can make a big difference as to how to handle these tables. Also not sure if the need is to have a form for data input, or a printed report for filling in by pen.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I'm not sure if it was your post or what but my brain just got to kicking. If I need to create 4 tables with equal rows to table 1 that are empty for filling in by pen why not just create one table with all fields necessary, and insert the table on the report in various spots, hiding the unnecessary fields in every other table. So one table looks like 4 tables.

  5. #5
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    Depending on what the data looks like, you would probably have an un-normalized data structure - possibly with lots of holes in your fields. Doesn't sound good to me.
    Maybe expound a bit more on what you have and are trying to accomplish. Are you looking to create an Access report with a fixed number of rows per group, or one with different row counts per group so that the report can be filled in by pen? If so, is there a reason why you need to do this in Access when you already seem to have it in Word?

  6. #6
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I have a word document that I am trying to recreate in Access. There are 10 steps to a recipe (instructions). 5 out of the 10 steps have tables that have the same number of rows as table 1, but they are blank. Meaning if I write in 5 lines of material on table 1, the other 4 tables will have 5 blank rows to hand write data in when the report is printed. So I think having one table with the fields needed for all 5 tables will work. Hopefully, that is clear enough. The reason for putting this document in Access is to create a searchable database of information associated with the document. We print thousands of the same document and it's a pain to look through that many documents for one piece of information.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are 10 steps to a recipe (instructions). 5 out of the 10 steps have tables that have the same number of rows as table 1, but they are blank. Meaning if I write in 5 lines of material on table 1, the other 4 tables will have 5 blank rows to hand write data in when the report is printed.
    I just ran a test using the following query.
    Assuming the table is named "Table1", save the following query: (could only be 1 field instead of 3)
    Code:
    SELECT " " AS [Item Number], " " AS [Scale ID], " " AS [Quantity Dispersed]
    FROM Table1;
    Count the number of records in "Table1", then open the query. There should be the same number of blank records as the number of records in "Table1".
    Add a few records to "Table1". Rerun the query. Again, there should be the same number of blank records in the query as records in the table.
    So all you need is "Table1" and a query.

    For the report, you could use 4 sub reports that are bound to the same query.
    (There could only be 1 sub report, but added 4 times......since they are blank records)

  8. #8
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    That's another alternative. Thanks for the info Steve!

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

Similar Threads

  1. Replies: 5
    Last Post: 06-25-2017, 01:24 PM
  2. Replies: 2
    Last Post: 10-26-2015, 06:14 AM
  3. Replies: 2
    Last Post: 08-07-2015, 02:11 AM
  4. Replies: 3
    Last Post: 06-04-2015, 09:48 AM
  5. Replies: 2
    Last Post: 11-18-2014, 08:22 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