Create multiple records from one entry (1 Viewer)

lord_claude

New member
Local time
Today, 13:28
Joined
Sep 28, 2020
Messages
4
Hi
This is my first post. I could not see another post regarding this and I hope I am posting in the right place :)

When I enter a record into my DB, I would like Access to create 5 other records that have two different fields of information in, one from a different table (Product_Colour) and one from no table (the "product code") which is externally generated. At the moment I am manually copying the record and changing the relevant fields.

Background
I am creating a multi coloured logo that has to have one of it's colours changed depending on the colour of the background (the black in the logo will need to change to white if the garment is black, otherwise you wont see the black parts of the logo) and the DB need to reflect this as I send the designs to my embroidery machine from Access.

I generate a product code externally in Excel and manually input this, but the suffix letter of this code is a letter denoting the colour e.g. 123456w for white, 123456b for black etc. (I'm sure Access could generate this code, but I will create another thread for that).

So to sum up, when I input my first record I will need Access to create 5 additional records, and to keep all information the same in the additional 5 records except for;
  1. a change to the field Product_Colour taken from the 6 records in the Product_Colour table,
  2. a change to the last digit in the Product_Code field which I currently manually input (there are only 6 colours and I have a table with the suffixes in). All product codes are the same length
I hope this all makes sense.
I'm quite new to Access but quite happy just to be pointed in the right direction.
Many thanks
 

arnelgp

error reading drive A:
Local time
Today, 21:28
Joined
May 7, 2009
Messages
10,844
on 2., what is the table name? does it contains all 6 suffixes? how about the color?

you can just create an Unbound form with a button that will add all 6 records using SQL Insert.
 

lord_claude

New member
Local time
Today, 13:28
Joined
Sep 28, 2020
Messages
4
Hi arnelgp
Thanks for the reply.
I have a table called Product_Colour that has 2 fields;
Colour (colour of the garment Black, White etc)
Letter_Used_in_Product_Code (as it says, the suffix; b,w etc)
At the moment, Letter_Used_in _Product_Code is purely a reference for me and is not used by any other table.

... as for Unbound forms - I have done a little googling and I think I have bitten off more than I can chew, and I have zero knowledge of SQL.

I may have to have a re-think about how it all works as I may be making life difficult for myself. Moving forward I now believe I'm looking at this from the wrong angle. I may be better off getting Access to generate the product code, instead of me generating it on Excel then manually copying across concatenated number into Access (it seemed like a good idea at the time due to my lack of Access knowledge). That way I could get Access to generate all iterations of the database (I think 87,000 possibilities) and all I would need to do then is add my logo file to the already present record... Of course this presents me with other technical and knowledge issues, but the result will be a complete database with no gaps.
Does this sound sensible?
 

plog

Banishment Pending
Local time
Today, 07:28
Joined
May 11, 2011
Messages
10,126
My mind can't get itself around what you are trying to do, so let me give some general advice--get your tables correct first and foremost. Your posts makes it seem that is what you are trying to do, so I think you are on the right track.

My specific suggestion is to layout the tables you think you need along with their fields. Then either complete the Relationship Tool in Access and post a screenshot back here, or simply upload the database itself so we can see what you have. With that we can offer specific advice and ask better questions to get you where you need to go.
 

lord_claude

New member
Local time
Today, 13:28
Joined
Sep 28, 2020
Messages
4
My mind can't get itself around what you are trying to do, so let me give some general advice--get your tables correct first and foremost. Your posts makes it seem that is what you are trying to do, so I think you are on the right track.

My specific suggestion is to layout the tables you think you need along with their fields. Then either complete the Relationship Tool in Access and post a screenshot back here, or simply upload the database itself so we can see what you have. With that we can offer specific advice and ask better questions to get you where you need to go.

I'm trying to get my head around what I'm trying to do as well... lol
A way to think of it is to imagine you are trying to create a police Photo Fit database. Many tables; gender, height, hair colour, eye colour skin tone etc. I want to produce a record for each of the possible combinations, with a unique product code that is generated from the specific selections e.g. male could be 1 in the product code, female 2. I will create a picture file based on the inputs and and attach the picture file to each corresponding record.
I have created the tables and it all fits together ok, and I can create the entry and add the file. While this works ok, albeit slowly, I want to have as few human interventions as possible. Rather stupidly I have been creating the records based on the pictures I have created, rather than the other way around. I doubt I will ever need all the iterations that are produced, but that is not a problem as Access will created however many thousands in a blink-of-an-eye and I can then produce the pictures based on the requirement from my customers (if I ever get any). In the future I hope to have the/a database produce the "photo fit" for the customer to see (to keep the police photo fit metaphor) as it is entered on line, which will then produce my picture file for me... but I'm so far from that at the mo. The immediate issue that I had was the "picture" colour depends on the "background" colour and there are 6 of them (imagine the photo fit scenario entries for English, Scottish, Welsh, Irish, French, German). Everything else stays the same. I did not want to waste time creating the other 6 entries, just to change 2 records with information that is already somewhere on the database and then produce a unique code for each???
I think all this is fairly moot for now, as for the time being at least I will have to keep things simple and stick to a clunky human interface. Time is not really on my side, and while I love sorting out problems, I probably should start with what I know... Copy and paste, then Edit. You are right though, about laying it all out and looking at the relationships. I thought I knew from the start what I wanted from my database, but as things have progressed I have seen more opportunities and productivity that I could get out of it, if only I had given it even more thought in the beginning... but then again, at what stage do you stop thinking and start doing.
I will be back, I hope, and will update you with my progress.
Thanks for your interest and ideas.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:28
Joined
Feb 19, 2002
Messages
30,059
If you post a database with your table schema and some data, we can look at it and come up with a suggestion. The simplest solution will be an append query that joins to the table with the background colors for this product. We don't need the forms, etc for now but if it is easier, just post the whole thing.
 

Users who are viewing this thread

Top Bottom