Items table Revisited
in [Tables]
|
From: Rob H on 14 Mar 2010 09:34 I thought it best to start new from the thread "Items Table help" that I originally posted. Original Post: I have a photography business that I am setting up a database for tracking sales by customer, item, demographics etc. I'd like some advise on the items table, I have over a hundred photographs that I currently offer for sale and each photograph is offered in various sizes as well as being a print only, matted or matted and framed. This is going to make the items table quite extensive. This is an example of what I have right now: Item Size Type Moulton Barn 12x24 Print Moulton Barn 12x24 Matted Moulton Barn 12x24 Framed Moulton barn 16x31 Print Moulton Barn 16x31 Matted Moulton Barn 16x31 Framed Dewy Dragonfly 11x14 Print Dewy Dragonfly 11x14 Matted Dewy Dragonfly 11x14 Framed and so on; So, each "Item" can have 6-12 "options" (ie: size, print or size, framed etc) x 100 photographs the list will be quite long. What's happening is; when I sort by Item>Ascending, the photos are listed alphabetically but the size and type become scrambled as such; Dewy Dragonfly 11x14 Framed Dewy Dragonfly 11x14 Print Dewy Dragonfly 11x14 Matted Moulton Barn 12x24 Print Moulton Barn 16x31 Matted I want the list to maintain the order of Item alphabetically, then Item size and finally Item Type in the order of Print, Matted or Framed. The obvious is to re-create the Items table and enter the items in the order I need but once done and I later add a new image I'm back to square one. Piet's Helpful Answer: Oh, you're an Excel guy. Excel <> Access. Write a query, and run it when you want your data sorted in a particular way. Follow-up Question: This worked perfectly for the original data in the Items list, however, when I enter new items and do a Refresh both the Item and Type update correctly but the Size is not working. If I choose Ascending in the layout view the old records show correctly but the new records show in reverse(descending). If I reverse the sorting order for size then just the opposite happens... Rob
From: Piet Linden on 14 Mar 2010 13:00 On Mar 14, 8:34 am, Rob H <R...(a)discussions.microsoft.com> wrote: > I thought it best to start new from the thread "Items Table help" that I > originally posted. > > Original Post: > I have a photography business that I am setting up a database for tracking > sales by customer, item, demographics etc. I'd like some advise on the items > table, I have over a hundred photographs that I currently offer for sale and > each photograph is offered in various sizes as well as being a print only, > matted or matted and framed. This is going to make the items table quite > extensive. > This is an example of what I have right now: > > Item Size Type > Moulton Barn 12x24 Print > Moulton Barn 12x24 Matted > Moulton Barn 12x24 Framed > Moulton barn 16x31 Print > Moulton Barn 16x31 Matted > Moulton Barn 16x31 Framed > Dewy Dragonfly 11x14 Print > Dewy Dragonfly 11x14 Matted > Dewy Dragonfly 11x14 Framed > > and so on; > > So, each "Item" can have 6-12 "options" (ie: size, print or size, framed > etc) x 100 photographs the list will be quite long. > > What's happening is; when I sort by Item>Ascending, the photos are listed > alphabetically but the size and type become scrambled as such; > > Dewy Dragonfly 11x14 Framed > Dewy Dragonfly 11x14 Print > Dewy Dragonfly 11x14 Matted > Moulton Barn 12x24 Print > Moulton Barn 16x31 Matted > > I want the list to maintain the order of Item alphabetically, then Item > size and finally Item Type in the order of Print, Matted or Framed. The > obvious is to re-create the Items table and enter the items in the order I > need but once done and I later add a new image I'm back to square one. > > Piet's Helpful Answer: > Oh, you're an Excel guy. Excel <> Access. Write a query, and run it > when you want your data sorted in a particular way. > > Follow-up Question: > This worked perfectly for the original data in the Items list, however, when > I enter new items and do a Refresh both the Item and Type update correctly > but the Size is not working. If I choose Ascending in the layout view the > old records show correctly but the new records show in reverse(descending). > If I reverse the sorting order for size then just the opposite happens... > > Rob Oh, so you *are* an Excel guy. There are several fundamental differences between Access and Excel, and you're still thinking in Excel terms. In a properly built Access application, your users NEVER see the plain tables. Everything happens through forms - for lots of reasons, but the one that's apparently got you hung up is the notion of sort order. In Access, tables are *unordered* buckets of data. Order in the table doesn't have any meaning. If you want to sort a bunch of records, base a query on that table/query and sort any way you want. You can set a sort order on your *form* that's based on this table and the form will keep it that way if you define it.
From: Rob H on 14 Mar 2010 18:18 I may have mis-spoke, I was referring to the Items list query that I had made prior to adding new items, not the actual Items list. Piet, I get your point and understand it now, thanks for being patient! "Piet Linden" wrote: > On Mar 14, 8:34 am, Rob H <R...(a)discussions.microsoft.com> wrote: > > I thought it best to start new from the thread "Items Table help" that I > > originally posted. > > > > Original Post: > > I have a photography business that I am setting up a database for tracking > > sales by customer, item, demographics etc. I'd like some advise on the items > > table, I have over a hundred photographs that I currently offer for sale and > > each photograph is offered in various sizes as well as being a print only, > > matted or matted and framed. This is going to make the items table quite > > extensive. > > This is an example of what I have right now: > > > > Item Size Type > > Moulton Barn 12x24 Print > > Moulton Barn 12x24 Matted > > Moulton Barn 12x24 Framed > > Moulton barn 16x31 Print > > Moulton Barn 16x31 Matted > > Moulton Barn 16x31 Framed > > Dewy Dragonfly 11x14 Print > > Dewy Dragonfly 11x14 Matted > > Dewy Dragonfly 11x14 Framed > > > > and so on; > > > > So, each "Item" can have 6-12 "options" (ie: size, print or size, framed > > etc) x 100 photographs the list will be quite long. > > > > What's happening is; when I sort by Item>Ascending, the photos are listed > > alphabetically but the size and type become scrambled as such; > > > > Dewy Dragonfly 11x14 Framed > > Dewy Dragonfly 11x14 Print > > Dewy Dragonfly 11x14 Matted > > Moulton Barn 12x24 Print > > Moulton Barn 16x31 Matted > > > > I want the list to maintain the order of Item alphabetically, then Item > > size and finally Item Type in the order of Print, Matted or Framed. The > > obvious is to re-create the Items table and enter the items in the order I > > need but once done and I later add a new image I'm back to square one. > > > > Piet's Helpful Answer: > > Oh, you're an Excel guy. Excel <> Access. Write a query, and run it > > when you want your data sorted in a particular way. > > > > Follow-up Question: > > This worked perfectly for the original data in the Items list, however, when > > I enter new items and do a Refresh both the Item and Type update correctly > > but the Size is not working. If I choose Ascending in the layout view the > > old records show correctly but the new records show in reverse(descending). > > If I reverse the sorting order for size then just the opposite happens... > > > > Rob > > Oh, so you *are* an Excel guy. There are several fundamental > differences between Access and Excel, and you're still thinking in > Excel terms. In a properly built Access application, your users NEVER > see the plain tables. Everything happens through forms - for lots of > reasons, but the one that's apparently got you hung up is the notion > of sort order. In Access, tables are *unordered* buckets of data. > Order in the table doesn't have any meaning. If you want to sort a > bunch of records, base a query on that table/query and sort any way > you want. > > You can set a sort order on your *form* that's based on this table and > the form will keep it that way if you define it. > . >
From: Fred on 17 Mar 2010 15:09 After 15 posts this might still be a bit confused, not sure if you still have an open question. But here's a thought on the sorting. If all of your descriptions are as precisely formatted and entered as your example, you could just use 4 fields, and derive your description from them Field: Item (text) Field: Dimension1 (numeric) Field: Dimension2 (numeric) Field: Type (text) And sort by those. And then derive your description from those whenever you need it: =[Item] & " "& [Dimension1]&" x "& [Dimension2]&" "&[Type]
|
Pages: 1 Prev: Items table help - stevie PIMPS again! Next: Relationships |