combining primary keys?

J

Jason

Hi there,

I'm new to this whole database thing and am trying to learn Access. I have
a question which i can't seem to find an answer to in the book i'm using to
learn it.

What i'd like to do is have a simple DB, including these two columns:

120604-10
110704-15
120504-22

This is the system i use at the office to label orders. They are the date,
with a corresponding number (10 in this case).

I want to store all of these order numbers (120604-10, 110704-12,
etc.)sequentially by date, but have the auto number column be able to
differentiate between the days. So, for example, it isn't just assigning me
incremental numbers, regardless of the date.

I'm sure there is a way to do this, but it would help me a lot if someone
could give me a push in the right direction here.

Thanks very much.

Jason.
 
L

Lynn Trapp

I want to store all of these order numbers (120604-10, 110704-12,
etc.)sequentially by date
Click to expand...

One of the things you should learn first about "this whole database thing"
is that database do not store data in any particular order. Data is merely
dumped into a big bucket to be retrieved by you in the order you want it.
Thus, you have to use a query with an Order By clause to sort the data in
the order you want it.
but have the auto number column be able to
differentiate between the days.
Click to expand...

I'm not quite sure what you have in mind here. Could you explain a bit more?
 
L

Larry Daugherty

Hi Jason,

Lynn gave you the first big clue.

Beyond that, it isn't clear what are the "primary keys" you reference so it
may be that you aren't referencing primary keys?? Or yet you may. Explain
please. What I see seems to be an encoded date with a concatenated sequence
number.

Unless you are inextricably wedded to your implicit design, I'd start over
with a different paradigm. Since I don't have a clue as to the rest of your
table design I'll just wing it a bit on what I see:

If you must encode the date, I suggest you make it YYYYMMDD rather than
MMDDYY. My way sorts nicely, what you have can only be sorted with a bit of
pain. For sake of discussion say that the encoded date is in tblOrderDate.

In a child table, tblOrderSeq you would have the actual order. Its fields
might be: OrderSeqID, an autonumber primary key; OrderDateID, a long integer
which stores the Primary Key of the parent record, next an integer (or text)
field;SeqNumber and then fields for everything else you want in that order.

When you are editing/massaging existing orders you open your form and find
the target date and display a list of SEQ numbers used for that date.
Select the sequence number to see that order. [A form/subform paradigm is a
natural choice for data entry and for data edit].

When you open your form for adding new records, find or enter the date (I
recommend that you encode the date in your head and always enter it in that
form - you can do it using an actual date format but you must then do some
encoding and decoding along the way). Whether or not the date was
previously used, you'll create a new sequence number by calculating DMax()
+1 on the SeqNumber field for this date to get the new SeqNumber.

When you need to display the Order date and Sequence number concatenated
it's a piece of cake.

It's unlikely that what I've suggested parallels what you already have. You
can implement things exactly as you've implied. However, you'll find
yourself bogged down in parsing and tracking and swapping around the date
components and working out how to deal with the "Sequence per date" issues.
Searches and sorts would be difficult and error prone.

Good luck with it. Post back with questions.
 
Top