UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cannot Figure Out How To Properly Configure This Query, Access 2010    
 
   
Dave14867
post Nov 29 2017, 07:47 PM
Post#1



Posts: 44
Joined: 28-November 16



Hello all,

I have a problem with a particular query. I have 6 possible instances of the same PN being used in a particular table, they represent the possibility of up to 6 different lots of the same PN. When I try to put this into a Query, I don't get the expected results, I get just 1 lot shown in all 6 fields when this cannot the case ( most only have 2 or 3 lots ). I have attached a file which i had to create and hopefully it works the same as the entire file as the original is too large to post, which it does appear to.

If you look at tblM22368 you see there are 16 entries, the M13472 feilds (6 of them) are the concern. As you can see there are a max of 4 of the 6 possibles that are populated, most are 1 or 2. When the query is ran, all of the M13472 fields are populate with just 1 lot number and only 10 records appear.

I am sure it has to do with the relationship and I don't know what to do to correct the issue.

Can someone please help.

Thanks

Dave

Attached File  Query_issue1.zip ( 33.98K )Number of downloads: 4
Go to the top of the page
 
RJD
post Nov 29 2017, 08:56 PM
Post#2


UtterAccess VIP
Posts: 7,833
Joined: 25-October 10
From: Gulf South USA


Hi Dave: Your table designs are confusing. You are using a "spreadsheet" design in the primary table (tblM22368) with what looks like data content as field names. Plus you haven't told us what the query output should actually look like. In the query you are calling for the same field six times. What was your intent there?

I changed the joins, assuming tblM22368 was the main table in the query (perhaps incorrectly), and used left joins from that table to the other two in the query. The output still didn't make any sense to me, but it did produce 16 rows of output.

First, please tell us what you are trying to do, with an example of the output you are trying to produce. Maybe that will help us understand. Then, please explain, in general business terms, what you are trying to model with your tables. Perhaps, with this information, we can point you in the right direction.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Dave14867
post Nov 29 2017, 09:24 PM
Post#3



Posts: 44
Joined: 28-November 16



RJD,

The tables may look like spreadsheets as I use excel a lot and I am just starting to use Access instead. The tables represent different products using various "Lots" of material which are either 4xxxxx numbers or 8xxxxx numbers. You are correct that the M22368 table is the Main table I am trying to get the report to output correctly using the query. Each M22368 Formulation can use up to 6 different "lots" of M13472 parts that is why there are multiple fields each labeled uniquely (M13472Lot1, M13472Lot2, etc). I want the report essentially to output the contents of the M22368 table only with the actual lot numbers of the parts and not the ID's as listed in the M22368 table. With the data entered in the M22368 table so far, the max fields that should be reported out for M13472 is 4 but as you can see in the original all 6 are being reported out and all with the same lot number which is incorrect, each record can only have 1 instance of a particular lot number of M13472.

I hope that explains it well enough, if not let me know and I'll try to better explain.

Other queries in the actual database are working fine, I am sure what is causing the issue is the multiple instances of M13472Lot? but I don't know why when the original table has the correct info in it.

Thanks
Dave
Go to the top of the page
 
RJD
post Nov 30 2017, 12:39 AM
Post#4


UtterAccess VIP
Posts: 7,833
Joined: 25-October 10
From: Gulf South USA


Hi Dave: Actually, I think we could see what you are trying to do if you could show the results you want rather than trying to explain it. You know what you want and why you put the data together as you did, but we do not know these things. Use an Excel spreadsheet and show us what the query result should actually look like - with all the data. Zip and attach that to a post. Then we might be able to back into the query design that will do that, given your tables and data.

But I remain very concerned about your table design. Spreadsheets tend to be wide and not tall. Access tables, however, when properly designed, tend to be narrow and tall, and not have "repeating fields" as you have. The design I see in your tables is no doubt the source of your troubles with this query. I don't know the impact of changing your table designs on everything else in your db, since we cannot see that, but the design certainly is suspect.

Looking forward to seeing the spreadsheet with the desired query result.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Dave14867
post Nov 30 2017, 04:43 AM
Post#5



Posts: 44
Joined: 28-November 16



Joe,
Attached is the spreadsheet as you requested, I let off the DHR Rev and DOM since that isn't important for this issue. This is what I expect from the query. It Looks like the current query is picking just 1 Lot of M13472 and using that in all fields.

Today I am going to try to write a query for each field individually and see what the results are, I should also note that I do not know SQL show it will take me some time to get that done. Essentially I will use the query builder to build several different queries, evaluate the results, then try to cut and paste the SQL statements together properly if the results look good.

Thanks for assisting
Dave

Attached File  Query_Issue_Spreadsheet.zip ( 7.22K )Number of downloads: 2
Go to the top of the page
 
GroverParkGeorge
post Nov 30 2017, 07:11 AM
Post#6


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Step one, as Joe suggested, really should be to correct the "spreadsheet" table design problem.

I understand, it's what you've grown accustomed to in Excel. That's fine, as far as it goes, but Access is a relational database and that calls for a different approach.

You can start here.

I can't really do much with suggested alternatives since the field names are cryptic and I don't know what business process is being modeled.

--------------------
Go to the top of the page
 
Dave14867
post Nov 30 2017, 08:33 AM
Post#7



Posts: 44
Joined: 28-November 16



I am at somewhat of a loss in understanding the issue with the table, let me try to explain further. The table represents the contents of a manufactured item, this item can contain up to 6 different lots of 1 part, or as few as 1 lot of that part, all of which have to be tracked and traceable. If instead of M13472Lot1, M13472Lot2, etc, they were called ABC, 123, would you still see an issue? The repeat column names are just that, names, they actual data contained in the filed is unique. How else could it be handled?

Thanks
Dave
Go to the top of the page
 
GroverParkGeorge
post Nov 30 2017, 09:10 AM
Post#8


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


I strongly urge that you step back, invest some time in learning how Relational Databases work, and see if you can not revise these tables accordingly. I understand the content; it's the STRUCTURE of that content which is not set up properly.

Start with the articles in the Newcomers Reading List I linked to previously.

You have, basically, imported some spreadsheets. That's not sufficient with a relational database. It's a fundamental thing that you need to come to grips with. I know that is harsh and I'm sorry for that. But further progress is going to depend on it.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Nov 30 2017, 09:41 AM
Post#9


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


While it possible to work with tables of this sort in Access, it is always more problematic. In fact, the reason we're even having this conversation about how to get a query to work is directly related to that table design problem.

Here's a story that was created many years ago for a somewhat different context, but I think it might help shed some light on the problem we're facing.

A farmer drove his Cadillac into a custom body shop. He parked and went inside. He asked the owner of the body shop how much it would cost to customize his Cadillac to haul pigs. The body shop owner was surprised. "Why would you want to do THAT?" "Well", said the farmer, "That's what I have and I figure I can use it for double purposes. I'll haul the pigs to town and sell them at the market, and then haul groceries back home." So, the body shop owner took in the Cadillac, removed the back seat and put down heavy-duty floor mats.

The problem, of course, is that the farmer was still hauling pigs in the back of a Cadillac and groceries in his pig-hauling vehicle.

The morale of the story is that you can do all kinds of things, but some of them make no sense.

Please invest some time in learning how to take best advantage of Access for your purposes.

--------------------
Go to the top of the page
 
RJD
post Nov 30 2017, 10:12 AM
Post#10


UtterAccess VIP
Posts: 7,833
Joined: 25-October 10
From: Gulf South USA


After studying your db, and then the spreadsheet of the results you want from your query, I agree with George that you should step back and completely rethink your design. The way you have set things up, I seriously doubt you will ever be able to get the output you want. I just couldn't see a way to link the records as you want in the designs you present.

Your use of repeating fields and putting data (Lot numbers, for example) as field names pretty well defeats good db design and your ability to manipulate data through proper queries to produce the output you want.

Sorry we couldn't be of more help, but sometimes you do need to step back and rethink things, as George emphasized.

Not knowing your business process and what, overall, you are trying to achieve with your db, makes it impossible for us to suggest an appropriate design at this point.

Best of luck with the readings George recommended, and with the redesign. If you reach a point in the redesign where you have another question, there will always be someone here to look at it.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Dave14867
post Dec 1 2017, 09:15 PM
Post#11



Posts: 44
Joined: 28-November 16



OK I have taken out the redundant fields and created a new table for that data and a link table. I also modified the form (frm22368) to use a subform and the data seems to go into the respective tables correctly. I still cannot figure out how to get a query to work the way I want it. I have attached the Database and a spreadsheet of what I would like to see from the query. qryM22368-2 Query1 is close but has repeated rows for the M22368 Lot Numbers and M13472LotNum's are showing the ID, not the Actual Lot Numbers of that ID as I would like.

I have also attached a spreadsheet of what I would like the result to be, I then want to create a report based on the query.

Thanks in Advance for any assistance you can provide.

Dave

Attached File  Query_Issue_Spreadsheet.zip ( 7.22K )Number of downloads: 1

Attached File  Query_issue_Progress.zip ( 129.81K )Number of downloads: 2
Go to the top of the page
 
RJD
post Dec 2 2017, 11:54 AM
Post#12


UtterAccess VIP
Posts: 7,833
Joined: 25-October 10
From: Gulf South USA


Dave, it looks like you are still on the wrong design track. You are still storing content information in field names and in table names, and this is a bad design, leading to the kind of difficulties you are now facing.

To show you how you might reconsider your design, look at the new table I designed for you (tblNewDesign). This may still not be exactly right, and certainly not complete, because we don't really know your business purpose and processes and what all you are trying to accomplish with the db. But it might guide you in the right direction.

Again, you have some studying and work to do to get your db in a condition to support your requirements.

HTH
Joe
Attached File(s)
Attached File  Query_issue_Progress_Rev1.zip ( 73.13K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Dec 2 2017, 12:03 PM
Post#13


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Joe is right.

As I've said before, you need to refocus AWAY from FIELDS in tables and onto RECORDS in Fields in tables.


--------------------
Go to the top of the page
 
Dave14867
post Dec 2 2017, 06:47 PM
Post#14



Posts: 44
Joined: 28-November 16



Joe,

What about the rest of the content in the table that is extremely relevant to each record in the original table like the M16115LotNum, in the current records in the table, all use the same lot number, which is the content of each record for that field, but Monday, that lot number may be used up and they have to start using a different lot number, how is that handled?

Also you have mistaken the M13472Lot1ID as a qty, in reality it is the M13472ID number from the tblM13472, which has a Lot Number associated with it. I don't know what the "Lot" field in your table relates to, forgive me.

Thanks

Dave
Go to the top of the page
 
MadPiet
post Dec 2 2017, 10:10 PM
Post#15



Posts: 2,264
Joined: 27-February 09



Maybe if you started over and described what the database is supposed to do, we could help you. What things are you storing information about? How are those entities/things related to the other things in your database? Then you have Nouns, which usually become the subject of tables, and verbs, which relate the nouns to each other. (Person buys Product, for example).

Without that, I don't think anybody can make head or tail of what you're doing. I sure can't.
Go to the top of the page
 
GroverParkGeorge
post Dec 3 2017, 09:26 AM
Post#16


UA Admin
Posts: 31,234
Joined: 20-June 02
From: Newcastle, WA


Let me see if I can summarize.

Your company produces and distributes (sells ?) kits.

These kits are assembled from one or more components.

Some components are used in more than one kit.

Some components are assembled from other components.

Some components can be sub-components in one or more other components.



You get shipments of components from your suppliers.

Each shipment comes to you as a numbered, or otherwise identified, "lot".

Lots are groups of identical components that must be tracked by which component you put them in. For legal reasons you need to be able to say, for example, "components received in Lot A111, on December 3, 2017, went into kits produced on December 4th, 2017. The serial numbers of those kits are: "Kit1000", "Kitt1001", and so on.

Any of that need to be modified?

If that's a reasonably accurate description, then I would call this a fairly typical Bill of Materials (BOM) type of database. This is a pretty well known kind of application, and there are a lot of good examples of that available.

So, what I would say -- and this is, in part -- a repeat of our private discussion, you need a table for Kits.

tblKit is a list, basically of each type of Kit you produce, by name. It can include both your internal designation, e.g. "KitABC", and the brand name under which it is distributed, "Best Kit for Medical Condition ABC".

Next you need a table of Components.

tblComponent is also basically a list of all of the components you use. It can include the designator, such as "M124721", and any details pertinent to components, such as "Type", where you would record whether it is a consumable type of component, for example, or a casing or housing for other components, and so on.

You need a table of Component assemblies, for lack of a better term.

tblComponentIncludedComponent is a Junction table. It contains two (or more fields). These two mandatory fields are the Foreign Keys from tblcomponent. One identifies the Including Component, the other identifies the Included Component. This junction table allows you to build up a set of records to indicate which components can be built up from others. In fact, any given component can appear in this junction table as both an Including Component and as an Included Component. This creates a hierarchical structure for components.

With this table structure you NEVER add new tables as new components are developed. You NEVER add new fields to existing tables. You add new records to the appropriate tables.

Tracking lots is a separate, but related task. It involves additional tables to identify incoming lots by Component (Foreign Key here, not component name), by supplier, by date received, quantity in the lot and other pertinent details.
It then requires another junction table to track those components by which ones were used in which other places, as components within others, or as components directly within a kit.

===

You also need a table in which you track each individual component. Here you have at least two fields, possibly others, but these two for sure. One is the Foreign Key from the component table, indicating the type of component received. The other is the Foreign Key from the Incoming Shipment table, indicating which lot that came from.

Again, this may be subject to further refinement, but that's basically how this kind of BOM application needs to go.
This post has been edited by GroverParkGeorge: Dec 3 2017, 09:53 AM

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 06:09 AM