Need to keep this data in one table, but errors keep coming up

[kleaverjr]

OK, I am receiving a "Property Value Too Large" error when I attempt to save the Design for the Table. The table will need the maximum 255 Fields. The Table is to record each individual sale. I supposed I could split up the table, but it is not what I want to do so if anyone can help explain to me what is causing the error and possible solutions while keeping the data in one table, that would be great. the Table is recording each individual transaction so I can go back through if there is a problem of some kind, either with inventory or need a duplicate receipt, or for whatever reason I want to know on a separate table, what each sales receipt had on it.



The Table Show_SalesLog has the following Fields:

ReceiptNumber (PRIMARY KEY) - Text (Each Receipt Number is Unique)
ShowName - Long Text
ShowDate - Date/Time - (Format: mm/dd/yyyy hh:nn)
OrderTotal - Currency
PaymentType - Short Text

And for up to 50 Items the Following Data is stored. My highest number of items for a single transaction has been up to now 38 so setting it at 50 I'm hoping is sufficient)

the ## represents the Number, so it would be Item_1, Item_2, etc up to 50)

Item_## - Long Text
ItemQty_## - Number
ItemPrice_## - Currency
ItemDiscount_## - Currency
ItemTotal_## - Currency


I haven't even started entering data into this table because it will not let me save the design. And from what little I am able to understand from when I search this error online for, it confuses me even more as no one is explaining WHY it is happening, just ideas on how to restructure the tables. I already have to update multiple tables for different purposes already, and I do not see a simple way of splitting this table without adding more headaches, unless that is my only solution, and i'm hoping it is not. The other solution is reducing the number to 40 items and hope I don't exceed that.

Thank for the help.

Ken L.

[Welshgasman]

Structure is all wrong.
Tables are long and thin, not wide and short, like an Excel spreadsheet.
So you need forget you excel thinking and start thinking the database way.

All those items should be individual records. Then it does not matter if you have 5, 50 or 500.

[kleaverjr]

I would love to use Excel instead but to use SQL language with searching the spreadsheets became problematic. Each item has it's own record in the Inventory Table, but this table is to help keep a definitive record or each sales transaction. I didn't want to have to have data all over the place when I need to look up quickly previous sales receipts. But it sounds like I will have little choice in the matter. :-\

And one reason why this set of data needs to be separate is because when the quantity is one the discount applied is simple to calculate and record, if there is more than one, then it becomes a bit more complicated and if the calculation is wrong somehow, then updating the Inventory Table with the wrong sold price can become quite a headache. Versus having a separate table showing the item list price, any additional discount applied to the one or more of the same items, and the total for each set of items.

Ken L.

[Welshgasman]

You do not have data all over the place.
You have data in the correct place and generally only once.
So in your record layout at the moment, if you only have 1 item, you use 5 fields?, and all the others are just empty?

If you insist on this structure, just put 25 items in one table and another 25 in another. Link with a field that identifies the record. However I would not recommend this, but it is your database.
I can see you coming back soon, asking how to total the items value, count whatever. With the correct structure is it a simple sql statement. With your structure, you would need to create you own functions.

Don't do it, is all I can say.

[kleaverjr]

>>>So in your record layout at the moment, if you only have 1 item, you use 5 fields?, and all the others are just empty?<<<

Correct.

If I were to keep it in one table, I fear I will run into exceeding the maximum number of records for the table. Each show I do can have over 300 transactions, so if each item sold was it's own record, I couple possibly exceed the 2048 limit. I figure that limit was not going to be an issue as I would archive the table at the end of each fiscal year and empty the "current" year table. I would have to do this after possibly each show.

"Linked tables" is an option, but not one to my liking as it has created problems before for me. But from what you are telling me I have few options.

BTW, can you answer for me what value is too large for access to handle? If it is supposed to handle 255 fields, why can't it?

Ken L.

[moke123]

the ## represents the Number, so it would be Item_1, Item_2, etc up to 50)

Item_## - Long Text

You really need 50 long text fields? What's a sample of the data for this field look like?

As Gas man points out your going at this all wrong.

You need normalized tables.

[kleaverjr]

The data for the Item field is the item SKU which is up to 18 characters.

TIMAFRD-123456789

I'm not sure how else to structure this while maintaining ONE table for a Fiscal Year for this data, while not using Linked Tables or exceeding 2048 records for the Fiscal Year.

Ken L.

[NoellaG]

>>>So in your record layout at the moment, if you only have 1 item, you use 5 fields?, and all the others are just empty?<<<

If I were to keep it in one table, I fear I will run into exceeding the maximum number of records for the table.
Ken L.

Access can easily handle 100000 recs in a table, the limit here is 2 GB (or is it 4GB now?) per file

[CJ_London]

There is a limit on the total possible size of a record, regardless of the number of fields

allow
2 bytes per integer
4 bytes per long
8 bytes for currency and date
10 bytes plus 2 bytes per char for a string. If you have a max size of say 50 chars for the field, it needs to allow 110 bytes

[June7]

What makes you think you have limit of 2048 records? There are Access databases with millions of records.

Keeping an "inventory" table is usually a bad design as well. Calculate inventory balances when needed, don't save balances to table. Review http://allenbrowne.com/AppInventory.html

Back to your original issue - no idea why unable to add 255th field. If you want to provide db for analysis, follow instructions at bottom of my post.

[CJ_London]

re the error message and my previous post, the number I was thinking of is 4000

Number of characters in a record (excluding Long Text and OLE Object fields) when the UnicodeCompression property of the fields is set to Yes - 4000

from this link

https://support.microsoft.com/en-us/...8-98c1025bb47c

But agree with everyone else - one table is the wrong approach - might as well use Excel since your concerns about the difficulties of using sql wont be solved by using a single table in Access and it will be exceedingly slow once you have a large number of records. Performance is about indexing and you can only have 32 indexes in a table.

From your first post, you only need two tables - effectively your saleslog table and an items table. Just include the receipt number in your items table as a foreign key to tie it back to the sales log. Not what I would really call 'all over the place'.

[ArviLaanemets]

Really, to store all necessary data properly and efficiently, you need tables like:
tblItems: ItemID, ItemName, ItemMU, [ItemPrice], ... (ItemMU means measurement unit. ItemPrice is optional here. In case item prices never change, or are used only into other tables the current price at moment the entry there is saved, you can it in this table. Otherwise you need a separeate table where you keep the history of prices for all items;
tblClients: ClientID, ClientName, ... (you receipts are for sales, so it looks like you need come client information there too);
tblReceipts: ReceiptID, ReceiptNumber, ClientID, ReceiptDate, ...; (This table contains general info about receipt. In case Receipt number is a unique string or number you probably get e.g. from bookkeeping, you can drop autonumeric ID for this table, and use ReceiptNumber as primary key instead.);
tblReceiptRows: ReceiptRowID, ReceiptID, ItemID, [ItemPrice], ItemQuantity (ItemPrice here is optional, and must be here, when prices are not fixed somewhere, but are determined somehow (e.g. haggled) for current sale every time the receipt row is registered).

In tblReceiptRows, you can have any number of rows for items sold with this receipt, from 1 up to whatever you comp is able to handle, or an autonumeric field can store (2,147,483,647)

To [display]/[print out]/[send by mail]/[save as pdf-document]/[whatever] the receipt, you need an ODBC query, which reads the info from all those tables, and uses this as the source for your output. The query is run anew whenever you need, and there is no need to save it's result data in your database (but of-course you can save it's output as pdf file into some folder [and sign it there digitally]).

[NoellaG]

Keeping an "inventory" table is usually a bad design as well. Calculate inventory balances when needed, don't save balances to table.

as a professional in the logistics (more than 20 years experience) world I must strongly disagree with this. I never have seen a logistic application without an inventory table that didn't get into trouble after a while, even in Access.

[ArviLaanemets]

as a professional in the logistics (more than 20 years experience) world I must strongly disagree with this. I never have seen a logistic application without an inventory table that didn't get into trouble after a while, even in Access.

Exactly! But probably June7 did mean no need for running inventory table instead on one made periodically, which real balance saved along with inventory date, and whoch must be used as source of base balance for later balance calculations (so you don't have to sum all in- and outcomes in your company from it's grounding).

[June7]

The exception for saving inventory balances is included in Allen Browne's tutorial. The point is a saved balance is not altered every time there is a transaction processed.