Spreadsheet type of data entry form

[warren0127]

Hello,



I would like to generate a form that looks exactly like a crosstab query. Currently, I have a tblBudget that holds ProjectName, FiscalYear, and $Amount.

I'd like to have a spreadsheet type/crosstab view of a data entry form with FiscalYear as the "column headers" and ProjectName on the left as a "row name". I was able to do this with reports using crosstab queries - but I am stuck trying to create a form in this view (it wont let me update it if I create it from a crosstab query, or a handmade crosstab query).

Basically, it would look something like this:

	FY15	FY16	FY17
Project A
Project B
Project C

A user should enter the budget amount for each fiscal year for their project horizontally:

	FY15	FY16	FY17
Project A	10	20
Project B		20	30
Project C	50	30

the tblBudget currently looks like this (how its stored):


Project A FY15 10
Project A FY16 20
Project B FY16 20
Project B FY17 50
Project C FY15 50
Project C FY16 30


The current form I use makes the user enter a project, fiscal year, and amount as a separate RECORD (just like how the table looks), but the Fiscal Years are not spread out horizontally, which would make it easier because it would look like a spreadsheet. I would like the fiscal years spread out horizontally so the user doesn't have to make a separate entry for each fiscal year.

I tried to create a handmade crosstab query, then append to a table, and create a form out of that append table, but I am not quite sure if that will actually work for what I intend to do.

Hope this makes sense - any suggestions would be appreciated.

Thanks
- Warren

[pbaldy]

I haven't used it myself, by try ChrisO's updateable crosstab form here:

http://www.baldyweb.com/ChrisOSamples.htm

[warren0127]

Thanks for the link - I opened the file, but its giving me errors when trying to enable the content / use the interface.

[pbaldy]

I just downloaded and it worked fine for me. Did you unzip before trying to open/use the file?

[warren0127]

I unzipped and seems to work now thanks.

[pbaldy]

No problem. Let me know how it works. Sadly, Chris passed away, and I'm trying to host his samples. Some of them are over my head, but I haven't looked at that one.

[warren0127]

Will do. Sorry to hear about Chris.

This should be plenty enough, alot to grasp for a newbie like myself. Even though I was looking for a more low-level or basic example of how you could create the form that looks like a crosstab for data entry, I will try and digest as much as this as possible. I wish I knew more VBA - seems very complex.

The other alternative I have is to just create Fiscal Year columns in the table, and just treat the FY15 - FY20 columns as amounts. They seem to display fine that way when testing, but I'm not sure if thats a poor database/table build or not. IE: Create 8 columns; FY15, FY16, FY17, etc.. and the data under those columns would be a number amount per Project.

I successfully created cross-tab queries and reports using the current method of using one column for "Fiscal Years", and one column next to it called "Amount". The trick is, how can I make it look like a spreadsheet data entry form, where the user can just enter amounts across each FY, instead of picking a FY from one column and making 10-12 record entries.

[pbaldy]

Personally I would not have each year as a field. As you note, it's not good design (normalization). If his method doesn't work for you, there are others. Some would use a de-normalized temp table for data entry, and normalize the data from there into the actual data table.

[Micron]

I had this same problem (I think). Users wanted a spreadsheet look that had 8 columns and a row for each day of the month, yet this would be bad practice since the column data should have been kept in rows. The alternative was a little popup datasheet driven by the main form in which they'd enter the column info as rows and close the datasheet. Repeat for day 2 to maybe 10 - 15 days. LOTSA clicking! Spent a lot of time looking for a solution, never found one. Decided to store the form header data in table1, 8 column headers as trade types (rows) in table2, and the trade type count in tbl3 as a comma separated string. The string is broken up into the form fields (or concatenated into the tbl3 field) by splitting/joining the array. Definitely not easy to code, not easily expandable and maybe there is a better way. I guess it's a form of job security!

[warren0127]

Thanks for your response! In that case I would prefer to have a temporary table where the data entry from the form resides, and then pushed to the actual Budget table (normalized). Not sure how one could do this, but I know it involves a query that appends data from one table to another. I'm not sure if the append could be autonomous, or do you have to manually run an append query each time you open the DB so the data is pushed to the real table? I would then use the real table data to update any reports, etc.

I'm guessing any option I take requires a bit of VBA. Unless there an option that won't require any VBA?

[pbaldy]

It would depend on your specifics, but it could be that a UNION query could be used as the base of an append query. The UNION query would "normalize" the data from the temp table. What I would probably have is a button on the form that the user would click on when they were done entering. That would trigger your process to copy the data and then empty the temp table.

[warren0127]

I'll give it a shot. Maybe do some small testing first in some example tables so I don't corrupt all of the work I have done so far

Thanks for your suggestion and I'll let you know what happens!

[pbaldy]

No problem!

[Micron]

Thanks for your response!

If you mean me, you're welcome. Thought I'd add that you were on the money in that a temporary table was required. The append to the actual tables was done in such a way as to normalize everything, but it was complicated. Each field in the query (at the top in the expression part) referred to a function that pulled that piece of data from the array (,separated string) passing the nth number of the column to retrieve the piece, and appended that to the correct table field. Definitely not easy, and I wonder if I could have done anything simpler and still provided the spreadsheet type of data input.