Cannot get this query to work properly

[Dave 14867]

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

View attachment Query issue-Progress.zip

View attachment Query Issue Spreadsheet.zip

 

[isladogs]

I assume you have discussed this in an earlier post but without that link the context isn't clear.

My initial response was to say you need to set the query properties as
unique values =yes. Or in query SQL use SELECT DISTINCT (which does the same)

However looking at your db and spreadsheet you are making the common error of creating a database as though it was a spreadsheet with lots of similarly named fields (excel columns)

Please read up on normalisation on this site or Google it.
Then redesign your tables.
Or use Excel

 

[Dave 14867]

Collin,

Yes, I posted my original issue on another forum and the table had redundant fields, so with some guidance, I was able to determine a way to take those out so that each field is now unique. I have this posted here now also to try to get as many different possible solutions as possible as I know there is always more than one way to get the same results.

I will try set the query properties as you suggest and try again.


Editted - Collin I tried to set the properties to unique but it was not available so I believe there is something wrong with the way the query is set up. The data in the tables looks correct and shows up correctly on the form.

Thanks.

Dave

 

[isladogs]

In your case setting the query to unique values won't work. You need to restructure your db tables

If your table design is based on advice you were given, it wasn't good advice or perhaps you misunderstood

 

[Dave 14867]

Collin,

Could you please give some more direction and reason why.

The table represents the materials that go into making up a particular component. This is just 1 of about 25 in the actual database, all others are working fine, this particular one had the possibility for up to 6 instances of 1 component that could have been used so I did take those out and put them into a separate table as suggested.

Each field is unique just like an street address and zip code are unique or a first name and last name.

Thanks

Dave

 

[plog]

You have broken 2 big rules. 1. Using values as table/field names and 2. Having tables with the same structure.

1. Table and field names should be very generic, you have named yours after specific items. Someone unfamiliar with your business should be able to look at your table names and field names and know the generic data that goes into each.

When you use 'tblM13472' and 'tblM22368 as table names you have stored a value ('M13472' and 'M22368') improperly. Both those values should be in the table itself as values in a field.

2. Tables with the same structure means the same thing. Instead of 2 tables where the name differentiates them, all that data should go intoa new table with the same structure and a new column to accomodate the value of what you are storing in the table name.

Since I don't know your data, let's use a car analogy. If you sold Volkswagens, Toyotas and Audis you wouldn't have 3 similar structure tables with each named after the type of car. Instead you would have 1 table with all the fields you need, plus an additional one to hold who the manufacturer is. That 1 table now holds all your data and allows you to differentiate between different cars. That's what you should do...


However, after all that it just explains your multiple tables and their naming. The same principle applies to your fields. M16115LotNum2 as a field name is doing the same thing. You've jammed 2 pieces of data into the name--whatever M16115 represents and the Lot Number value. Improper. When you do this with field names it requires a whole new sub-table to hold that data.

Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization)

 

[arnelgp]

examine query arnelgpQuery, the layout is based in excel file.

 

[Pat Hartman]

Dave,
Try again at normalization. This time, try to abstract more. If you find yourself creating tables or columns with data as their names, stop.

I can't really even tell what this application is supposed to do. I only recognize a couple of words such as "Beer" and perhaps you are tracking "lots". If that is what you are trying to do, let us know so we can give you some schema ideas.

 

[Dave 14867]

arnelgp,

At a quick glance,That looks like exactly what I was trying to get. Can you explain how you did it please so in the future I won't have the same problem.

Thanks

Dave

 

[Dave 14867]

Pat,

I appreciate the extra guidance, I use the naming convention because it reflects the documentation I am getting the data from, so it's familiar and I recognize it. This table could just have easily been named BeerCard, which is really its name, the Doc # it's M22368 that is why it is named as it is. It is made up of the other Components listed in the table, each field is unique, don't let the naming confuse you. I believe it is the same as a CustomerAddress table, having a Street, Town, State, ZipCode, etc., except in my case I am using the names of components. That is why I have a hard time understanding why folks think it can be broken down further, I really believe it already is. Every "BeerCard" has to have all of the items in the table to be complete (Then it becomes it's own "LotNumber" of "BeerCards" which will then be called out in another Document.

I think it is just the names that is making it confusing, or look like it isn't broken down far enough.

These are a Component used to test Beer, you are right about that. The M13472 is a sub-component. as is each other field.

Thanks

Dave

 

[isladogs]

Hi Dave

Sorry I didn't respond to your question back in post #5.
I was away from my computer all day.
However I'm pleased to see you've had expert help from a number of my colleagues so I will leave you in their charge.

Good luck with your project and try and keep off the 'beer'

 

[Dave 14867]

arnelgp,

It looks like you used the original table which had the redundant fields, is that correct?
How did you get the correct output, when I had them in there I wasn't getting the correct output, it would just give me the 1st lot from the first field.

I see in the query that each of those fields has was looks like a query in the Field name, can you please explain how you did this? Did you just type it into that Field Name section.

Ridders,

No problem at all, we all have lives that we must go on with, I am just extremely appreciative for the assistance you folks provide when needed.

Thanks to all who commented and pointed me in the right direction.

Thanks

Dave

 

[MarkK]

...and try and keep off the 'beer'

I don't see this as 'good advice.' My 2c is the beer is 'OK,' and should be consumed intermittently in moderate quantities, with or without salty snacks.
hth
Mark

 

[arnelgp]

ijust lookup the value from corresponding table with linenum, and used the values from the base table whenever it is availablr. see the query in design view. you may use similat methid in the future..

 

[isladogs]

I don't see this as 'good advice.' My 2c is the beer is 'OK,' and should be consumed intermittently in moderate quantities, with or without salty snacks.
hth
Mark

LOL.
I was of course referring to the 'BeerCards' mentioned in post #10 ... rather than suggesting a change of lifestyle!!