Duplicating records

[Hope]

I have a query where I want to type in the part number and the begin and end dates and have that information appear.

In part number I have
[form]![frmTest]![Test_Part]

and in Date I have
Between [forms]![frmTest]![Test_Date].[startdate] And [forms]![frmTest]![Test_Date].[enddate]

It is duplicating my records and not pulling all of them, any help here what be great.

 

[KenHigg]

Between #[forms]![frmTest]![Test_Date].[startdate]# And #[forms]![frmTest]![Test_Date].[enddate]#

???
ken

 

[Hope]

yeah I orginally tried that too, but its still duplicates the records????

 

[KenHigg]

I'm not sure what you mean when you say it duplicates records?

 

[Hope]

In my table I have for example part number 123, type B, wgt5 and
part number 123,type C, wgt2

when I do the query it says part number 123, Type B, wgt5
part number 123, Type C, wgt 2
part number 123, Type B, wgt5
part number 123, Type C, wgt2

 

[KenHigg]

Can you post a db with a sampling of the data in a table?

 

[KenHigg]

I'm guessing the query you included is the one you're having problems with?

 

[GaryPanic]

I cannot open you file

anyway
how many tables are in your qry ??
I think you will find its the join between the tabels that is causing the problem

in your tables what common fields are their from table 1 to table 2

( if there are more than 1 common items - a quick cheat is to link both common items inthe two tables) - this sometimes sorts the problme out - if you have only 1 common item then you need to look at your qry set up

.

 

[GaryPanic]

I'l leave you in Kens hands -

regards
gp

 

[KenHigg]

It may mean 'No' hope

 

[KenHigg]

This has nothing to do with your criteria causing the records to duplicate. Those records are in the data set when you remove the criteria. It only appears to be duplicating the first several columns because you have a one to many relationship defined. If you have one record on the one side and four records on the many side with the same key field, you will get the data from the one side four times... Hope this makes sense.


ken

 

[Hope]

I think I know what your talking about but I need the one to four because of the information I need to pull over for my report? is there a way around this?

See I need all the information on the test table to pull in, plus the Part type which comes from the form to pull in, and thats why I have the one to four.

 

[KenHigg]

Sorry to drag this out but I'm not quite on board with what the actual data is supposed to represent so I not sure how the joins should be defined. If you'll humor me for a few minutes maybe we can figure this out...

So you run tests on parts. In tblTest one record represents one test. A 'Test' is performed on a specific part number. In tblTest this is the 'Test_Part' field. Also in tblTest you have a test code which I assume means which kind of test you are running. And you have a 'Test_Oper' which is the person doing the test, the test date and the 'Test_Press' which I assume is a particular piece of equipment you need to perform the test. Is all of this correct so far?

ken

 

[Hope]

Yes, correct as far

 

[KenHigg]

So in tblWeight, one record represents a specific part. So one test in tblTest has more than one actual part that was tested? So 'Weigh_Part_ID' in 'Weig_Part_ID' would be like a part serial number?

Or are you testing the same physical part more than once?

 

[KenHigg]

Sure. So now when you ask to see tests on a specific test for a given date range, if you want to see the results for multiple test locations on each part you will get what appears to be duplicate rows from the tblTest side. The only way to see one row per 'test' is to remove the test locations results table (tblWeight) form the query.

I think the best drill is to make sure you understand whats going on in the results before you ever try to put the criteria in...


ken

 

[Hope]

Thank you so much for your time and help Ken that corrected it, your right I had one too many tables involved.

Thanks again and have a great day

 

[KenHigg]

Don't leave if you didn't get your question answered - Maybe someone else can help. Here's a sample of the same thing (I think)


ken

 

[Hope]

Ken I got your Db and I understand that part and I believe thats what I have, the problem is I need everything in the test table which is in the query and the other thing I need to to link that to the weight table so I can pull in the density (by creating a formula in the report, and I need the Part_type in the other table, still trying, when I took the weight table out yes it stopped dupicalating but then I realized I cant pull my other info.

 

[KenHigg]

So you need all your test results on one row for a given 'test'. The only other thing I can think of to do is something like the databse in post #7 here:

Link


ken