Adding data to a Query

[Sniper-BoOyA-]

Good Afternoon,

I am currently working on a Access Application to determine the degree of compaction of sand.

The degree of compaction is determined and calculated by putting samples in a Proctor machine.

At this point, the user is able to select the different samples that need to go through the Proctor machine. (on a form)

They simply select the samples by ticking the selection boxes, and in the same table as those boxes theres a ProctorID which has a autonumbering property.

Every tick = ProctorID+1

For example :

I have a total of 10 samples. And i want to use sample 1 and 8.

Sample 1 = ProctorID 1
Sample 8 = ProctorID 2

So far so good.

I gave the user the opportunity to make graphs, etc..

But here's the thing,

Sometimes the employees in the lab have to mix 2,3 samples. Which means a proctor can be made of 2 to 3 samples. Instead of 1.

I was wondering if its possible to select more samples per Proctor, with the ProctorID still intact?

My first thought was, that is not possible, due to the autonumbering. But before i redo the entire thing (2 months of work) i wanted to ask you guys for any suggestions or ideas first before i throw 2 months of work in the trash.

Thanks in advance!

 

[jdraw]

Sounds like there is still some analysis work to do on the business side. What is a Proctor machine test? What is actually tested? I think you need to lay out the "business " requirements in detail, then do the data base design. The Proctor test has nothing to do with autonumbering. The current database/table design involves/uses autonumbering -- that is how the designer set things up. Perhaps some redesign is needed, based on a more detailed look at requirements.

Good luck.

 

[Sniper-BoOyA-]

Yeah, you are probably right. The thing is, someone started making this database in early 2008, but never got to finish it.

The people in the lab are still using Excel to enter the data and eventually make graphs etc.

Ive put that all into Access, and everything works great. But now they asked if it was possible to select multiple samples per Proctor. Which is a machine that hits an X amount of points into the samples and the outcome is used to calculate the compression of sand.

Anyways, like i said, for every monster they select (before they put all the data in) the ProctorID will be ProctorID+1, due to the autonumbering feature.

And all i really want is the opportunity to say,

Ok i hit a proctor on sample 1, but sample 4 is has the same properties, so instead of the proctor being linked to just sample 1, it will be linked to 1 and 4..

OR

Ok, i want to hit a proctor of samples 4,7,9 (mixture of the 3 samples).

After this process, the user has to enter data that refers to the proctor machine, such as the mass of the cilinder used. If they enter the mass, then the max compression of that proctor is calculated. Plus it also calculates the water density. (by simply putting it into an oven for 20-24 hours).

Anyways, i was wondering if it was possible to link multipal samples to one particular proctor without the proctorID being raised by 1.

Ive made a table , which uses a Lookup function to see what ProctorID has been made, plus a record Samlpenr which simply uses a Lookup function to see what samples have been registered within the database.

To make it more easier for the user i made a form where they can say ok, i want to select, ProctorID 2, and Sample#10. So far so good. BUT that doesnt solve my problem, because i have to make a query so it actually updates the original table with all the Proctor information with the data that has been added using the form i just made. And that is where it goes wrong. Because of its Autonumbering property, u cant have duplicate values of Proctor ID. So it either doesnt do anything, or it multiplies the original content of the Proctor Table by 2.

Ive been working on this issue for at least 2 weeks, and its getting on my nerves.

Thanks again!

Ill keep playing with it, and if the person who originally made this database comes in office, ill ask him or some clearification.

 

[gemma-the-husky]

surely, if you have 10 samples then store these in a table etc etc

now, are you saying you mix two or more samples for a test.

In that case does this consititute a completely new sample, or data relating to the original mixed smaples

depending you can either add another sample to your population of 10, or maybe you need a sub-table to manage the mixes you have tried.

its the data analysis that is vital here - presumably you struggle in your spreadsheet as well. Access is far better at modelling complex structures compared with excel

 

[Sniper-BoOyA-]

The spreadsheet they are using is pretty basic. With just 3 tabs, one for input, output and a graph.

About the samples:

I populated the database to the point where there are 10 samples. And sometimes its needed to lets say mix sample 3 and 4. Or when the test is done with just just sample 1, and afterwards that sample 6 has the same properties as sample 1, then it would be nice to link sample 6 to sample 1 which is linked to a ProctorID.

So theres a record Samplenumber and which is currently linked to ProctorID.

This process is started by a popup form where the user can select the samples , needed to perform the test in question.

At this point, the user is only able to select 1 sample for each ProctorID (due to autonumbering). Due to the fact that ProctorID is the primary key, and is pretty much the glue that holds all the tables / forms / sub-forms together, duplicate values are not allowed.

When data is being added to a table it normally looks like this:

ProctorID Samplenr
--------- -----------
01 ---------01
02 ---------08
03 ---------03

As you can see both sample 1 ,8 and 3 were selected.

Now the idea is to get something like this:

ProctorID Samplenr
--------- -----------
01 ---------01
02 ---------08
01 ---------02
02 ---------05
02 ---------03
02 ---------04

But that is not possible due to the fact ProctorID is a primary key and its autonumbering.

The solution that makes the most sense:

ProctorID Samplenr
--------- -----------
01 ---------01,02
02 ---------08,03,04,05

But if im correct, that is not possible either.

 

[Sniper-BoOyA-]

Good Morning,

Ive been playing with different setups this morning, and i just wanted to keep u updated on this issue.

The last thing i tried seems to work so far, well, at least i can add multiple samples to the ProctorID without messing up the Forms/Subforms.

This is what ive done so far:

- Made a copy of the Proctor table
- Opened the copied table in design mode and
* Deleted the PrimaryKey on ProctorID
* Changed its property to Number
* Saved Table
* Added a row on the top, named it ID
* And made it Autonumber aswell as Primary Key
- Made a new table called tblprctr, with just the proctor numbers (1-10)
* And made the ProctorID record in the Proctor table to look in this table.
- Opened the form where the user selects the samples in design mode
* Added a dropdown menu where the user gets to select the ProctorID
number, from tblProctor and it saves the ProctorID in the Proctor Table

Then i checked all the forms / subforms and everything seems to work fine.

Ill keep u updated.