Crosstab like data entry (1 Viewer)

[jepoysaipan]

Hi Guys,

I wanted to seek the help of the Guru's with regards to the problem I am dealing at present.

I am facing this difficult problem so far, I need to create a form which required the following entry:

PC Model Score
AXD125 34
MRT665 12
HTG768 45
...
UYT 45

This is what I have so far:
PC Model has its own database with all the records, now the user needs to input on a form only the score of a specific PC.

I am now still at the table design of the main form that will handle data entry and caught up with this problem, I don't know If I am over doing this project or is it just me.

I am really lost on how to deal with this requirement, I need your help guys.

Thanks.
Jeff

 

[Pat Hartman]

I don't get where crosstab fits in here. You need a search feature to bring up the PC record so you can enter a score for it.

 

[jepoysaipan]

Hi Pat,

Thanks for the quick response, pls. see the attached form design.

 

[Banana]

Looks to me that what you want is a continuous form or a datasheet.... If you search Access help on continuous form, it'll give you some information on how to implement one.

 

[jepoysaipan]

Hi Banana,

I thought of doing the datasheet, but it turnsout that the user still have to choose from the drop-down list before typing the score, (that is what my design leads me) but if you have a different approach it will be appreciated.

Another option I have in mind is to create a set of records which has the all the Evaluation Category and the user simply fills-out the Score & Remarks, but don't have that ability to create at present.

Thanks.
Jeff

 

[Banana]

Actually for either continuous form or datasheet, you would place a combobox for the PC Model, create a query pulling in all PC Model needing to be evaluated and use it as combobox's rowsource so user can then select from the list and enter the evaluation data.

 

[khawar]

Is it the main table in which all items are stored and you just want to edit the score or it is a transaction table where you have to pick pcs from your main table and store in transaction table more than once and want all items to be pre filled for user and he has to enter just the scores more than once

 

[jepoysaipan]

Hi Khawar,

Exactly what I needed, I want the form to be pre-filled upon opening and fill the datasheet or beeter yet a listbox and then the user simply fills-out the score & remarks depending on the chosen PC Model.

Sorry It's been hard for me to explain things when your in deep sh$t, pardon my wordings but it seems it get into my nerves, really I am loving Access before this problem and slowly work my way out because of this forum, but now agauin & again I'm totally confused & lost at the same time.

Thanks a lot guys for all your time, hope I could be at par with all your knowledge, again I appreciate everything & tahnking you all in advance!

Jeff

 

[stopher]

Just following on from what Khawar has asked, what is your table structure? Is all your data in one table or do you have PCModels in one table and scores in another? Do you record more than one score for each model?

Is this example any help? Take a look at frmScoreEntry. On the left you can click on a model. Then on the right, the record for that model appears for editing.

The form is just a regular form with the record source as the table. The selection box on the left is just another form (subfrmPCModelList) placed as a subform but with no linking between the form and subform. This form has PCModelID as a hidden field. Also, there is an OnClick event on the PCModel field. When a record is clicked on, some code runs. The code applies a filter to the main form according to which record is clicked on.

I’m not sure if this is what you want but try it and see if it gives you some ideas.

I’m not really sure why you wouldn’t just list all the fields in a continuous form and edit as if it were a spreadsheet though.

Hth
Chris

 

[jepoysaipan]

Hi Cris,

Your'e on target!, wow it seems that it can be created, anyways I am playing around it and works good.

But upon adding a combo box to sort out the scores on a given day, I can't seem to do it.

What I want is the user clicks on a button "NEW" to create a new set of scores on a given date, also the user can select from a combo box to review some scores based on the date it was created.

Hope I explain my case well.

Thanks again for all your help, and hope to get this new problem fixed soon.

Cheers!!!!!

Jeff

 

[khawar]

So you need scores for each date in that case you have to creat a transaction table where the entries can be saved by date and thereafter can be retrieved
This was what I asked in my previous post which you didnt reply properly

 

[jepoysaipan]

Hi khawar,

Sorry for not responding properly on your question, your were certainly right, I do need a transaction table that handles all the scores & remarks on every PC Model based on the date it was created.

I was playing with Cris's DB and having a difficulty implementing what I had in mind.

Regards,

Jeff

 

[stopher]

Hi khawar,

Sorry for not responding properly on your question, your were certainly right, I do need a transaction table that handles all the scores & remarks on every PC Model based on the date it was created.

I was playing with Cris's DB and having a difficulty implementing what I had in mind.

Regards,

Jeff

The trick is to get your database design (table structure) sorted out before you worry about the interface. The example I gave could be implemented in any table design.

As Khawar says, I think you need two tables if you are doing multiple scores for each pc.

Chris

 

[jepoysaipan]

Hi Chris & Khawar,

So far I have this form, can you pls. evaluate what is missing or if the logic behind it is terrible?

Hoping for your recommendation

Jeff

 

[jepoysaipan]

Hi Guys,

The problem with my design it wont register the date & the evaluation title.

Jeff

 

[khawar]

because they are not bound fields you have to use some code to do it or use a master detail structure save date and evaluation in one table and scores in other

 

[jepoysaipan]

Hi Khawar,

Sorry I don't have any idea about Master Detail Structure, I was trying to capture the content of txtEvalDate & txtEvalTitle on the main form (it is on the control source on the subform sbfrmEval).

How do I programatically add the date and the title on the frmEvaluation on the EvalTransaction Table?

Thanks for your time.

 

[khawar]

Have a look at orders and orders detail structure in northwind sample database
also you dont have to save date again in the detail table you can combine them in query check query orderdetailextended in northwind sample

 

[jepoysaipan]

Hi Khawar & Cris,

I solved partially the problem, I put on the "Default Value" of the Field "MyForms!MySubform!txtDate" for the date and so on, works like magic.

But I have one more problem left, how do I get the Average of the Scores on my Continous Form?

Thanks.