Referencing multiple records in a table

[akbigcat86]

I have a table with two fields: Drawing and File Used

The Drawing field has many duplicates because each drawing uses many files. There are no duplicate drawing/file used combinations, however.

I am trying to write a code that will start off with a single drawing number and enter it into a temporary table. It will then add the files used by that drawing into that same temporary table in the same field as the original drawing number. I am not sure of the best way to do this, but is there any way to use DCount and then reference each duplicate record's File Used field?

For example, if a drawing number comes up 4 times in the main table with 4 different files used, can I pull out the names of those 4 files used?



Thanks in advance!

[akbigcat86]

I should probably be more specific.

The table has an additional field called Type. The value can either be "part" or "assembly".

Of the files used that are paired with the drawing number, I only want the ones that are an "assembly" to be placed in the temporary table. I think I will be able to figure that out once I get how to choose each duplicate record to check.

Thanks!

[jzwp11]

You don't need code. You just need a query. But, your table structure needs a little work. Since a drawing can have many associated files, it describes a one-to-many relationship. The appropriate table structure for that relationship would be as follows:


tblDrawings
-pkDrawingID primary key, autonumber field
-txtDrawingName


tblDrawingFiles
-pkDrawFileID primary key, autonumber field
-fkDrawingID foreign key to tblDrawings (long number data type field)
-txtFileName

Does the type field you mention refer to the drawing or to the files? If it describes the drawing, then it goes in tblDrawings. If it refers to the file, then it goes in tblDrawingFiles

You would use a query to pull out the information you need. There is no reason to duplicate the same information in a temp table.

[akbigcat86]

My database is a lot more complex than just this table. It involves a few different types of hierarchies that don't have a constant number of levels, which makes laying the information out pretty difficult. In order to get other subdatasheets and forms to work properly, I found that repeating the data in this table was a simple (but not the most efficient) fix.

Changing the table structure for this data would require a lot of tweaking on the other parts of the database. I want to use this code to make a query that will define the contents of a report that lays out all of the assemblies and their components. Is there any way to just repeat the necessary data that I need through code instead of creating a new table structure?

[akbigcat86]

Also, the type field refers to the file used. thanks!

[jzwp11]

You're welcome. Let us know if you have any questions on creating the query.

[akbigcat86]

Do you know of a way to do this through code though? At this point, I am willing to sacrifice efficiency if it means I don't have to alter the entire database design. Unfortunately, time is of the essence for this project.

[jzwp11]

Even with your current design, you still should be able to pull the info with a query. Writing a query will be easier than code.


SELECT drawingfield, filefield
FROM yourtablename
WHERE type="assembly"

You can then base a report on the query (once you save the query), then you can set up a group within the report to make the data easier to read.

I would still recommend the redesign of the table structure to make things easier in the future.

[akbigcat86]

The problem with that is I need just the assemblies under a single drawing number to be in a query/table that a report can be made from. That's why I was thinking that writing code is the only option besides restructuring everything. Each drawing number is an assembly itself which is made of additional parts and sub-assemblies. I would like the report to list all of the assemblies and sub-assemblies that are associated with that single drawing number. A query may still be possible, but I don't really get where to start.

[jzwp11]

It sounds like you may not be familiar with setting up a query; am I correct (my apologies if I'm wrong)? In Access 2007, click create--->query then select the table that has your data. In the lower part of the grid, select the fields you want to see in your query results. For the criteria row of the type field put in "assembly". Run the query and see if you get the data you want. If so, save the query. Highlight the query in the navigation pane and then go to create-->report (use the report wizard). It should step you through whether you want grouping levels--you will need a group for the drawing.

If this does not work to your liking, can you post your database (with any sensitive data removed) and an example of what you want the report to look like? And I will give it a shot.

[akbigcat86]

I know the basics on how to create queries with criteria and sorting, but not much more advanced than that. What I would effectively like to do is create a table or query (whichever is easier) that lists in a single column the drawing number (which is chosen through a list box in a form) and the file numbers of the sub-assemblies that create that top level drawing. I don't see how a query could combine those columns into a single column which is what I need in order for the report to work (the report has a subreport that lists the actual parts that make up these subassemblies, but the subassemblies need to be defined first). That is why I figured I could write a loop that would check to see if the files used by the drawing are assemblies and, if they are, add them to a temporary table used to make the report.

Sorry if this is getting confusing. Access isn't designed to handle hierarchies, but it would be really useful if I could get this to work.

[jzwp11]

It sounds like you have an MRP (Bill of Materials) type application. Although Access doesn't have the hierarchical capabilities as some RDMS, some people have had success creating MRP systems. (See this post from another Access site). As to your immediate issue, since you want to put the drawing and the files in the same field, then yes, a query cannot do that with your present table structure.

In code, you will still need to utilize a query to get the file names relative to a drawing and filter to determine which are assemblies.

How do you distinguish a drawing from its related files that are assemblies if the drawing also has a type=assembly or is the type field blank for the drawing?

A series of queries can accummulate the data into a field in a new temp table. To facilitate running the queries, then code can be used to execute them in sequence. I've attached an example database. Open the form and select a drawing and the click the button. The tblAssemblyList is populated with the main drawing and its related files. I assumed that the type field of the main drawing was Null.

[akbigcat86]

That is exactly what I am trying to do. My table names are long and descriptive (I made them this way because there are dozens of different types of files) so I am running into syntax problems. I changed your code to work for my tables:

mySQL = "INSERT INTO assemtemp ( Drawing )"
mySQL = mySQL & " SELECT [AirFrame Files Used by Assemblies].[File Used] "
mySQL = mySQL & " FROM [AirFrame Files Used by Assemblies] "
mySQL = mySQL & " WHERE ((([AirFrame Files Used by Assemblies].[File Used]) Is Not Null) AND (([AirFrame Files Used by Assemblies].drawing)='" & Me.topdrawing & "') AND (([AirFrame Files Used by Assemblies].[Part/Assembly])='Assembly'))"

I am getting an error message saying that the table cannot be found. "AirFrame Files Used by Assemblies" is the exact name of the table though. Am I formatting this wrong?

[akbigcat86]

I got it to work. Simple mistake.

Thank you so much for helping me out! This is really great!

[jzwp11]

Glad I was able to help.