Where Used report

[KurtL]

First off i apologize if something like what im about to ask has been discussed arleady, im not sure what exactly im looking for to know what to search.

I would like to make a "where used" report. Im starting with a query of my tables that has a column titled "Part Number" that lists abotu 500 different part numbers. Then there are abtou 20 more column that are the different places these part numbers could have been used. These 20 columns are yes/no columns. I want to have a report that Displays the Part Number, where its being used and the total. The only thing i can figure out is how to make it look like a spreadsheet that says yes in the cell where a yes(-1) was present in the query or a blank cell where a no(0) was present in the query. To me this approach leaves much to be desired as there is A LOT of wasted space.

Please help this Access newbie!

 

[grnzbra]

You would want 3 tables. One would be a parts table with part number, description and anything else that pertains to the part itself.
Another would be a PlacesPartsAreUsed table. It would contain an ID for each place a part could be used. Finally, you would have a PartsAreUsedHere table that would have a field for part number and another field for place ID and perhaps some other things that relate to that particular part being used in that particular place. This would make what you are trying to do more manageable.

 

[KurtL]

Makes sense, but another questing arises. So whenever i add a new part i not only have to add it and its information to the parts table but i also have to list it in the PartsAreUsedHere Table? So is there an easy way to do this? Say some kinda of macro or something that pops up and all the information is entered and placed in the correct table? I've never done any database stuff before, im jsut an electrical engineer.

 

[grnzbra]

For a new part, you would add it to the Parts table and the PartsUsedHere table, unless you also added a new place to add parts.
I'll give you an example that I did a few years ago.

I had a series of reports, which had various parameters. I needed to create a parameter form that would display only fields for the parameters applicable to that form

I had three tables: Reports, Parameters and ReportParameters.

When the report opened, the OnOpen event opened the form and fed the form the report name ( Name() function). The form would look up the report's name in the Reports table and get its ID. It would then look up the ID in the ReportParameters table and get all the parameters that went with that report (including where the fields were to be placed on the form, parameter name displayed in the field label, is it a required field and other such info. It would then use that recordsets information to display the fields.

So I had a Report table. When I added a new report, it went into that table. Its parameters were added as records in the ReportParameters table if they already existed in the Parameters table. If they did not, they had to be added to the Parameters table as well.

In your case, you could add them to the Parts table. Then create a PlacesUsed table and add each of the 20 places used to that table.

There may be a simpler way to do this, but you could create an append query that selected the part number for the first field and a place used number (id from the PlacesUsed table) and enter it into the second field as a hard number. Then set the criteria to True for one of the PlacesUsed fields in the current table (corresponds to the PlacesUsed you entered in the second field). Run the query. Repeat the process after changing the PlacesUsed Id and the criteria until you have gone through all the PlacesUsed fields

 

[KurtL]

Heres the problem... I dont know how to do any of that. And im worried about the data entry aspect. Right now i have over 500components and growing as well as 20 assemblies and grown. Worst case that means i would have 100,000 entries in my whereused table. That scares me a bit, ok a lot a bit.

 

[grnzbra]

100,000? Is that all. It only sounds like a lot because of what we think of what we could do if it was money in the bank. For Access, it's chump change.

You already have these as records in the table with part number and place used fields.

Create a make table query with just the part number and description and whatever else goes only with the part (having nothing to do with where it is used). Run the query. It will make the parts table for you.

Create a table and manually enter the locations in the Locations field. Add other fields if appropriate. You may wish to have an autonumber field for the primary key of this table.

Let's say your current parts table has the following fields:

Part Number, Radios, Skateboards, Fuel Injectors, Computers

Your locations table would look like:

1 Radios
2 Skateboards
3 Fuel Injectors
4 Computers

Create a LocationsUsed table consisting of a PartNumber field and a Location field and any other fields that would apply to that combination of parts and locations (perhaps quantity used in that location)

Now create an append query with the table number from your current parts table for the first field and the following for the second field:

LocationUsed: 1

Add the LocationUsed field that corresponds to the ID = 1 (Radios) in the Locations table. Set its criteria to True (or -1 which is Access version of true).

Run the query

Now modify the query to

Location: 2

Get rid of the current criteria field and bring in the one that corresponds to 2 (Skateboards). Set its criteria to True. Run it again.

Repeat for Location 3, 4, etc.

If you have 20 locations, you will go through this process 20 times and you will have all 100,000 records you need.

Remember, you are making all new tables, so if you mess it up, you can always delete them and start over.

 

[KurtL]

heres the problem. Fields that i have created indicating where the parts are used are yes/no. example:

Part number - 001003021 is used in assemblies 378, and 379. The check boxes under those assemblies are checked the other fields 376 through 411 are left unchecked.

After looking into what an append query does, i dont think that will work for the way i have this set up.

 

[grnzbra]

The SELECT statement for the query would be something like
"SELECT PartNo, '378' AS PlaceUsed
FROM Parts
WHERE 378 = True"

True may have to be -1 since that is the same as True in Access.

Now, change it to an Append Query.

Run it

Then change 378 in the first line and 378 in the last line to 379 and run it again.

Repeat for all 20 or so Yes/No columns.

To do it graphically,

Open a new query and bring in your parts table.

Drag the Parts Number field into the first column.

In the second column type in Place Used: 378
( you might need quotes around the 378)

Then drag the 378 field to the third column.
Set the criteria for the third column to True (-1)

Go to the query type button and select Append query.

Tell it the table name. If the field names from the table don't automatically appear (they won't for the second column and shouldn't for the third - it's a two column table), type in the name.

Run the query.

In the second column, change the 378 to 379 and delete the third column.

Drag the 379 field to the third column and set its criteria to True.

Run it.

Repeat for all the True/False fields of your current parts table.

Go for it! If you mess it up, just start again. You are not modifying your current table.

 

[KurtL]

Wow didnt know i could do this haha. So say tomorrow i add a handful of new components. Would i have to do the same thing as above?

 

[grnzbra]

In other words, how do you get a two column table to display the way that the original 20 column table appeared?

You could use a crosstab querry. That would give you part numbers down the side and location numbers across the top. You will need a third number. Start out with a straight select query with Part Number, LocationUsed, ThirdField:1

Then create a crosstab query. You will have 1s wherever there is a part number-location intersection.

 

[KurtL]

Ok im not sure about the options that are available when i select crosstab

 

[KurtL]

My end result i want something like with 3 columns:

Part No
Assemblies
Total

Will a crosstab do this? It sounds a lot like what i have now where i have a column for every assembly and there is a Yes where the part number is in the assembly, looks like a crossword puzzle kinda. A LOT of useless information on it and hard to read.

 

[grnzbra]

What does "Total" represent. The number of assemblies that use a particular part? Total Price of parts?

The way you have it set up (Parts and Assemblies), the Total will always be 1 unless you have another field that counts the number of parts in a particular assembly. How would you know that from your current parts table unless you can have duplicate records. If you do, no problem, just run the queries as I described and you will add the field Total, each field of which will have the value 1. Then run an aggregate query grouping on part and assembly and sum the Total.

 

[KurtL]

I want Total to be the total of how many different assemblies that part appears in. I do have dupilcate records in that where used table that you helped me make using the append query (turned out to be only abotu 850 entries, so no where close to worst case).

Basically my where used table now has 2 fields. Field 1 in the part number (with duplicates) and field 2 is the assembly number. I hope i added that field in correctly. In the append query when i was looking up say assembly 378 i had that number placed in field 2 with the append (i used another table with the assembly numbers but im thinking i probably didnt need to do that.)

Im not asking for something thats not possible am i?

 

[grnzbra]

Im not asking for something thats not possible am i?

Not at all. You're almost there.

Now create a query with both fields.

Go up to the top and click on the sigma button and select group by for the part number and count for the locations.

That will give you a list of parts and a count of the number of places it is used.

 

[KurtL]

Ok looks like im almost there. Now i have a query that displays the part numbers, and how many different assemblies its used in. Now would i get it to show me which assemblies each part is in, and in just one field per entry?

 

[grnzbra]

Set up a similar query with three fields; PartNumber, Assembly, Count:1
You are forcing a 1 in the third column.

Then set up a new query and use the Crosstab query wizard. It will walk you through the process of creating a display in the format you want.