Create query to create records for an object based on quantity field

[Thomas Van Peteghem]

Hi all,

I am making a database to manage IOs for a PLC configuration. For this purpose I have a table called tblIO_cards which lists all the IO cards I have, has a short text field [Card Tag] containing the unique identifier of the card, and refers to another table tblIO_cardTypes which contains the specifications of each type of IO card, including a number field [IO Count] which indicates how many IOs each card can contain. For example one type of IO card can hold 16 IOs.

Next I have a table IOs where I describe all the IOs and provide details of what is connected to each IO slot on an IO card. In this table there is a relationship field to link each IO to an IO card, and a number field [IO Number] to define on which position on the card the IO is connected. Another field contains the [Variable Name] which is linked to another table where I map the addressing.

However, not every slot in an IO card is used, there are spare slots. I would like to create a query that creates a record for each IO slot in each IO card based on the [IO Count] field, and takes the [Variable Name] from tblIOs for slots which are already assigned, and for slots which are not assigned to an IO, I would like to fill the text "Spare".

For example, if I have an IO card of a certain type that can hold 16 IOs, and I have assigned 11 IOs to it in positions 1,2,3,4,5,6,7,8,12,13,14 in tblIOs, I would like the query to generate 16 records containing the slots in the card numbered from 1 to 16, with a field [Variable name], in which for slots 1,2,3,4,5,6,7,8,12,13,14 it takes the variable name from tblIOs and for slots 9,10,11,15,16 it fills "spare" in the variable name field.



At the moment I am manually creating dummy records in the tblIOs for all slots in a card and assigning the details to the IOs which are used and leaving the others blank, but that's a labour intensive job prone to failure and if I have to add or remove a card.

The reason why I need also the spare slots is because I have created a report that will show an IO overview of each card including spares to indicate cabling, and I want that report to also show spare IOs.

Would appreciate your support.

[Welshgasman]

I would probably create the list with them all set to Spare, then amend with whatever the field you are using.

[Thomas Van Peteghem]

I would probably create the list with them all set to Spare, then amend with whatever the field you are using.

That's exactly what I do not want to do, because then I need to create many placeholder records which I want to avoid because it's a labour intensive job to do whenever I want to make a new configuration to assign dummy IOs to all cards. If the database knows a card has 16 slots, how can I create a query that creates records numbered 1-16 for that card?

[Welshgasman]

You have a table that stores details about the card and how many slots it can have.
Then when you select a card, the system generates as many records as the number of slots.

[Thomas Van Peteghem]

You have a table that stores details about the card and how many slots it can have.
Then when you select a card, the system generates as many records as the number of slots.

Yes that's what I want to have, but the information about how many slots it can have is a single field with a number e.g. 16, how do I get the database to convert that into 16 records?

[Gicu]

You could do it in VBA in a loop or you can create a table holding the maximum number of records you want to add for the various card types.
Here is a similar link with more info:
https://stackoverflow.com/questions/...xt-box-number\

Cheers,

[ssanfu]

Do yourself a favor and remove all spaces from object names - Fields, Table, Form, Query, Report names.

Bad names	Good/Better Names
[Card Tag]	[CardTag]
[IO Count]	[IO_Count]
[IO Number]	[IO_Number]
[Variable Name]	[VariableName]

[Thomas Van Peteghem]

You could do it in VBA in a loop or you can create a table holding the maximum number of records you want to add for the various card types.
Here is a similar link with more info:
https://stackoverflow.com/questions/...xt-box-number\

Cheers,

I don't want to insert records into the existing table, I want to achieve a second query result with all unmatched records and assign dummy values to those and then make a union query to merge them into one. So I created a table with 16 numbers assigned to different types of IO cards and have managed to write a query qry_IO_Slots_Total that generates a record for each IO, so far so good. In that query I've generated a calculated text field [IO_Slot_Tag] making a unique ID for each IO slot. Then I've created a query qry_IO_Slots_Assigned to make the same calculated text field [IO_Slot_Tag] (I need a query because the information to make the unique ID is in other tables). Then I've created a relationship between both queries on the [IO_Slot_Tag] field and want to create a find unmatched query to select all IO slot tags not appearing in the qry_IO_Slots_Assigned. Below the sql code, but it is not generating any results. Is it not possible to run find unmatched queries on queries?

SELECT qry_IO_Slots_Total.IO_Slot_Tag
FROM qry_IO_Slots_Total INNER JOIN qry_IO_Slots_Assigned ON qry_IO_Slots_Total.IO_Slot_Tag = qry_IO_Slots_Assigned.IO_Slot_Tag
WHERE qry_IO_Slots_Assigned.IO_Slot_Tag Is Null;

[Gicu]

You need a Left (Outer) join, not an equijoin:
SELECT qry_IO_Slots_Total.IO_Slot_Tag
FROM qry_IO_Slots_Total LEFT OUTER JOIN qry_IO_Slots_Assigned ON qry_IO_Slots_Total.IO_Slot_Tag = qry_IO_Slots_Assigned.IO_Slot_Tag
WHERE qry_IO_Slots_Assigned.IO_Slot_Tag Is Null;

https://www.w3schools.com/sql/sql_join_left.asp